VBA Help

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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
Yes that worked for the prompt issue. The data is being refreshed from 4 sql tables on our server
 
Upvote 0
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."
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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