Extract certain columns from multiple .xlsm files into master file without opening source files

[Rodrigues]

Hi
I'm trying transfer data (certain columns) from multiple .xlsm files into a master one.
All the files have the same structure and they are in the same folder.
Directory: C:\Test
Source file - TestA ; TestB ; testC ; TestD (all xlsm)
Master file - TestMaster.xlsm

Columns to be copied from the source files:
A,B,D,H,L and so on from row 10 until 10000


Destination file:
past from cell C10 than next row available down

Tested a code LoopThroughDirectory (not sure if it is correct) and I'm getting the error TestA.xlsx could not be found.
Could someone help me please?
thanks again
R

 

[PCosta87]

Hi,

You are not feeding it the "full name" when setting "wb"... "Set wb" should read:

Set wb = Workbooks.Open("C:\Test\" & MyFile)

Hope this helps

 

[Rodrigues]

PCosta
Thank you so much, that did help.
Just one more thing, if I can ask, the columns on the source files will have multiple rows (up to 30 max) which I need to copy, at the moment it is only copying the first row(10). Could you help on this one, please?
Thanks
R

 

[SirJB7]

Hi, Rodrigues!
As a matter of fact you can't do anything with the data stored in a workbook if you don't open it first. From the Excel interface, from VBA, however it might be... you have to open it to manipulate its data.
Maybe this helps:
http://chandoo.org/forum/threads/ho...opening-an-excel-file.11887/reply?quote=69729
Regards!

 

[PCosta87]

PCosta
Thank you so much, that did help.
Just one more thing, if I can ask, the columns on the source files will have multiple rows (up to 30 max) which I need to copy, at the moment it is only copying the first row(10). Could you help on this one, please?
Thanks
R

Hi,

Sure... similarly to what you have for the destination:

Sub LoopThroughDirectory()
Dim MyFile As String, rng As Range, wb As Workbook
Dim lrow As Integer
MyFile = Dir("C:\Test\*.xl*")
    Do While Len(MyFile) > 0
        If MyFile = "TestMaster.xlsm" Then
            Exit Sub
        End If
        Set wb = Workbooks.Open("C:\Test\" & MyFile)
            With wb.Sheets(1)
                lrow = .Cells(Rows.Count, 1).End(xlUp).Row
                Set rng = .Range("A10:B" & lrow & ",D10:D" & lrow & ",H10:H" & lrow & ",L10:L" & lrow)
                rng.Copy Workbooks("TestMaster.xlsm").Sheets(1).Cells(Rows.Count, 3).End(xlUp)(2)
            End With
        wb.Close
        MyFile = Dir
    Loop
End Sub

 

[Rodrigues]

Excellent, thanks again PCosta

 

[PCosta87]

Don't mention it

 

[Rodrigues]

I'm sorry to come back again on this matter, just looking for a couple of opinions.
1 - Have added a clear cells code to workbook open, otherwise will copy all the data every time the code runs, is this the best practice or there is a better way to do it? My concerns are, as we proceed we'll have hundreds of files therefore it will slow the process to run the macro at some point. and also would like to add some dynamic charts, so perhaps skimping the data has been copied it will be better (don't know if it is feasible).
2 - The source files have links to other files, added some code to suppress update links warning messages but still popping up.
Thanks
R



Apologies and thanks.

 

[PCosta87]

Hi,

I've made a few adjustments to the code you already had...

The code to clear all data (when opening) is now dynamic (instead of a fixed range like you had):

Private Sub Workbook_Open()

    Dim lrow As Integer
 
    lrow = Sheet1.Cells(Rows.Count, "C").End(xlUp).Row
    Sheet1.Range("C10:W" & lrow).Clear

End Sub

For the alerts, since they appear when opening the source files, I added the lines of code to the "LoopThroughDirectory":

Sub LoopThroughDirectory()

    Application.AskToUpdateLinks = False
    Application.DisplayAlerts = False
 
    Dim MyFile As String, rng As Range, wb As Workbook
    Dim lrow As Integer
    MyFile = Dir("C:\users\user\downloads\*.xl*")
        Do While Len(MyFile) > 0
            If MyFile = "TestMaster.xlsm" Then
                Exit Sub
            End If
            Set wb = Workbooks.Open("C:\users\user\downloads\" & MyFile)
               
                With wb.Sheets(1)
                    lrow = .Cells(Rows.Count, 1).End(xlUp).Row
                    Set rng = .Range("A10:B" & lrow & ",D10:D" & lrow & ",H10:H" & lrow & ",L10:L" & lrow & ",P10:P" & lrow & ",T10:T" & lrow & ",X10:X" & lrow & ",AB10:AB" & lrow & ",AF10:AF" & lrow & ",AJ10:AJ" & lrow & ",AN10:AN" & lrow & ",AR10:AR" & lrow & ",AV10:AV" & lrow & ",AZ10:AZ" & lrow & ",BD10:BD" & lrow & ",BH10:BH" & lrow & ",BL10:BL" & lrow & ",BP10:BP" & lrow & ",BT10:BT" & lrow & ",BX10:BX" & lrow)
                    rng.Copy Workbooks("TestMaster.xlsm").Sheets(1).Cells(Rows.Count, 3).End(xlUp)(2)
                End With
            wb.Close
            MyFile = Dir
        Loop
     
    Application.AskToUpdateLinks = True
    Application.DisplayAlerts = True
     
End Sub

Now, to answer your first question... In my opinion it is better/safer to clear all data and import everything again from the source. Doing some sort of comparison between what you already have and what is on the source files can be difficult depending on what you have to go with. It can be done, I'm just not sure it is worth the trouble. In fact, copy/paste of everything may end up being quicker than figuring out what to copy first.

 

[Rodrigues]

PCosta
It works very well.
Thanks again, very much appreciated.
Regards

 

[PCosta87]

Don't mention it