Solved

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

Posted on 2010-11-15
13
579 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
ID: 34137852
What about the 'Update Remote References' option? Uncheck that. It should work.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34137864
Code would be
ThisWorkbook.UpdateRemoteReferences = False
0
 

Author Comment

by:ss44
ID: 34137983
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34138045
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
ID: 34138340
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
ID: 34138893
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
 

Author Comment

by:ss44
ID: 34139442
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
ID: 34140885
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
ID: 34146971
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
ID: 34147497
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
ID: 34147571
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
ID: 34424476
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
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…

816 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

8 Experts available now in Live!

Get 1:1 Help Now