VBScript无法刷新Excel工作簿内容

2024-05-21 • 问答

我想创建一个脚本来更改给定文件夹中每个excel文件以及子文件夹中所有文件的单元格。

我认为我的代码应该可以工作，但是由于安全问题（我认为该文件具有启用内容锁定，即使我在excel中禁用了该锁定）也无法刷新工作表）。我想知道是否有人可以帮助我弄清楚发生了什么并帮助我找到解决方案？

strDate = "20190831"
strPath = "C:\lefuras_test"

Dim objFSO,objRootFolder,objFil,objXl,objWb,objExcel

Set objExcel = CreateObject("Excel.Application")
Set objFSO = CreateObject("scripting.filesystemobject")
Set objRootFolder = objFSO.getfolder(strPath)

SearchSubFolder objRootFolder

Sub SearchSubFolder(objRootFolder)
    For Each objFil In objRootFolder.Files
        If InStr (objFil.Type,"Excel") > 0 Then
            Set Wb = objExcel.Workbooks.Open(objFil.Path)
            Wb.Sheets("CONFIG").Cells(3,2).Value=strDate
            DisableBackgroundConncections Wb
            Wb.RefreshAll
            p = objFil.Path
            Wb.SaveAs Replace(p,"C:\lefuras_test","C:\lefuras_test_output")
            Wb.Saved = True
            wscript.echo Wb.name&" elkeszult!"
            Wb.Close True
        End If
    Next
    For Each objFolder in objRootFolder.SubFolders
        SearchSubFolder objFolder
    Next
End Sub

Sub DisableBackgroundConncections(Wb)
    For Each connection in Wb.Connections
        If connection.Type = xlConnectionTypeOLEDB Then
            connection.OLEDBConnection.BackgroundQuery = False
        End If
    Next
End Sub

objExcel.Quit
wscript.echo "Folyamat vege."

strDate = "20190830" strPath = "C:\lefuras_test" strOutputPath = "C:\lefuras_test_output" Dim objFSO,objRootFolder,objFil,objXl,objWb,objExcel Set objExcel = CreateObject("Excel.Application") Set objFSO = CreateObject("scripting.filesystemobject") Set objRootFolder = objFSO.getfolder(strPath) objExcel.AutomationSecurity = 3 objExcel.DisplayAlerts = False objExcel.AskToUpdateLinks = False objExcel.AlertBeforeOverwriting = False SearchSubFolder objRootFolder Sub SearchSubFolder(objRootFolder) For Each objFil In objRootFolder.Files If InStr (objFil.Type,"Excel") > 0 Then Set Wb = objExcel.Workbooks.Open(objFil.Path) Wb.Sheets("CONFIG").Cells(3,2).Value=strDate DisableBackgroundConncections Wb p = objFil.Path Wb.SaveAs Replace(p,strPath,strOutputPath) Wb.Saved = True wscript.echo Wb.name&" elkeszult!" Wb.Close True End If Next For Each objFolder in objRootFolder.SubFolders SearchSubFolder objFolder Next End Sub Sub DisableBackgroundConncections(Wb) For Each connection in Wb.Connections If connection.Type = 2 Then connection.ODBCConnection.BackgroundQuery = False connection.ODBCConnection.backgroundquery = false connection.ODBCConnection.EnableRefresh = true connection.Refresh End If Next End Sub objExcel.Quit wscript.echo "Folyamat vege."

VBScript无法刷新Excel工作簿内容

littlejade 回答：VBScript无法刷新Excel工作簿内容

大家都在问