Run a Macro to update Multiple Excel workbooks


I have a VBA macro utility that opens each excel workbook in a folder makes some formating changes and runs a workbook.refreshall command then uses a ActiveWorkbook.Close savechanges:=True to close and save the changes.
But when I reopen the workbook it has not updated.
If I remove the close command it updates.

How do I make the connections update while the macro is running or make the macro wait till the updating has finished?

Who is Participating?
p-platerConnect With a Mentor Author Commented:
Worked it out

I changed the connections to BackgroundRefresh = False
then looped through the number of connections

 cons = ActiveWorkbook.Connections.Count
For i = 1 To cons
Next i
Hi PP,

I'm not the authority here, but I thought I'd throw out a few ideas:

1. Check that you aren't opening the workbooks as read-only. It may be that Excel opens read-write by default, but I thought I'd throw that out, just in case. Ie:

  Dim xlWb As Excel.Workbook
  Set xlWb = xlApp.Workbooks.Open("filename", , False)

Open in new window

2. Perhaps do your work in a second instance of Excel? I do this a lot when working with Visio macros that edit documents. I open the documents in a 'clean' instance of Visio, separate from where the macro is running.

Sub StartExcel()

  Dim xlApp As Excel.Application
  Set xlApp = CreateObject("excel.application")
  xlApp.Visible = True

  ' stuff

End Sub

Open in new window

p-platerAuthor Commented:
No cahnge.
The macro is making other changes that work.
it's ony the refresh that won't
p-platerAuthor Commented:
The Responses didn't solve the Problem but I found a solution after further Searching EE
Glad you worked it out and thanks for sharing. I thought the Connections were used when referencing external data, and weren't related to formatting changes. Interesting. <off to read more about Workbook.Connections...>
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.