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

Delaying moving on to next part of a macro

0

I have a macro that works perfectly fine when I'm in the office and everything is quick. But it gives an error/crashes when I try to do it remotely because the connection is slower.

I think what's happening is, the macro moves onto the next step right after it connects to Access, rather than waiting until the pivot tables are actually refreshed (which takes time due to remote connection and Access having rather large amount of data.)

Is there any way to tell the macro not to move on until the pivot tables are fully refreshed?

Thanks.

Sub NewTemplates()

    Dim i As Long
    Dim k As Integer

    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ActiveWorkbook
    Set ws = ActiveSheet

    k = ActiveSheet.Range("H1").End(xlDown).Row

    MyPath = Range("MasterLocation").Value
    Fcst = Range("FcstLocation").Value

    Workbooks.Open Filename:=MyPath & "SGA Master Template.xlsx"

    ActiveWorkbook.RefreshAll

    For i = 1 To k

        Range("_Office").Value = ws.Range("H" & i).Value
        Range("_HCOffice").Value = ws.Range("H" & i).Value

        ActiveWorkbook.SaveAs Filename:= _
        Fcst & ws.Range("H" & i).Value & " Fcst " & ws.Range("FiscalYr").Value & ".xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False

    Next i

    ActiveWindow.Close
    MsgBox "New Templates Generated at " & Fcst, vbInformation


End Sub

The Range("_HCOffice") is actually a report filter of a pivot table that has the setting "Save source data with file" unchecked. Hence the macro gives an error message when it tries to change it before the table is refreshed.

I've set it up this way in order to save on file size when the template is saved. 

excelmacros-vba
posted by: Cathy (rep: 53) on Mon Dec 24, 2018 at 11:48 am
Answer
Discuss

Answers

0
Selected Answer

Having the pivot table's data connection's refresh setting correctly is key. The "Enable background refresh" option should be unchecked. This will prevent the macro from moving on to the next step until the pivot table is fully updated.

On the "Data" ribbon -> Connections -> select your connection then "Properties" -> "Usage" tab -> "Refresh Control" section -> "Enable background refresh" UNCHECKED

posted by: Cathy (rep: 53) on Mon Dec 31, 2018 at 10:32 am
Discuss
0

Hi Cathy,

There are two solution to your problem at this link.

One suggests to save the file with the pivot tables immediately after the Refresh. The argument is that Save will make Excel wait for the completion of the Refresh action. You might just move the SaveAs in your code to execute right after the Refresh command. Unfortunatley, this solution didn't get any nods of approval in that thread.

The other solution proposes to catch the error in a loop and wait for a second at a time until it has disappeared. There is code to do this at the link (courtesy of AD_Taylor).

I would have put this code in your procedure but found it too hard because your objects are a bit messy :-). When you open a workbook that workbook becomes the ActiveWorkbook. Of course, that workbook has an ActiveSheet. Therefore The ActiveSheet changes upon the Open command.

All range references not specifying a worksheet are understood to refer to the ActiveSheet. That goes for Range("MasterLocation") as well as Range("_Office") which are, however, on different sheets. Wb is set to be the ActiveWorkbook but never used. Finally, you close the ActiveWindow. What's happening to the ActiveWorkbook at that time?

As you say, it all probably works. But that is because Excel can guess your intention. Should it guess wrong at any time you will be flabbergasted.

Three more tips.

  1. Use Option Explicit at the top of the code.
    In the VBE window, click Tools > Options.
    Checkmark everything on the Editor tab.
    That will include Require Variable Declaration, which will insert Option Explicit on every new module automatically.
  2. Always declare multi-character variable names using caps & smalls (Wb instead of wb). Then, as you do your programming, always write names in lower case. Excel will change the case.
    - Declare Wb
    - Type wb
    - See Wb
    If Excel doesn't change the case you typed the name wrong. It's an automatic check which will save you tons of time.
    Only works when Option Explicit is specified and, unfortunately, doesn't work for Enums (both built-in and declared)
  3. Try to wean yourself of the beginner's way of defining ranges like Range("H" & i). You are no longer a beginner and using this kind of addressing will hold you back. It's Cells(i, 8) or Cells(i, "H") if you absolutely must. The point is that you already started calculating row numbers. Soon you will also start caculating column numbers as well. Then your addressing system won't work any more.
posted by: Variatus (rep: 4889) on Mon Dec 24, 2018 at 8:25 pm
Discuss

Discussion

The delay loop and Save workbook options didn't work. Once the macro went into the loop it actually stopped the pivot table refresh altogether and it was stuck on an endless loop. 

The Save option waits until excel connects to the database but it was trying to save while the PT was still refreshing which ends up giving me another message. In the process of googling THAT message, I found the simplest of answers.

I had the data connections property "Enable background refresh" checked which basically was telling excel to move on once a connection is established. That's great if I'm just in a straightforward workbook and want to refresh and do other things. Not so much if the "other things" are the rest of a macro.

I unchecked the "Enable background refresh" option and the macro doesn't move on to the next step until all the tables are refreshed. No mess, no fuss.

Thanks for the continued support and I'll try my best to work on the cleanliness of my codes.
Cathy (rep: 53) Dec 27, '18 at 3:31 pm
Hi Cathy,
Since you found teh answer yourself the recommended procedure here is to post it as an answer and "Select" it. Please go ahead and do that. You will earn reputation points for your trouble.
Happy New Year!
Variatus (rep: 4889) Dec 27, '18 at 7:38 pm
Add to Discussion


Answer the Question

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