Copy data from closed workbooks in folder to active workbook

ryan8200

Active Member
Joined
Aug 21, 2011
Messages
327
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.

VBA Code:
[/
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

]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,585
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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

Active Member
Joined
Aug 21, 2011
Messages
327
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,585
Office Version
  1. 2013
Platform
  1. Windows
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

Active Member
Joined
Aug 21, 2011
Messages
327
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,585
Office Version
  1. 2013
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,699
Messages
5,766,005
Members
425,322
Latest member
galaxy6623top

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top