Solved

Updating Excel links referencing a different workbook on a different server, taking too long

Posted on 2010-11-15
13
572 Views
Last Modified: 2013-11-25
Hello,

I'm trying to update links in a workbook that reference a different workbook located on a different server.  The workbook that I'm trying to update contains in excess of 10,000 links per sheet.  (this is a financial model that runs, referencing many other workbooks).  To load the cell address and link information into an array for the entire workbook takes seconds. However to update the links to point to a different server requires about 10 minutes per 1,000 links.  That is roughly 1.7 hours per sheet.  Every time a link is updated, it pauses momentarily to check the link and insert the value.  -- Is there a way I can suppress Excel from going out and checking the link and just update the cell contents.  I'm not concerned if the cell reference  throws a #REF for right now.  Once the workbook has completed updating, the suppression can be turned off and the workbook saved.  Upon opening the workbook, cell references would be checked, which is a lot faster than one cell at a time.

Summarized:
 Can someone please advise me as to how to update a cell formula with fully qualified address to another workbook, suppressing the referential check.  Below is a  sample link reference:

=+IF(AW2<='\\<<old server address>>\Sales Model\[Sales Plan Model.xls]Input'!$B$22,1,0)

=+IF(AW2<='\\<<new server address>>\ Sales Model\[Sales Plan Model.xls]Input'!$B$22,1,0)

The code below doesn't work in my case:
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
Application.Calculation = xlManual

Thanks in advance
0
Comment
Question by:ss44
  • 7
  • 4
13 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
What about the 'Update Remote References' option? Uncheck that. It should work.
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
Code would be
ThisWorkbook.UpdateRemoteReferences = False
0
 

Author Comment

by:ss44
Comment Utility
Just tried your suggestion.  No difference.  It took about a minute to update 100 links.  That still comes out to about 1.5 hours per worksheet.  Its pausing about about a second to update.  Should be milliseconds.
---------------------------------------------------------------
Sub test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
Application.Calculation = xlManual
ThisWorkbook.UpdateRemoteReferences = False

Workbooks.Open Filename:= _
        "\\<deleted path>>\sales plan\op plan 2010\2010OpPlanSales", _
        updateLinks:=xlUpdateLinksNever
Sheets("Model").Activate
ThisWorkbook.UpdateRemoteReferences = False
-----------------------------------------------------------------

Elapsed time for 100 links:
11:12:21 AM
11:13:18 AM
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
Ah, I see the problem. It changed the setting on the wrong workbook. Do something like this:
Set wbMain = Workbooks.Open Filename:= _
        "\\<deleted path>>\sales plan\op plan 2010\2010OpPlanSales", _
        updateLinks:=xlUpdateLinksNever
Sheets("Model").Activate
wbMain.UpdateRemoteReferences = False

Open in new window


You also might need to change the setting and save and close the workbook first so it won't do it on open. (You can change the setting in the options not just in the code)
0
 

Author Comment

by:ss44
Comment Utility
Changing the setting in the workbook won't work, because there are hundreds of workbooks that this code will act on.  I went ahead and tried it anyway to see if it would get me there, but as it was it had no effect on the run time.

Here's what I did.  Changed the settings in the workbook, then saved and closed the workbook in a different directory.  Re-opened the workbook with the following code.

Sub test()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.AskToUpdateLinks = False
Application.Calculation = xlManual
Dim wbmain As Object
Workbooks.Open Filename:= _
        "\\<<server>>\FinanceLinkUpdates\temp.xls", _
        updateLinks:=xlUpdateLinksNever
Sheets("Model").Activate
Set wbmain = ActiveWorkbook
wbmain.UpdateRemoteReferences = False

No Change.  

The settings in the workbook itself is unchecked for update remote references and calculation check for manual.
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
How are you updating the links? Have you tried doing replace all? I believe that will change all the links before updating. You can do it from the code or from the user interface.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ss44
Comment Utility
The updated string can be at any place within the reference.  

Do
    Set x = Cells.Find(oldDir, Range(lastcell), xlFormulas, xlPart, xlByRows, xlNext, False, False)
    If Not x Is Nothing Then
        If ct = 0 Then
            bcell = x.Address
            End If
        lastcell = x.Address
        ct = ct + 1 'Counter
      ReDim Preserve addr(k), lnk(k)
      addr(k) = x.Address
      lnk(k) = Range(x.Address).Formula
      k = k + 1
      Else
      Exit Do
      End If
    Loop Until (Range(bcell).Address = Range(lastcell).Address) And ct > 1

Then run through the array using the replace function. I use this because it will replace all occurrences of the old string with the new string.

For i = 0 To UBound(addr)
    lnk(i) = Replace(lnk(i), oldDir, newDir)
    Next

Then replace each cell with the old string with the new string.

For i = 0 To UBound(addr)
    Range(addr(i)).Formula = lnk(i)
    Next

It takes about 2 seconds to find over 10,000 occurrences with the old link and replace with new string, including loading the arrays.  The last for loop (above) is pausing about a second on each iteration.

0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 250 total points
Comment Utility
Why not just do the whole thing at once?
Something like
    Cells.Replace What:=oldDir, Replacement:=newDir, LookAt _
        :=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
0
 

Author Comment

by:ss44
Comment Utility
Tried your suggestion. It brought the time down by 1/2 hour.  For two directories that I need to update this translates to 5 hours to update links (one directory has over 48,000 links contained in 22 workbooks).  The key here to suppress Excel from checking the link and just take the formula as it were text.

One thought.  Is there a way to update a closed workbook?  Maybe using Java or Perl? Has anyone done this and is it stable.
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
Could very well be. What version of Excel was the file made in? This was going to be my last resort suggestion. (Always add your Excel version when asking questions on EE in the future please. It helps us a lot.)
0
 
LVL 37

Expert Comment

by:TommySzalapski
Comment Utility
Open your file in Wordpad and search for the old string that you want to change. If it finds it, you have a format that will work. (Probably stable, save a backup just in case)

If you can save the file in Excel as an xml file then the change will be super easy. Stable, if no unsupported features are used.

0
 
LVL 24

Expert Comment

by:broomee9
Comment Utility
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

771 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

11 Experts available now in Live!

Get 1:1 Help Now