• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

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
0
p-plater
Asked:
p-plater
  • 3
  • 2
1 Solution
 
Visio_GuyCommented:
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

0
 
p-platerAuthor Commented:
No cahnge.
The macro is making other changes that work.
it's ony the refresh that won't
0
 
p-platerAuthor 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
        ActiveWorkbook.Connections(i).Refresh
Next i
0
 
p-platerAuthor Commented:
The Responses didn't solve the Problem but I found a solution after further Searching EE
0
 
Visio_GuyCommented:
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...>
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now