0
\$\begingroup\$
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

'FILE BEING READ
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

    Application.DisplayAlerts = False
    DataWB.Close
    Application.DisplayAlerts = True

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

    ExportWB.Sheets(1).UsedRange.RemoveDuplicates Columns:=1, Header:=xlGuess
    ThisWorkbook.Save

    Application.DisplayAlerts = False
    ExportWB.Save
    ExportWB.Close
    Application.DisplayAlerts = True

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 ?

Self answer : ADODB does that

\$\endgroup\$
7
  • \$\begingroup\$ 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 \$\endgroup\$ Apr 2 '19 at 19:52
  • \$\begingroup\$ They are excel files, however I'm very open to having to go though excel object model(I will, I want to know everything about excel). I have managed to do it using ADODB connection. however it was useless as the range I need to do things with isn't known before I open the file and is identified by another sub I have created (the way I have managed to do it required the range fed to the recordset / sql query) and no table in the files either obviously. No problem converting the files to csv. Looking at your link RN, not sure I understand it all and how it apply to my problem. Will dig. TY. \$\endgroup\$
    – a1a1a1a1a1
    Apr 3 '19 at 16:25
  • \$\begingroup\$ Solution offering is going to be dependent on your data and what you are trying to accomplish. Some examples might be helpful here. As for this part "I have managed to do it using ADODB connection. however it was useless as the range I need to do things with isn't known before I open the file and is identified by another sub I have created..." ADODB should be picking the sheet up as a table, so all the data should be there in a recordset object. \$\endgroup\$ Apr 3 '19 at 19:16
  • \$\begingroup\$ 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 \$\endgroup\$
    – a1a1a1a1a1
    Apr 4 '19 at 12:06
  • \$\begingroup\$ 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/… \$\endgroup\$ Apr 4 '19 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.