Results 1 to 3 of 3

HOW CHECK FILE.NAME HAS EXISTS OR NOT IN FOLDER,if not exists then copy

Threaded View

  1. #1
    Forum Expert
    Join Date
    11-28-2015
    Location
    indo
    MS-Off Ver
    2016 64 bitt
    Posts
    1,513

    HOW CHECK FILE.NAME HAS EXISTS OR NOT IN FOLDER,if not exists then copy

    I HAVE CODING LIKE this,using workbook open ,if file in folder has copy or file has exists ,for next time not to calculate or not copy again
    else if in folder contain new file ,every workbook_open new file in sheet1.usedrange copy to resume file sheets("REKAP")
    HOW TO do check up file exists or not exists
    i have coding ,every file in folder has copy ,i am save filename in sheets.rekap columns("O")
    how to do thank for attention
    Private Sub Workbook_Open()
    Dim fso As Object, f As Object, sf As Object, ssf As Object, myFile As Object
    Dim WB As Workbook, j As Long, n As Long, i As Long, x(), mytable, SP
    Dim s As String, temp As String, rg As Range
    Set fso = CreateObject("Scripting.filesystemObject")
    Set f = fso.GetFolder("C:\Users\wawang-pc\Documents\Locked\te\fileku\Gabungfile")
    'GANTI PATH SESUAIKAN
     With Sheets("REKAP")
         If Not rg Is Nothing Then Set rg = Range("O1", .[O100000].End(3))
         If Not rg Is Nothing Then
             SP = Split(Join(Application.Transpose(rg.Value), ","), ",")  ' i want check up is file has copy or not,if in folder there are new file then copy range from new file
         Else
             GoTo n1
         End If
     End With
    Application.ScreenUpdating = False
    For Each sf In f.SubFolders
          Set ssf = sf
        For Each myFile In ssf.Files
         If myFile.Name Like "*DIKLAT*" Then
            For n = 0 To UBound(SP)
                If Not fso.FileExists(SP(n)) Then         ' IF FILE DOESN EXISTS IN FOLDER THAN COPY RANGE  FROM NOT EXISTS FILE
    n1:
                        j = j + 1
                        ReDim Preserve x(1 To j)
                        Set WB = Workbooks.Open(SP(n), False)
                        With WB.Sheets("REKAP")
                             mytable = .UsedRange.Offset(1).Value
                              x(j) = mytable: mytable = ""
                              s = IIf(s = "", myFile, s & "," & myFile)         's = path of every file and save to range
                             .Parent.Close savechanges:=False
                        End With
                 Else
                   GoTo nn
                 End If
               Next n
       End If
     
    Next myFile, sf
    For i = 1 To UBound(x)
       With Sheets("REKAP")
        Range("A100000").End(3)(2).Resize(UBound(x(i)), UBound(x(i), 2)) = x(i)
       End With
    Next i
        With Sheets("REKAP")
           .Cells(Rows.Count, "O").End(3)(2).Resize(n) = Split(s, ",")   'SAVE PATH TO columns O
        End With
        Erase x
    nn:
        MsgBox "Tidak ada tambahan file Baru di folder ", 64: Exit Sub
        Exit Sub: Application.ScreenUpdating = True
    End Sub
    this other code but still can copy if has exists
    Sub ff()
    Dim d As Object, fso As Object, wb As Workbook, rg As Range, r As Range
    Set d = CreateObject("scripting.dictionary")
    Set fso = CreateObject("scripting.Filesystemobject")
    Dim t, a(), j As Long
    For Each f In fso.getfolder("C:\Users\wawang-pc\Documents\Locked\te\fileku\Gabungfile\DATA").Files
      d(f) = ""
    Next f
    If Not rg Is Nothing Then
       Set rg = Columns(12).SpecialCells(2)
       For Each r In rg
          If d.EXISTS(r.Value) Then
           MsgBox d(r.Value)
           d.Remove r.Value
          Else
             For Each E1 In d
               Set wb = Workbooks.Open(E1, False)
               j = j + 1
               ReDim Preserve a(1 To j)
               a(j) = wb.Sheets("REKAP").UsedRange.Offset(1)
               wb.Close False
               Cells(Rows.Count, 12).End(3)(2) = E1
               MsgBox "file exists "
             Next E1
          End If
       Next r
     
    Else
            For Each E In d
               Set wb = Workbooks.Open(E, False)
               j = j + 1
               ReDim Preserve a(1 To j)
               a(j) = wb.Sheets("REKAP").UsedRange.Offset(1)
               wb.Close False
               Cells(Rows.Count, 12).End(3)(2) = E
               MsgBox "file not exists "
            Next E
         
    End If
    
    Dim i As Long
    For i = 1 To UBound(a, 1)
        With Sheet1
           .Cells(Rows.Count, 1).End(3)(2).Resize(UBound(a(i), 1), UBound(a(i), 2)) = a(i)
        End With
    Next i
    Erase a: j = 0
    End Sub
    Last edited by daboho; 10-12-2018 at 04:22 PM.
    "Presh Star Who has help you *For Add Reputation!! And mark case as Solve"

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create Folder & Check if Folder Exists if Not Create Folder & then Save File
    By Quivolt in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-28-2017, 05:31 AM
  2. Check if file exists from same folder spreadsheet was opened
    By TimmerSuds in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-08-2016, 04:37 AM
  3. Check if Folder exists
    By akq125 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-02-2012, 10:35 PM
  4. Check if folder exists, if not create it
    By mattmac in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-28-2009, 10:17 AM
  5. Check if folder exists, if yes just copy sheet in to folder?
    By Simon Lloyd in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-19-2006, 10:44 AM
  6. [SOLVED] How to check if a file exists in an ftp folder
    By LL Cool A in forum Excel General
    Replies: 3
    Last Post: 05-16-2006, 04:25 PM
  7. how to check if folder exists
    By funkymonkUK in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-14-2005, 07:03 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1