Consulting

Results 1 to 3 of 3

Thread: delay in a macro?

  1. #1
    VBAX Newbie
    Joined
    May 2014
    Posts
    2
    Location

    delay in a macro?

    Hi
    I have a simple macro that updates and save a file that often needs to be updated. The file updates from an external data source and there is a delay until the data is downloaded. Is there any way to delay the program a few seconds until the data is downloaded and then jump on the save action?

    Sub Kampanjstorlek()
    '
    ' Kampanjstorlek Makro
    ' Öpnnar filen kampanjstrolek och uppdaterar och sparar.
    '
    ' Kortkommando: Ctrl+a
    '
    Workbooks.Open Filename:= _
    "K:\Assortment & Purchasing\AO Egenvård\8. Layout & Space\6. Butiks & Kampanjinfo\Kampanjstorlek\Kampanjstorlek.xlsx"
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Save
    End Sub

    Sincerely
    Peter

  2. #2
    First of all, use CODE tags when posting a full code.


    Sub Kampanjstorlek()
    '
    ' Kampanjstorlek Makro
    ' Öpnnar filen kampanjstrolek och uppdaterar och sparar.
    '
    ' Kortkommando: Ctrl+a
    '
    Workbooks.Open Filename:= _
    "K:\Assortment & Purchasing\AO Egenvård\8. Layout & Space\6. Butiks & Kampanjinfo\Kampanjstorlek\Kampanjstorlek.xlsx"
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Save
    End Sub

    Perhaps add DoEvents to your code after the ActiveWorkbook.RefreshAll? That might work.


    Sub Kampanjstorlek()
    '
    ' Kampanjstorlek Makro
    ' Öpnnar filen kampanjstrolek och uppdaterar och sparar.
    '
    ' Kortkommando: Ctrl+a
    '
    Workbooks.Open Filename:= _
    "K:\Assortment & Purchasing\AO Egenvård\8. Layout & Space\6. Butiks & Kampanjinfo\Kampanjstorlek\Kampanjstorlek.xlsx"
    ActiveWorkbook.RefreshAll
    DoEvents
    ActiveWorkbook.Save
    End Sub

    I've just found this on another forum. http://www.mrexcel.com/forum/excel-q...ml#post3009948


    Do this via selecting one of the cells in your external data range and in 2007+ hit the Properties button on the Data ribbon (or in 2003 right-click the mouse and select Properties - I think, can't quite remember).


    Now, uncheck the box for Enable Background Refresh. Save the workbook to fix the property. Then you can simply have your subsequent code immediately following the RefreshAll line.

    Hope this helps

  3. #3
    VBAX Newbie
    Joined
    May 2014
    Posts
    2
    Location
    Quote Originally Posted by ashleyuk1984 View Post
    First of all, use CODE tags when posting a full code.


    Sub Kampanjstorlek()
    '
    ' Kampanjstorlek Makro
    ' Öpnnar filen kampanjstrolek och uppdaterar och sparar.
    '
    ' Kortkommando: Ctrl+a
    '
    Workbooks.Open Filename:= _
    "K:\Assortment & Purchasing\AO Egenvård\8. Layout & Space\6. Butiks & Kampanjinfo\Kampanjstorlek\Kampanjstorlek.xlsx"
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Save
    End Sub

    Perhaps add DoEvents to your code after the ActiveWorkbook.RefreshAll? That might work.


    Sub Kampanjstorlek()
    '
    ' Kampanjstorlek Makro
    ' Öpnnar filen kampanjstrolek och uppdaterar och sparar.
    '
    ' Kortkommando: Ctrl+a
    '
    Workbooks.Open Filename:= _
    "K:\Assortment & Purchasing\AO Egenvård\8. Layout & Space\6. Butiks & Kampanjinfo\Kampanjstorlek\Kampanjstorlek.xlsx"
    ActiveWorkbook.RefreshAll
    DoEvents
    ActiveWorkbook.Save
    End Sub

    I've just found this on another forum.

    Hope this helps
    Cheers AshleyUK!

    I unchecked the background refresh. It seems that the problem was not that Excel did not wait to save before the update was done, instead as stated in the mentioned thread, it started an automatic update that cancels the update in the macro!

    Sorry for my useless English.

    Sincerely
    Peter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •