我正在尋找 VBA 代碼來參考一個名稱為前幾個月的 excel 檔案。
我已經嘗試了下面的代碼，它從模板創建檔案并使用前幾個月的名稱保存它沒有問題。然后我嘗試參考此檔案以將一些值粘貼到其中，但我不斷收到運行時錯誤 9：下標超出范圍。
Workbooks.Open Filename:="https://X.sharepoint.com/GROUP/Whiteboard/Malton New Month Template.xlsx?web=1"
ActiveWorkbook.SaveAs Filename:="https://X.sharepoint.com/GROUP/Whiteboard/2022/Malton/Malton " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm yyyy"), FileFormat:=51, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
Workbooks("WHITEBOARD project.xlsm").Worksheets("Malton Weekly Input").Range("A5:R404").Copy
Workbooks("Malton " & Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm yyyy" & "xlsx")).Worksheets("Data").Range("AC5:AT404").PasteSpecial Paste:=xlPasteValues
任何幫助我指出正確的方向將不勝感激
提前致謝
艾倫
uj5u.com熱心網友回復：
您的檔案命名不一致，但是如果您在第一次打開檔案時獲得了對該檔案的參考，則無需按名稱參考它。
Sub tester()
Const ROOT As String = "https://X.sharepoint.com/GROUP/Whiteboard/"
Dim prevMnth As String, wb As Workbook
'get a reference when you open the file...
Set wb = Workbooks.Open(Filename:=ROOT & "Malton New Month Template.xlsx?web=1")
prevMnth = Format(DateSerial(Year(Date), Month(Date) - 1, 1), "mmm yyyy")
wb.SaveAs Filename:=ROOT & "2022/Malton/Malton " & prevMnth & ".xlsx", _
FileFormat:=51, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
Workbooks("WHITEBOARD project.xlsm").Worksheets("Malton Weekly Input").Range("A5:R404").Copy
wb.Worksheets("Data").Range("AC5").PasteSpecial Paste:=xlPasteValues
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/503712.html