目標：
創建 VBA 例程以重繪 SharePoint Online 站點檔案檔案夾中所有作業簿中的查詢。
檔案夾設定：
SharePoint 網站 -> 檔案 -> 主檔案夾 -> 10 個子檔案夾 -> 每個子檔案夾 1 個 Excel 檔案
當前代碼：
Sub RefreshFW()
Dim fso, oFolder, oSubfolder, oFile, queue As Collection
Dim lCnt As Long
Set fso = CreateObject("Scripting.FileSystemObject")
Set queue = New Collection
queue.Add fso.GetFolder("C:\Users\user1\organization\main_folder") 'from Sharepoint sync
Do While queue.Count > 0
Set oFolder = queue(1)
queue.Remove 1 'dequeue
For Each oSubfolder In oFolder.SubFolders
queue.Add oSubfolder 'enqueue
Next oSubfolder
For Each oFile In oFolder.Files
Workbooks.Open fileName:=oFile
ActiveWorkbook.Unprotect Password:="wb"
With ActiveWorkbook
For lCnt = 1 To .Connections.Count
If .Connections(lCnt).Type = xlConnectionTypeOLEDB Then
.Connections(lCnt).OLEDBConnection.BackgroundQuery = False
End If
Next lCnt
End With
ActiveWorkbook.RefreshAll
ActiveWorkbook.Protect Password:="wb"
ActiveWorkbook.Close SaveChanges:=True
Next oFile
Loop
End Sub
問題：
運行時 1004 錯誤
“Microsoft 無法訪問檔案 'C:\Users\user1\organization\folder\.123A1234-D756*******'檔案名或路徑不存在或該檔案正在被另一個程式使用”期間Workbooks.Open fileName:=oFile
我嘗試過的事情：
指向本地檔案夾而不是同步的“本地”SharePoint 檔案夾 - 這有效。
使用 DavWWWRoot 將 SharePoint 路徑更改為 UNC 格式 -不起作用* 可能已經搞砸了，但找不到路徑
將主檔案夾網址添加到映射的網路驅動器，而不是使用 SharePoint Online 上的“同步”按鈕，然后指向網路驅動器 - 缺乏權限（不是受信任的站點，需要聯系內部資源以獲得批準）
問題：
這個 .123A1234-D756**** 檔案是位于主檔案夾中的快取/臨時檔案嗎？
如何在回圈遍歷檔案夾時調整我的代碼以忽略此快取/臨時檔案？
還有其他推薦嗎？
謝謝
uj5u.com熱心網友回復：
Sub RefreshFW()
Application.ScreenUpdating = False
Dim fso, oFolder, oSubfolder, oFile, queue As Collection
Dim lCnt As Long
Set fso = CreateObject("Scripting.FileSystemObject")
Set queue = New Collection
queue.Add fso.GetFolder("C:\Users\user1\organization\main_folder")
Do While queue.Count > 0
Set oFolder = queue(1)
queue.Remove 1 'dequeue
For Each oSubfolder In oFolder.SubFolders
queue.Add oSubfolder 'enqueue
Next oSubfolder
For Each oFile In oFolder.Files
If oFile.Name Like "*xls*" Then
Workbooks.Open fileName:=oFile
ActiveWorkbook.Unprotect Password:="wb"
With ActiveWorkbook
For lCnt = 1 To .Connections.Count
If .Connections(lCnt).Type = xlConnectionTypeOLEDB Then
.Connections(lCnt).OLEDBConnection.BackgroundQuery = False
End If
Next lCnt
End With
ActiveWorkbook.RefreshAll
ActiveWorkbook.Protect Password:="wb"
ActiveWorkbook.Close SaveChanges:=True
End If
Next oFile
Loop
Application.ScreenUpdating = True
End Sub
添加If oFile.Name Like "*xls*" Then并End If僅檢查 excel 擴展。
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/398227.html