Updating values from external workbook automatically

Question

I have the following workbook setup:

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.

Answer 1

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

Answer 2

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

Answer 3

So, as VBA is not an option, let's try a VB Script solution:

dim objFSO, objExcel, objWorkbook, objFile
'
set objExcel= CreateObject("Excel.application")
'
objExcel.visible=false
objExcel.displayalerts=false
'
Set objFSO = CreateObject("Scripting.FileSystemObject")
objStartFolder = path
'
Set objFolder = objFSO.GetFolder(objStartFolder)
' get collection of files from folder
Set colFiles = objFolder.Files
' begin loop through all files returned by Files collection of Folder object
For Each objFile in colFiles
    ' sanity check, is the file an XLS file?
    if instr(objfile.name,"xls")<>0 then ' could also use right(objfile.name,4)=...
        Wscript.Echo "Opening '" objFile.Name & "' ..."
        set objWorkbook=objexcel.workbooks.open objfile.name, updatelinks:=true
        objexcel.workbooks(objfile.name).close savechanges:=true
    end if
Next
' close Excel
objexcel.quit
' kill the instance and release the memory
set objExcel=nothing

try that and see how you get on

and here is the VB Script SDK: MSDN Library - VB Script