问从外部工作簿自动更新值
EN

Stack Overflow用户

提问于 2013-04-15 01:25:28

回答 3查看 4.3K关注 0票数 7

我有以下工作簿设置：

工作簿A有一个指向工作簿B的x数量的链接，并从它们获取数据。工作簿B有指向其他工作簿的链接，并从它们获取数据。

工作簿A是所有其他工作簿所包含内容的一种“摘要”。现在，我必须打开所有的工作簿B，刷新它们并在打开工作簿A之前保存它们。如果我不这样做，工作簿B的B就不会用工作簿C中的数据更新。

是否可以使用.bat或vbs脚本更新所有工作簿B？或者可以在工作簿A中更新它们吗？

我可能补充说，我在这台计算机上使用了excel启动器，所以更好的解决方案将与此兼容。

excel

scripting

vbscript

wsh

vba

回答 3

Stack Overflow用户

回答已采纳

发布于 2013-04-15 04:51:53

附加是一个潜在的解决方案，作为一个vbs，可以从vba运行，如果可用的话。

感谢Sid Rout的建议编辑到RecursiveFile(objWB)

警告:太多同时打开的书籍(我在vbs递归地狱中访问了512 )可能会导致内存问题--在这种情况下，每个主要分支都应该依次更新，然后这些工作簿在进入下一个分支之前关闭。

它做什么

打开由strFilePath持有的工作簿
检查1中是否有链接的工作簿，如果有，则打开它们(B、B1、B2等)
然后，代码从(2)中查找每个工作簿中的任何链接，然后依次打开所有这些链接(C1和C2表示B等)
每个打开的图书名都存储在一个数组Arr中。
当打开所有书籍时，初始工作簿将被更新，递归代码结束，除了strFilePath以外，所有打开的书籍都将关闭而不保存。
然后保存并关闭strFilePath。
代码整理好了

编辑:修复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

Working ScreenShots

票数 5

Stack Overflow用户

发布于 2013-04-15 01:41:10

是的，您可以循环遍历所有源B工作簿，在后台打开它们并将UpdateLinks标志设置为True .

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

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

这应该给你一个开始

票数 2

Stack Overflow用户

发布于 2013-04-15 02:03:52

因此，由于VBA不是一个选项，让我们尝试一个VB脚本解决方案：

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