Pause VBA Code to Allow Data to Refresh


  • I have a file that I'm attempting to setup to be automated using the On Open.

    There are two data connections that are linked and refreshed after the value in cell B8 is changed.

    Background Refresh has been turned off on both.

    I'm trying to make the code stop long enough for the refresh to occur before moving on to the file Save As step.

    Below is the code I have currently (apologies if formatting is what everyone is accustomed to; new to writing VBA Code):


    JMData Consultant

    Private Sub WORKBOOK_OPEN()
    Dim ENDDATE As Date
    Dim TODAY As Date
    TODAY = Now()
    ENDDATE = WorksheetFunction.EoMonth(TODAY, -1)
    Worksheets("Recap - Automated").Activate
    ActiveCell.Value = ENDDATE
    Application.Speech.Speak "Macro Routine has been paused for 3 Minutes to Allow for Data Refresh."
    Application.Wait (Now + TimeValue("0:03:00"))

    Application.Speech.Speak "Now Attempting Save Workbook As Subroutine."

    Application.DisplayAlerts = False
    ThisWorkbook.SaveAs Filename:="H:\Automated Report Files\Monthly Close\xxxxx" & Format(ENDDATE, "yymm") & ".xlsx", FileFormat:=51
    Application.DisplayAlerts = True
    Application.Speech.Speak "File has been saved."

    Application.Speech.Speak "Starting Waiting Period for User input. You will have 5 Seconds to Stop the Process."
    Dim AckTime As Integer, InfoBox As Object
    Set InfoBox = CreateObject("WScript.Shell")
    AckTime = 5

    Select Case InfoBox.Popup("Click OK to Stop the process," & vbCrLf & _
    "and start using file. If NO Selection is made with 5 seconds the Process will continue.", _
    AckTime, "You may stop or continue", 0)

    Case 1 'When user clicks OK, new message box appears.
    MsgBox "You clicked OK." & vbCrLf & _
    "You can resume working in this workbook.", 64, "Process halted."
    Exit Sub

    Case -1 'When no user action takes place, the following VBA Code will be run
    Application.Speech.Speak "No User Selection was made. This file and program will now be closed. Goodbye."

    End Select

    Wednesday, August 28, 2013 4:21 PM


All replies