Solved

Run a Macro to update Multiple Excel workbooks

Posted on 2011-09-06
5
505 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 11

Expert Comment

by:Visio_Guy
ID: 36493974
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
ID: 36499479
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
ID: 36499704
       
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
ID: 36521441
The Responses didn't solve the Problem but I found a solution after further Searching EE
0
 
LVL 11

Expert Comment

by:Visio_Guy
ID: 36501064
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

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

726 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