Обновление значений из внешней рабочей книги автоматически

Question

У меня есть следующие настройки книги:

Рабочая книга A содержит ссылку на количество рабочих книг B и извлекает из них данные. Рабочие книги B содержат ссылки на некоторые другие рабочие книги и извлекают из них данные.

Рабочая книга A является своего рода "сводкой" того, что содержатся во всех других рабочих книгах. Как и сейчас, я должен открыть все свои рабочие книги B, обновить их и сохранить перед открытием рабочей книги A. Если я не сделаю этого, рабочая книга B не будет обновлена ​​данными из рабочих книг C.

Можно ли обновить все книги B, используя скрипт.bat или vbs? или возможно обновить их из книги А?

Я мог бы добавить, что я использую Excel Starter на этом компьютере, поэтому предпочтительно решение будет совместимо с этим.

Answer 1

Прилагается одно потенциальное решение для этого как VBS, который может быть запущен из vba если это доступно

Спасибо Сиду Рауту за предложенные изменения RecursiveFile(objWB)

Осторожно: возможно, слишком много одновременно открытых книг (я получил 512 во время vbs ад рекурсии) приведет к проблемам с памятью - в этом случае каждая основная ветвь должна обновляться по очереди, затем эти книги закрываются, прежде чем перейти к следующей ветке.

Что оно делает

1. Открывает рабочую книгу strFilePath
2. проверяет, есть ли в 1 связанные книги, если так, то открывает их (B, B1, B2 и т. д.)
3. Затем код ищет любые ссылки в каждой из книг из (2), а затем открывает их по очереди (C1 и C2 для B и т. д.)
4. имя каждой открытой книги хранится в массиве, Arr
5. Когда все книги открыты, начальная рабочая книга будет обновлена, рекурсивный код завершится, и все открытые книги, кроме strFilePath закрыты без сохранения
6. strFilePath затем сохраняется и закрывается
7. код приводит в порядок

РЕДАКТИРОВАТЬ: Обновлен код, чтобы исправить проблему рекурсии VBS

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

Рабочие скриншоты

Answer 2

Да, вы можете перебрать все книги источника B, открывая их в фоновом режиме и устанавливая флаг UpdateLinks в True ...

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

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

это должно дать вам начало

Answer 3

Итак, поскольку VBA не вариант, давайте попробуем решение VB Script:

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

попробуйте и посмотрите, как вы попадаете

и вот VB Script SDK: Библиотека MSDN - VB Script