Open Multiple excel files using VBA

[Kayslover]

Hi

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

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.

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?

 

[JLGWhiz]

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]

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]

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.

 

[Kayslover]

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]

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:-

Dim ws As Worksheet
Path = ThisWorkbook.Path & "\"

I tried to change the above to say

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]

fPath is the variable:

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:

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.

 

[Kayslover]

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

=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]

You need to start a new thread for the additional requirement, since it does not fall within the scope of this thread subject.

 

[Kayslover]

JLGWhiz,

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

Thank you for your assistance.