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
Bookmarks