我有代码打开存储在网络驱动器上的Excel模板文件,使用另一个Excel工作表的输入填写空白,并保存现在填写的模板。
我们改成了SharePoint解决方案。
如何使用URL而不是本地目录打开?
Private Sub GenererTestjournaler_Click()`
Dim i As Long
Dim Directory As String, TempDirectory As String, NewDirectory As Variant
Set FSO = CreateObject("Scripting.FileSystemObject")
'This used to be the directory; Directory = "Z:HerculesDEN2. Sager, IgangværendeDynamisk ankertabelDynamisk ankertabelAnkertabelGodkendelsesforsøg_skabelon.xlsm"
'Now i want this to be the new directory:
Directory = "https://ncconline.sharepoint.com/:x:/r/sites/HerculesDanmark/DE/Dynamisk%20ankertabel/Ankertabel/Godkendelsesfors%C3%B8g_skabelon.xlsm?d=w35745d48f1984d189336f0e630a03f9a&csf=1&web=1&e=90aXBu"
TempDirectory = "C:Users" & Environ("UserName") & "DocumentsTemp_template.xlsm"
On Error Resume Next
Kill TempDirectory
On Error GoTo 0
FSO.copyfile Directory, TempDirectory
Set wbTemplate = Workbooks.Open(TempDirectory)
' And so on...
我
"运行时错误'76'路径未找到。
我猜它没有正确加载URL。
使用URL在Excel中打开工作簿,然后将其保存到Temp位置。
你不能使用FSO/Dir来处理http文件夹/文件。
Private Sub GenererTestjournaler_Click()
Const TEMPL_URL As String = "https://ncconline.sharepoint.com/sites/HerculesDanmark/" & _
"DE/Dynamisk%20ankertabel/Ankertabel/Godkendelsesfors%C3%B8g_skabelon.xlsm"
Dim wb As Workbook
Dim TempName As String
Set wb = Workbooks.Open(TEMPL_URL)
TempName = "C:Users" & Environ("UserName") & "DocumentsTemp_template.xlsm"
On Error Resume Next
Kill TempName
On Error GoTo 0
'Application.DisplayAlerts = False 'can use this instead of Kill
wb.SaveAs TempName
'Application.DisplayAlerts = True 'in place of Kill
'...
'...
End Sub