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.

Defrock

Are all x number of "B" files in the same folder or can they be in different folder? – Mizuki

They are in different folders. Although I suppose they can be moved if it's simplifies things – Defrock

If you move them to a single folder then you can use @Philip's code below else you will have to use a slightly somplex mpdule to find the paths to all those files and then then calculate them. Let me know if you are interested in that module... – Mizuki

yes, in different directories, it would be necessary to use a collection or array or something to store the file names and locations... – Spheroidicity

turns out VBA is not an option (rather like failure I guess) as the OP is using the Starter edition of Excel which doesn't have VBA! – Spheroidicity

@Philip: But you can use VBS ;) – Mizuki

so, as VBA is not an option, will try to write a Script file to do it :) – Spheroidicity

retagged to include Scripting! – Spheroidicity

I can't recollect the link but @brettdj had a module somewhere which allowed getting the file information from the links... – Mizuki

Found it! vbaexpress.com/kb/getarticle.php?kb_id=1064 – Mizuki

+1 for the question, would add +1 for the name if I could. – Falcate

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

Falcate

The reason why it is going in an infinite loop is beacuse if your remove OERN, you will see that the line vLinks = objWB.LinkSources() fails because vLinks is temporarily locked from the previous For Each vL In vLinks loop and hence vl will always return the same file from the first loop. Let me see if I can find an alternative. – Mizuki

Nicely done Sid. I had seen the temporary lock on the link sources but didn't sera route around it last night. Many thanks. – Falcate

I got this to work. Many thanks to you for the help. A few notes tho: I did not get it to work with .xlsx format. Resaved the sheets to .xls and it works. I changed the following line: For Each vArr In Arr objExcel.Workbooks(vArr).Close False to be Close True. Else the B sheets would not be saved with the updated values and when I opened sheet A and it asked me if I wanted to update the links it would revert to the old values (when I first opened the sheet A correct value would be shown but after update it was reverted). Thanks again, this was way more help then I expected – Defrock

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

Spheroidicity

OP says he's using Excel Starter. VBA is not available is Starter version. See this answer – Koller

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

Spheroidicity

Thank you. I will try to get this to work and report back later – Defrock

I don't see how this answers the question. This opens all excel files in a folder and updates links, the OP starts with a single workbook. – Falcate

@Falcate - well, the idea is to update the links, now it is correct that the user doesn't specifically ask to open all workbooks in a folder, but how else will the links be updated if not by opening the workbooks, updating the links, and saving the workbooks? – Spheroidicity

@brettdj: of course, if I have made a mistake, or misunderstood the OP's intent, please tell me. – Spheroidicity

@philip your code opens all workbooks in a given directory, and updates all the links. The OP request has a single workbook, that links to the Bs in turn to the Cs. So a) A single file starts the update process b) The linked files are not necessarily all in one directory c) the order of opening and closing is critical (C must update B, B updates A etc) – Falcate

@Falcate true, however, VBA is not available (OP has Starter Edition), I have proposed a starting point for a script based solution using VB Script. Now, in order for my script to update all the B workbooks, the OP would have to set the path correctly, or we would need to do further work, perhaps adding all the C workbooks to a collection or array, and looping through them to update the links before trying to update the links in the 'B' workbooks and so on. But this may be OUT OF SCOPE :) – Spheroidicity

Thanks for your help Philip. Im choosing to go with brettdj's answer since it suits me needs better and I got it to work. I still thank you for your effort! – Defrock