Open Multiple excel files using VBA

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows
Hi

I have a macro that create 12 files from a template using the command:-

Rich (BB code):
ActiveWorkbook.SaveAs Filename:=Path & "01 January.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

ActiveWorkbook.SaveAs Filename:=Path & "02 February.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

ActiveWorkbook.SaveAs Filename:=Path & "03 March.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

Etc. etc. etc.

ActiveWorkbook.SaveAs Filename:=Path & "12 December.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

I am trying to use the following VBA to open all 12 files, however it only opens the Last file and the first file. I understand the last file as there is no 'ActiveWorkbook.Close once 12 December has been saved.

Rich (BB code):
Workbooks.Open Path & "01 January.xlsm"
Workbooks.Open Path & "02 February.xlsm"
Workbooks.Open Path & "03 March.xlsm"
Workbooks.Open Path & "04 April.xlsm"
etc etc etc

Please note Path is defined in the macro with the following command Path = ThisWorkbook.Path & "\"

Is it possible once all the files have been created that I can use VBA to open them all up?
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Don't use Path as a variable because it is a reserved Excel word and has specific meaning when used in code. Use 'fPath' or some other variation but not 'Path' alone.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,295
Office Version
  1. 2013
Platform
  1. Windows
While @JLGWhiz is right with his advice, this will most likely not be the cause of your issue. If you used the ThisWorkbook_Open event procedure in your template, it could be a reason that your macro trying to open all those other 11 workbooks is prematurely aborted. Would suggest using F8 key within VBE to step trough your code.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Correction: Path is not a reserved keyword, but it is a better practice to use a variation like 'fPath' for the variable rather than 'Path' because the Path property can return a string for a workbook object like ThisWorkbook.Path would return the path string for the host workbook.

Could you post your declarations so we can see how you have you variables declared.
 
Last edited:

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Guys,

Thank you for your prompt responses. I have switched off my PC and once I switch it on I will try out what is being recommend.
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows
Guys,

I tried to step through the code by using F8 as suggested by GWteB and I noticed that the macro does open ALL the sheets (as I am taking my time to hit the F8 command).

But just running the macro without F8, it just opened files called 12 December and 01 January.

JLGWhiz, you asked to see the variables declared. Please find them below:-

VBA Code:
Dim ws As Worksheet
Path = ThisWorkbook.Path & "\"

I tried to change the above to say
VBA Code:
Path = ThisWorkbook.fPath & "\"
as suggested, but when I try and run the macro, it gives me a “compile error, which reads Method or data member not found.

Help, I am a total novice at VBA.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

fPath is the variable:
VBA Code:
fPath = ThisWorkbook.Path
wiould be the syntax. Then use the fPath variable throughout the code to reference the directory path where you are now using 'Path'.

As for the Worklbooks.Open issue, if it was opening all the workjbooks when using the F8 key, but not when running the code on automatic, then it could be a timing issue if your workbooks contain a lot of data and formulas. i.e. The code continues to run while a workbook trying to open and it skips fa line or two of code without executing them. You can use a delay snippet between workbooks to give each one time to do the open execution before moving to the next workbook.

Example:
Rich (BB code):
Workbooks.Open Path & "01 January.xlsm"
d = Timer + 2
Do While Timer < d
    DoEvents
Loop
Workbooks.Open Path & "02 February.xlsm"
d = Timer + 2
Do While Timer < d
    DoEvents
Loop
Workbooks.Open Path & "03 March.xlsm"
d = Timer + 2
Do While Timer < d
    DoEvents
Loop
Workbooks.Open Path & "04 April.xlsm"
d = Timer + 2
Do While Timer < d
    DoEvents
Loop
etc,

This will add a few seconds to your processing .time.
 
Solution

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows
JLGWhiz.

The workbooks do contain a lot of formulas (Vlookup’s and Sum).

I have inserted the loop code you have supplied and it works a treat now, so many many thanks for your assistance.

I want to automate the process as much as possible, is it possible to do the following?

Macro to ask a question as to what year the sheets are to be created for

Once this has been answered, insert the following formula
VBA Code:
=EOMONTH(A2,-1)+1+MOD((7-WEEKDAY(EOMONTH(A2,-1)+1,11)),7)
Formula is for the first First Sunday in a month in Cell B4 Sheet1 for 01 January, 02 February, 03 March etc. obviously the reference to A2 in the formula would have to change to 01/01,year from the above question in sheet1 01 January, 01/02,year from the above question in sheet1 02 February etc. etc etc.

Once the value has been populated in B4, the formula should be copied and paste special to remove the formula and finally the file to be saved.

Finally, I have a workbook called Bar Area Taking Print in the same folder as all files called 01 January, 02 February. etc. etc. etc.

I would like Cell B4 in sheet called QTR 1 to be populated with the value from B4 sheet1, 01 January.

Finally, Finally, can I run a macro from the active workbook against all workbook? If so how do I achieve this.

Is any of the above possible?
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
You need to start a new thread for the additional requirement, since it does not fall within the scope of this thread subject.
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
138
Office Version
  1. 2013
Platform
  1. Windows
JLGWhiz,

I will do some research on the Internet for #8, and will raise a thread.

Thank you for your assistance.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,129
Messages
5,628,865
Members
416,347
Latest member
AT2021

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