Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Updating values from external workbook automatically

I have the following workbook setup:

enter image description here

Workbook A has a link to x amount of workbook B's and fetches data from them. The workbooks B have links to some other workbooks and fetches data from them.

Workbook A is a kind of "summary" of what all the other workbooks contains. As it is now, I have to open all my workbook Bs, refresh them and save before I open workbook A. If I don't do this the workbook B's will not be updated with the data in the workbooks C.

Is it possible to update all the workbook B's using a .bat or vbs script? or is it possible to update them from within workbook A?

I might add that I use excel starter on this computer so preferly the solution would be compatible with that.

like image 480
John Snow Avatar asked Apr 15 '13 09:04

John Snow


People also ask

Can I link Excel workbooks automatic update?

Go to File > Options > Advanced. Under General, clear the Ask to update automatic links check box. If this check box is cleared, the links are automatically updated, and no alert is displayed.

How do you get Excel to automatically update numbers?

Unlike other Microsoft Office programs, Excel does not provide a button to number data automatically. But, you can easily add sequential numbers to rows of data by dragging the fill handle to fill a column with a series of numbers or by using the ROW function.

How do I get Excel to automatically update external links?

In the Excel Options window, go to the Trust Center tab, and choose Trust Center Settings… 4. In the Trust Center Settings window, (1) go to the External Content tab, (2) select Enable automatic update for all Workbook Links, and (3) click OK.


2 Answers

Attached is one potential solution for this as a vbs that can be run from vba if that is available

Thanks to Sid Rout for his suggested edits to RecursiveFile(objWB)

Caution: It is possible that too many simultaneous books being open (I got to 512 during vbs recursion hell) will lead to memory issues - in that case each major branch should be updated in turn, then those workbooks closed before proceeding to the next branch.

What it does

  1. Opens up a workbook held by strFilePath
  2. checks whether there are any linked workbooks in 1 , if so opens them (B, B1, B2 etc)
  3. the code then looks for any links in each of the workbooks from (2), then opens all these in turn (C1 and C2 for B etc)
  4. each open book name is stored in an array, Arr
  5. When all the books are opened, the initial workbook will have been updated, the recursive code ends, and all the open books except strFilePath are closed without saving
  6. strFilePath is then saved and closed
  7. the code tidies up

EDIT: Updated code to fix the vbs recursion issue

Public objExcel, objWB2, lngCnt, Arr()
Dim strFilePath, vLinks
`credit to Sid Rout for updating `RecursiveFileRecursiveFile(objWB)`

Erase Arr
lngCnt = 0

Set objExcel = CreateObject("Excel.Application")
strFilePath = "C:\temp\main.xlsx"

With objExcel
    .DisplayAlerts = False
    .ScreenUpdating = False
    .EnableEvents = False
End With

Set objWB = objExcel.Workbooks.Open(strFilePath, False)
Call RecursiveFile(objWB)

For Each vArr In Arr
    objExcel.Workbooks(vArr).Close False
Next

objWB.Save
objWB.Close
Set objWB2 = Nothing

With objExcel
    .DisplayAlerts = True
    .ScreenUpdating = True
    .EnableEvents = True
    .Quit
End With

Set objExcel = Nothing
MsgBox "Complete"

Sub RecursiveFile(objWB)
    If Not IsEmpty(objWB.LinkSources()) Then
        For Each vL In objWB.LinkSources()
            ReDim Preserve Arr(lngCnt)

            'MsgBox "Processing File " & vL

            Set objWB2 = objExcel.Workbooks.Open(vL, False)
            Arr(lngCnt) = objWB2.Name
            lngCnt = lngCnt + 1
            RecursiveFile objWB2
        Next
    End If
End Sub

Working ScreenShots

enter image description here

like image 96
brettdj Avatar answered Sep 30 '22 05:09

brettdj


yes, you can loop through all the source B workbooks, opening them in the background and set the UpdateLinks flag to True ...

strFiles=Dir(*path & \.xls*)

do
    workbooks.open strfiles, UpdateLinks:=true
    workbooks(strfiles).close savechanges:=true
    strFiles=Dir
loop while strfiles<>""

that should give you a start

like image 42
Our Man in Bananas Avatar answered Sep 30 '22 06:09

Our Man in Bananas