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

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

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.

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
DoEvents
ActiveWorkbook.Save
End Sub

---

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

Quote:

---

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).

---

Quote:

---

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

Quote:

---

Originally Posted by ashleyuk1984

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

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.

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
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