Hi everyone.
I am fighting since quite some time with a specific problem. I have 3 layers of Excel-files, and the top most (a kind of cockpit view) takes data from the layer 2. Layer 2 has been made so that it will get the FX-rate from a single file (layer 3). This 2nd-to-3rd layer works fine (means as soon as I open a layer 2 file, the current FX-rate from level 3 file is taken).
The problem I have is that, in order for my system to work, I intend to run a macro from the cockpit-view that will open the various files on level 2 (this will then update the FX in them autimatically) and immediately close again. This is just done so that to refresh the level 2 with the updated FX.
If I am in the cockpit view and I do the operation manually (open file_level_2.a, save file_level_2.a, close file_level_2.a, open file_level 2.b etc), everything works fine, and the cockpit view is updated as desired.
But when I try to "automate" this task with a macro (see below), then it just doesn't work, the level 2 files are not updated and thus also the cockpit is not.
I do not understand, as the macro does exactly what I would do manually, but there's obviously a difference in result.
Here the macro:
Sub Macro1()
'
' Macro1 Macro
'
'
ChDir "C:\xxx"
Workbooks.Open Filename:= _
"C:\xxx\aaa Calculation.xlsm"
Workbook.Save
Workbook.Close
Workbooks.Open Filename:= _
"C:\xxx\bbb Calculation.xlsm"
Workbook.Save
Workbook.Close
Workbooks.Open Filename:= _
"C:\xxx\ccc Calculation.xlsm"
Workbook.Save
Workbook.Close
End Sub
Any help is highly appreciated.