krisso

Active Member
Joined
Sep 16, 2005
Messages
291
Can anyone help with the below code.

I keep getting an error of "This will cancel a pending data refresh. Continue?" even though all the tables have updated and then when the save happens I am getting a box to ask if I want to save changes. I thought what I had below would just save the changes automatically. I just need this workbook to open, refresh, save and close.

Thanks
Chris

Sub Updatebillingfigures()
'
' Updatebillingfigures Macro
'
'
Workbooks.Open Filename:= _
"\\dellddp91234\Private\Chris\Billing analysis for daily figures.xlsm"
ActiveWorkbook.RefreshAll
DoEvents
Application.Wait (Now + TimeValue("0:01:00"))
ActiveWorkbook.Save
DoEvents
Application.Wait (Now + TimeValue("0:00:45"))
ActiveWindow.Close
End Sub
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

GirishDhruva

Active Member
Joined
Mar 26, 2019
Messages
308
Hi @krisso,
Try with this , untested
Code:
Sub Updatebillingfigures()
Workbooks.Open Filename:= _
"\\dellddp91234\Private\Chris\Billing analysis for daily figures.xlsm"
ActiveWorkbook.RefreshAll
ActiveWorkbook.Close SaveChanges:=True
End Sub

Regards,
Dhruva
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
Hi Chris
I'm no expert but...
I'm guessing that you may be getting the "Pending data refresh" warning, because the links are automatically beginning to refresh - due to opening of the workbook, so if you then try to "Refreshall" you're trying to overwrite that operation. It might be worth just skipping that line. I've remmed it out, below, so you can just un-rem it should you wish to.

Secondly, the fact that you have a "save" command doesn't stop Excel from wanting to know whether to save or not before close.
Best thing would be to remove your .save line (I've remmed it out for now) and qualify your save on close with a "True" statement - instructing Excel to save changes, so it doesn't need to ask.
BTW - I've changed your "Activewindow.close" to "Activeworkbook.close".
I've also remmed out your second "Wait" line, as this may not be necessary now, either.
Code:
Sub Updatebillingfigures()
'
' Updatebillingfigures Macro
'
'
Workbooks.Open Filename:= _
"\\dellddp91234\Private\Chris\Billing analysis for daily figures.xlsm"
'ActiveWorkbook.RefreshAll
DoEvents
Application.Wait (Now + TimeValue("0:01:00"))
'ActiveWorkbook.Save
DoEvents
'Application.Wait (Now + TimeValue("0:00:45"))
'ActiveWindow.Close
ActiveWorkbook.Close (True)
End Sub
Have a go, and see how you get on.
 
Last edited:

krisso

Active Member
Joined
Sep 16, 2005
Messages
291
Hi Chris
I'm no expert but...
I'm guessing that you may be getting the "Pending data refresh" warning, because the links are automatically beginning to refresh - due to opening of the workbook, so if you then try to "Refreshall" you're trying to overwrite that operation. It might be worth just skipping that line. I've remmed it out, below, so you can just un-rem it should you wish to.

Secondly, the fact that you have a "save" command doesn't stop Excel from wanting to know whether to save or not before close.
Best thing would be to remove your .save line (I've remmed it out for now) and qualify your save on close with a "True" statement - instructing Excel to save changes, so it doesn't need to ask.
BTW - I've changed your "Activewindow.close" to "Activeworkbook.close".
I've also remmed out your second "Wait" line, as this may not be necessary now, either.
Code:
Sub Updatebillingfigures()
'
' Updatebillingfigures Macro
'
'
Workbooks.Open Filename:= _
"\\dellddp91234\Private\Chris\Billing analysis for daily figures.xlsm"
'ActiveWorkbook.RefreshAll
DoEvents
Application.Wait (Now + TimeValue("0:01:00"))
'ActiveWorkbook.Save
DoEvents
'Application.Wait (Now + TimeValue("0:00:45"))
'ActiveWindow.Close
ActiveWorkbook.Close (True)
End Sub
Have a go, and see how you get on.

Works perfectly. Thank you Sykes.
 

krisso

Active Member
Joined
Sep 16, 2005
Messages
291

ADVERTISEMENT

Hi @krisso,
Try with this , untested
Code:
Sub Updatebillingfigures()
Workbooks.Open Filename:= _
"\\dellddp91234\Private\Chris\Billing analysis for daily figures.xlsm"
ActiveWorkbook.RefreshAll
ActiveWorkbook.Close SaveChanges:=True
End Sub

Regards,
Dhruva

I was still getting the error Dhruva, I suspect because the tables refresh on opening as stated below. Thank you for the reply though
 

krisso

Active Member
Joined
Sep 16, 2005
Messages
291
Works perfectly. Thank you Sykes.

Maybe spoke too soon, as I have now posted some more figures but the macro isn't refreshing the data. I have tried it with ActiveWorkbook.RefreshAll Un-Rem but still no updated figures
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

2 questions, Chris:

1. Did the save=True line solve the savechanges prompt issue?
2. Where's the "Billing analysis for daily figures.xlsm" file getting its data from/ sending its data to?
 

krisso

Active Member
Joined
Sep 16, 2005
Messages
291
Yes that worked for the prompt issue. The data is being refreshed from 4 sql tables on our server
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
I'm guessing that's the issue - in that area somewhere.
The only thing I can think of, is to incrementally increase the wait time to a value that DOES work. It'd be a workaround, which I don't like, but might solve the immediate issue:
Code:
Sub Updatebillingfigures()
'
' Updatebillingfigures Macro
'
'
Workbooks.Open Filename:= _
"\\dellddp91234\Private\Chris\Billing analysis for daily figures.xlsm"

Application.Wait (Now + TimeValue("0:02:00"))

ActiveWorkbook.Close (True)
End Sub
This MS article about updating links light be of interest: https://support.microsoft.com/en-gb...up-message-about-updating-linked-workbooks-in it only seems to cover up-to Excel 2010, though.
Are your links set to "Automatic?" if not, then that's why they're not updating without the call to "Refreshall."
 

Watch MrExcel Video

Forum statistics

Threads
1,129,430
Messages
5,636,232
Members
416,909
Latest member
hayleyg123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top