VBA Help

[krisso]

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

 

[GirishDhruva]

Hi @krisso,
Try with this , untested

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]

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.

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.

 

[krisso]

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.

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]

Hi @krisso,
Try with this , untested

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]

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]

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]

Yes that worked for the prompt issue. The data is being refreshed from 4 sql tables on our server

 

[sykes]

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:

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