Macro to open 215 Workbooks and get data from them

[stathisaska]

Dear all,

I want to import data from 215 workbooks formatted the same way.
I have created a summary workbook which contains all filenames in A1:A215

I have created a macro to open all which it is like this

Sub open_all()
Dim filenames(215) As String
Dim time1 As Date
Dim time2 As Date


time1 = Now


    
    For i = 1 To 215
        filenames(i) = "/Users/soldier/Library/Containers/com.microsoft.Excel/Data/files/" & Cells(i, 1).Value
    Next


MsgBox ("Filenames copied")


Dim WB As Workbook


    For i = 1 To 10
    Set WB = Workbooks.Open(filenames(i))
    Next


time2 = Now


msgbox(time2-time1)


End Sub

It takes 8 seconds to open 10 workbooks. I tried more and more, but for 215 it stuck.

I am running an iMac with a fusion drive and those specifications:
Processor Name: Intel Core i5
Processor Speed: 3,2 GHz
Number of Processors: 1
Total Number of Cores: 4
L2 Cache (per Core): 256 KB
L3 Cache: 6 MB
Memory: 8 GB

The workbooks have a total of 35mb


My questions:

1. Do you believe this task it's not possible ?
2. Can I import data without even opening workbooks
3. Can i open workbooks and keep them hidden
4. Shall I open workbooks 1 by 1: get the information I need, close the workbook and then proceed to next one
5. Would you recommend any other method to get the outcome I need ?

 

[BITechMacro]

Hi, Put all of your workbooks in a folder. And open A workbook for import data from those Workbooks.

paste this code below.

Sub FromAllWorkbooks()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Dim WB As Workbook 

Dim path As String

Dim Wbooks As String 

path = "Your Workbooks FilePath\"

dont forget to put \ end of your file path.

Wbooks = Dir(path & "*.xlsx") 

Do While Wbooks<> "" 

Set WB= Workbooks.Open(path& Wbooks)
 Range(Range("a2"), Range("a100000").End(xlUp).End(xlToRight)).Copy

Range("a1").CurrentRegion.Copy

After you copy the data, activate the other workbook, paste the data, close the workbook,
switch to other workbook until all the workbooks will be runned

Workbooks("YourConsolidationWorkbookName").Activate 

Range("a100000").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)

WB.Close 
Wbooks= Dir()
Loop
End Sub

Or you can use Power Query

 

[oldbrewer]

how many sheets in each workbook

 

[FDibbins]

Why do you need VBA for this?

You could put a formula together that will do what you want.
You *could* use INDIRECT(), but the other file/s need to be open, but if this is a constant process with the same files always being used, you could take some time and set up the formulas to reference each WB

 

[Norie]

Do not open all the workbooks at the same time.

Open a workbook, get the data from it, close it and move onto the next workbook.

 

[stathisaska]

7 sheets

how many sheets in each workbook