Solved

Run a Macro to update Multiple Excel workbooks

Posted on 2011-09-06
5
495 Views
Last Modified: 2012-05-12
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
Comment
Question by:p-plater
  • 3
  • 2
5 Comments
 
LVL 11

Expert Comment

by:Visio_Guy
Comment Utility
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
 

Author Comment

by:p-plater
Comment Utility
No cahnge.
The macro is making other changes that work.
it's ony the refresh that won't
0
 

Accepted Solution

by:
p-plater earned 0 total points
Comment Utility
       
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
 

Author Closing Comment

by:p-plater
Comment Utility
The Responses didn't solve the Problem but I found a solution after further Searching EE
0
 
LVL 11

Expert Comment

by:Visio_Guy
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now