View Full Version : [SOLVED] 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
ashleyuk1984
05-30-2014, 05:43 AM
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-questions/607332-excel-visual-basic-applications-refresh-wait.html#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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.