Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Problems saving/closing files with VBA macros

0

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.

excelmacros-vba
MS Office Version: Excel 2016
posted by: theswissguy_ch on Tue May 23, 2017 at 7:41 am
Answer
Discuss

Discussion

Is this the entire macro or is there more to it? Because there is A LOT that could affect this if you have other macros running at the same time or calling this macro or if this macro should call other macros to do the updating. On that note, how is the cockpit view updated after you open files from level 2?
don (rep: 1989) May 23, '17 at 9:00 am
There is no other macros running. I must admit that I am fairly new to the issue of macros, and all I wanted to achieve was to open and immediately re-save a certain numbe of files. If I open and re-save the files manually, one by one, then all is fine. But if I do THE SAME JOB by this macro, then the update is not happening.
theswissguy_ch May 23, '17 at 10:39 am
Add to Discussion

Answers

0

Try adding this line in your macro:

Application.Calculate

Your code could look like this:

Workbooks.Open Filename:= _
        "C:\xxx\aaa Calculation.xlsm"
        Application.Calculate
    Workbook.Save
    Workbook.Close

And if you want the macro to work faster, put this line at the top of all of the code:

Application.ScreenUpdating = False

And this line at the bottom of all of the code:

Application.ScreenUpdating = True
posted by: don (rep: 1989) on Wed May 24, 2017 at 1:45 am
Discuss

Discussion

Hi Don,

I tried your advice and, reading your suggestions, I already thought "this is it". But unfortunately, no change in the behaviour. I also tried, as I always did, with different settings on how the "connections" are refreshed.

So unfortunately I am still stuck in not being able to update my cockpit-view if run with the macro, but working perfectly well when I open/save/close the "input-files" manually.

Any other thoughts...?
theswissguy_ch May 24, '17 at 2:53 am
...And I just single-stepped through my macro (F8), and have indeed noticed that the level-2 files are opened but NOT updated (despite the "application.calculate" command) before being saved & closed (i.e. unchanged).
theswissguy_ch May 24, '17 at 4:36 am
Ok, so the next question is, what do you exactly mean by "updated" for these workbooks? Are there formulas in them that reference each other? Be specific.
don (rep: 1989) May 24, '17 at 9:10 am
Sorry for maybe not being clear.

Layer 3:
I have a signe Excel file with various foreign exchange rates, which is updated manually 1x per month.

Layer 2:
Then there are a whole bunch of other Excel-files that do some specific calculations on our product, and they all take the FX rate from this one FX file as basis. And as soon as I open one of these files, indeed the FX is instantaneously updated and the calculations are done with the new values.
The transfer of the FX rates is done with Excel's Data-connections.

Layer 1:
Third and last is a single cockpit-file, which takes certain key-data from the layer-2 files (in fact only 2 values from the layer-2 files are important for us on the cockpit-view).
Now I have created in this file a macro which basically automatically opens / calculates / saves / closes each and every file from layer 2. I have also added your suggested "calculate" command into it.
theswissguy_ch May 26, '17 at 10:20 am

This "open/calculate/save/close" activities are a kind of refresh, to ensure that all these layer-2 files are up-to-date and have the current FX rate as calculation basis. The data on the cockpit view is then updated through Excel's data-connection feature, where the required data from all the layer-2 files, now updated through the described macro, can pass the updated data onto the cockpit.
Now if I single-step the macro, then the execution works fine, and all data on the cockpit-view are updated once I make a "refresh all" (my last command in the Macro). But if I let the macro "free-run", it will not do the job.
I read someahere that the execution may be too fast. So I tried (so far in vain) to include some waiting-loops. I say in vain, because they always somehow return an error message
theswissguy_ch May 26, '17 at 10:20 am
We have a tutorial on that that should help:
Pause a Macro or Make it Slow Down in Excel
Read the tutorial for more information, but this is the simplest way to do it from there:
Application.Wait(Now + TimeValue("00:00:01"))
don (rep: 1989) Jun 4, '17 at 1:10 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login