如何使用VBA从SharePoint加载/检索Excel工作簿?

Question

我有代码打开存储在网络驱动器上的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。

Answer 1

使用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