asked on

Run a Macro to update Multiple Excel workbooks

Hello

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?

Thanks

Chris Roth

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

  '...do stuff

End Sub

Open in new window

Ron Kidd

ASKER

No cahnge.
The macro is making other changes that work.
it's ony the refresh that won't

ASKER CERTIFIED SOLUTION

Ron Kidd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ron Kidd

ASKER

The Responses didn't solve the Problem but I found a solution after further Searching EE

Chris Roth

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