Can you get data from multiple Workbooks without opening each one?

[PJ0302917]

Hi

I have a folder containing 10 workbooks and i'm trying to get data from each Workbook. At the moment i'm using the code below to set the wb object.

Set wb = Workbooks.Open(Filename:=folderPath & filename)

This code actually opens each Workbook. Ideally i want this to be invisible to the user and was hoping VBA would be able to get data from other workbooks in the background. I've had a look at the other functions of the Workbook class and cant see anything that i can use.

If anyone knows how to do this, could you help me out and point me in the right direction?

Thanks

 

[Eric W]

Consider the macro in post #2 of this thread:

https://www.mrexcel.com/forum/excel...l-files-folder-give-me-list-numbers-each.html

It gathers data from each workbook in a folder. It does not actually open the other workbooks, it just creates a worksheet formula that references the closed workbooks, then saves the values. You can probably adapt it to do what you want.

Let me know if you have any questions.

 

[PJ0302917]

Hi Eric, thanks for the help, i've managed to get into each file now thanks to your example.

 

[Eric W]

Well done! Thanks for the update.

 

[PJ0302917]

I'm struggling with the following code, i don't understand the .Formula and =Index code (i assume this is from a previous example?) . I need to get all the data from the worksheet into a MyData 2d Variant array. I've tried multiple ways and end up with a path as a string being applied to the MyData array. At the end of the code listed below you have used a Row(), is this something i need? Thanks in advance for the help
For Each f In fldStart.Files
If f.Name Like "*.xl*" Then
Range("A1:A" & MaxRows).Formula = "=INDEX('" & MyPath & "\[" & f.Name & "]Sheet1'!AD:AD,ROW())"</pre> Range("A1:A" & MaxRows).Formula = "=INDEX('" & MyPath & "\[" & f.Name & "]Sheet1'!AD:AD,ROW())"

 

[Eric W]

What is the address of the range you want to get from the closed workbook?

 

[PJ0302917]

Hi Eric, the range i was going to use is MyData = Range("A3", Range("A2").End(xlDown).End(xlToRight))
that command gets data from the Active worksheet i'm writing the visual basic in, i need to try and use the MyPath, f.Name and SheetName variables in the loop to iterate through the different workbooks but not sure how??

 

[Eric W]

I don't know of a way to use xlDown or xlToRight on a closed workbook. The best I can suggest (without actually opening the workbooks) is to select a range that's larger than you think it will ever get.

Sub GetData()
    Dim fso As Object, fldStart As Object
    Dim MyPath As String, MyData(10) As Variant, MDCtr As Long

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    MyPath = "C:\Users\yourname\Desktop\Excel"
    
    Set fso = CreateObject("scripting.FileSystemObject")
    Set fldStart = fso.GetFolder(MyPath)
    
    With Worksheets.Add
        For Each f In fldStart.Files
            If f.Name Like "*.xl*" Then
                .Range("A1:Z100").Formula = "='" & MyPath & "\[" & f.Name & "]Sheet1'!A1&"""""
                MDCtr = MDCtr + 1
                MyData(MDCtr) = .Range("A1:Z100").Value
            End If
        Next f
        .Delete
    End With
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
End Sub

This creates a temporary worksheet, loops through all the matching files in the folder, creates a 1-1 formula that pulls the data from the other workbooks, and saves that into MyData. You'll now have to read through the arrays to find the actual start and stop locations.

You can try to use the xlDown and xlToRight functions by changing the If structure to:

            If f.Name Like "*.xl*" Then
                .Range("A1:Z100").Formula = "='" & MyPath & "\[" & f.Name & "]Sheet1'!A1&"""""
                .Range("A1:Z100").Value = Range("A1:Z100").Value
                MDCtr = MDCtr + 1
                MyData(MDCtr) = Range("A3", Range("A2").End(xlDown).End(xlToRight))
            End If

but the original range (A1:Z100) must still already encompass the maximum size you think you'll reach. And there's a possibility of a memory error if there isn't a value to find.

Also note that in this example, MyData is an array of ranges. To reference a particular value, do this:

x = MyData(1) (2, 3)

where 1 is the number of the workbook, 2 is the row, and 3 is the column. You can of course get even more complicated if you want to save multiple sheets per workbook.

Hope this helps.

 

[Fazza]

if the data is structured like a database, the workbooks (or text files, csv, mdb (MS Access), etc, etc. MANY file types) don't need to be opened.

one way is using ADO. Google will find lots of information. an excellent resource from years ago is http://www.xtremevbtalk.com/tutors-corner/217783-using-ado-excel-files.html

above is via VBA

without VBA, you can just query from a worksheet. just set it up & refresh like a pivot table. implementation will be version specific, suggest refer Excel help or google. in old versions, ALT-D-D-N gets you started

HTH