Importing all sheets from files in folder taking long - suggestions?

[Razorman]

Hi

I have 150 workbooks each with 40 to 90 sheets.

When I use the Excel.Workbook([Contents]) method in PowerQuery it takes very long to import the data. I then want to use another reference table to help filter the import (using a custom colun filename&sheetname as a the lookup reference between the 2 tables)

Is there a quicker way to import the data?

I've tried VBA in Access but I'm struggling to write the code to loop through the workbooks. I have code that imports all the sheets.

BTW - I'm an accountant, self-taught in VBA Excel by using online forums only. No programming background.

Any suggestions on how I should be approaching this task?

 

[XLfriend777]

Hi Razorman. As nobody has responded I thought I might try to help get you started. There are some very basic and not so basic rules to follow in speeding up VBA code. Have you checked out some of the basic rules like those explained by Kevin Roper Excel VBA Speed And Efficiency Pay specific attention to Rules #1, #2 and #11 that may apply in your case. Hope this helps get you started. All the best.

 

[Razorman]

Great!! Thanks for this resource. I never came across it before. I'm still working away at the VBA code to get this task done, so this is really useful.

I discovered "DoEvents" which has solved my problem of code suddenly stopping, but it has increased my macro run times.

I'll have a read of the link you posted.

Thanks again.