# VBA processing multiple files

Const FOLDERPATH As String = "\ITSEASY"
Const DATAFOLDER As String = "\AS"
Const OLDDATAFOLDER As String = "\ABC"
Public Sub ExportData()
Application.ScreenUpdating = False
'GET DROPBOX PATH
Dim TheDropBoxPath As String
TheDropBoxPath = DropBoxPath.GetDBPath

Dim files() As Variant 'STARTS AT 1
files = FileStuff.listfiles(TheDropBoxPath & FOLDERPATH & DATAFOLDER)
Dim NumberOfFlies As Integer

On Error GoTo blob
NumberOfFlies = UBound(files)
On Error GoTo 0

'FILE FOR EXPORTED DATA
Dim ExportWB As Workbook
Set ExportWB = Workbooks.Open(TheDropBoxPath & FOLDERPATH & "\" &
"Pushed.xlsx")
Dim ExportWBLastRow As Long
ExportWB.Sheets(1).UsedRange.Clear

Dim DataWB As Workbook
Dim DataWBLastRow As Long

For i = 1 To UBound(files)
'OPEN THE FILE
Set DataWB = Workbooks.Open(TheDropBoxPath & FOLDERPATH & DATAFOLDER & "\" & files(i))
DataWBLastRow = DataWB.Sheets(1).Cells(1, 1).End(xlDown).Row

If i > 1 Then 'LAZY
ExportWBLastRow = ExportWB.Sheets(1).Cells(1, 1).End(xlDown).Row + 1
End If

If i = 1 Then 'FOR HEADER ROW
DataWB.Sheets(1).Range("1:1").Copy
ExportWB.Sheets(1).Range("A1").PasteSpecial xlPasteValues
ExportWBLastRow = 2
End If

'COPY PASTE CONTENT
DataWB.Sheets(1).Range("2:" & DataWBLastRow).Copy
ExportWB.Sheets(1).Range("A" & ExportWBLastRow).PasteSpecial xlPasteValues

DataWB.Close

'MOVE FILE TO OLD
Call FileStuff.MoveFiles(TheDropBoxPath & FOLDERPATH & DATAFOLDER & "\" & files(i), TheDropBoxPath & FOLDERPATH & DATAFOLDER & "\AYE\" & files(i))
Next i

ThisWorkbook.Save

ExportWB.Save
ExportWB.Close

Application.ScreenUpdating = True

MsgBox (NumberOfFlies & " dfghdfsgsfg tf " & Chr(34) & "FAKETEXT" & Chr(34) & "BLABLA")
Exit Sub

blob:     MsgBox ("FAKE MESSAGE  NUMBER 2 " & TheDropBoxPath & FOLDERPATH & DATAFOLDER): Exit Sub
End Sub


I'm looking for improvement on the way I deal with processing multiple files. Currently i'm using FSO, I feel like it's slow. Any better way to do that ?

Is there a way to copy the content of a sheet without having to open it ?

• If they are Excel files, ADODB is a decent approach without having to go through the Excel object model. If they aren't excel files. or you can convert them to a e.g. csv file there are alternatives. Buffering a string, then using Get is very fast. Have a look for potentially some helpful methods if you decide to go that route. codereview.stackexchange.com/a/188068/108307 Apr 2 '19 at 19:52
• i'm going to read a bit more about adodb then. The example I was folowing was putting a range in the ADODB.Recordset like in this thread : link Could you ellaborate on what you meant by buffering a string out of the book converted to csv. Convert to csv : Can do. Turn Csv to string : can do. What do you mean by buffering ? and get Apr 4 '19 at 12:06
• One way to speed up string operations is to allocate a string to the size you need ahead of time. You do this to avoid the string being reallocated every time you change it as strings are immutable. You can set size of a string like myString = Space\$(50000). Get is a vba statement to read data from a file to a variable. You can read more about it here --> docs.microsoft.com/en-us/office/vba/language/reference/… Apr 4 '19 at 16:44