Copy data from closed workbooks in folder to active workbook

[ryan8200]

I need to copy data from files located within specific folder to current open workbook. The data range for each files is from A2 to cell located at most right column and a row above of last row. There will be 4 files in the folder. My objective is to copy
1. data from files 1 and paste to active workbook A3
2. data from files 2 and paste to active workbook D3
3. data from files 3 and paste to active workbook F3
4. data from files 4 and paste to active workbook J3

I have tried below code but nothing happen.

[/
Dim directory As String, fileName As String

Application.ScreenUpdating = False
directory = "C:\Users\users\Downloads\My\"
fileName = Dir(directory & "*.xlsx")
Set wb1 = ThisWorkbook

Do While fileName <> ""
    Set wb2 = Workbooks.Open(directory & fileName)
    Lastrow = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row + 1
    Lastcol = Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column - 1
        With wb2.Sheets(1)
            wb1.Sheets(1).Range("a3") = .Range(.Cells(2, 1), .Cells(Lastrow, Lastcol))
            wb1.Sheets(1).Range("d3") = .Range(.Cells(2, 1), .Cells(Lastrow, Lastcol))
            wb1.Sheets(1).Range("f3") = .Range(.Cells(2, 1), .Cells(Lastrow, Lastcol))
            wb1.Sheets(1).Range("j3") = .Range(.Cells(2, 1), .Cells(Lastrow, Lastcol))
        End With
    wb2.Close savechanges:=True
    fileName = Dir
Loop
Application.ScreenUpdating = True
End Sub

]

 

[GWteB]

There are a few things that stand out in your code. The first two won't play a big role, but the rest will.
- not all variables used are explicitly declared;
- not all worksheet ranges are explicitly qualified;
- the target range does not have the same dimensions as the source range;
- the code relies on the default property of a range, but VBA sometimes gets it wrong;
- an attempt is made in a loop to write to the same target range each time, so data is (unintentionally) overwritten.

Try this and see whether it works for you.

Public Sub ryan8200()

    Const SRCFOLDER As String = "C:\Users\users\Downloads\My\"
    Const FILE1 As String = "file1.xlxs"
    Const FILE2 As String = "file2.xlxs"
    Const FILE3 As String = "file3.xlxs"
    Const FILE4 As String = "file4.xlxs"

    Dim SourceSht   As Worksheet
    Dim DestSht     As Worksheet

    Set DestSht = ThisWorkbook.Worksheets(1)

    Set SourceSht = Workbooks.Open(SRCFOLDER & FILE1).Worksheets(1)
    GetData SourceSht, DestSht.Range("a3")
    SourceSht.Parent.Close SaveChanges:=False   ' don't save on closing, nothing was changed

    Set SourceSht = Workbooks.Open(SRCFOLDER & FILE2).Worksheets(1)
    GetData SourceSht, DestSht.Range("d3")
    SourceSht.Parent.Close SaveChanges:=False

    Set SourceSht = Workbooks.Open(SRCFOLDER & FILE3).Worksheets(1)
    GetData SourceSht, DestSht.Range("f3")
    SourceSht.Parent.Close SaveChanges:=False

    Set SourceSht = Workbooks.Open(SRCFOLDER & FILE4).Worksheets(1)
    GetData SourceSht, DestSht.Range("j3")
    SourceSht.Parent.Close SaveChanges:=False

End Sub

Public Sub GetData(ByVal argSourceSht As Worksheet, ByVal argDestRange As Range)

    Dim LastRow As Long, LastCol As Long, SourceData As Variant
    With argSourceSht
        LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
        LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
        SourceData = .Range(.Cells(2, 1), .Cells(LastRow, LastCol)).Value
        argDestRange.Resize(UBound(SourceData, 1), UBound(SourceData, 2)).Value = SourceData
    End With
End Sub

 

[ryan8200]

Hi GWteB,

Thanks for your input. The files in the folder has dynamic name and depend on the time I downloaded them. Is that possible to use for loop to read each file in the folder instead of manually declare FILE1 to FILE4 ?

 

[GWteB]

Yeah, but how would you then determine which workbook should target which worksheet range? That's why I coded it in sequence, to have control.
If you don't know the workbook's name in advance then you have a 1 in 4 chance that the date ends up in the right place, provided there are no other workbooks in the disk folder you want to pull your data from.

 

[ryan8200]

Hi GWteB,
The filename has the following format: data__at_30 Sep 2021, 11_32_19.xlsx. Is that possible to extract time from filename so that the earliest will be executed first and the latest will be the last to execute.

 

[GWteB]

That is all possible, but then a number of things have to be taken into account in the code to prevent things from going wrong unexpectedly and unintentionally, after all and among others:
- there may be more than four files, all of which need to be checked against the naming convention as you specified;
- if the correct files are collected there could be more than four as well, so we have to determine which one is the last one and then count backwards to obtain the first wanted;
In short, certainly not impossible but quite a job to build code that takes all scenarios into account.

Imo your main objective has been achieved. Your additional requirements are an extension of it, nevertheless of a different order, so I think it's fair asking you to start a new thread for this problem.