如何使用VBA在Sharepoint网站中打开文件

12 浏览
0 Comments

如何使用VBA在Sharepoint网站中打开文件

我试图打开一个文件，文件名每周都会变化。这意味着文件名中的日期部分是不固定的。此外，该文件是文件夹中唯一的文件，但文件名会变化。我使用了以下代码，但出现了错误：“运行时错误52：文件名或编号错误”。我需要你的帮助。

<代码>

Dim ThePath As String

Dim TheFile As String

ThePath = "https://ts.company.com/sites/folder1/folder2/folder3/folder4/"

TheFile = Dir(ThePath & "MANILA_ShiftRecord_*" & ".xlsx")

Workbooks.Open (ThePath & TheFile)

谢谢！

0

1 答案

0 Comments

问题的原因是：需要使用VBA打开Sharepoint站点中的文件，但是没有提供具体的方法和代码。

解决方法：可以使用以下代码来打开Sharepoint站点中的文件。

Dim sharepointFolder As String
Dim colDisks As Variant
Dim objWMIService As Object
Dim objDisk As Variant
Dim driveLetter As String
'Create FSO and network object
Set objNet = CreateObject("WScript.Network")
Set fs = CreateObject("Scripting.FileSystemObject")
'Get all used Drive-Letters
Set objWMIService = GetObject("winmgmts:\\" & "." & "\root\cimv2")
Set colDisks = objWMIService.ExecQuery("Select * from Win32_LogicalDisk")
'Loop through used Drive-Letters
For Each objDisk In colDisks
    For i = 65 To 90
        'If letter is in use exit loop and remember letter.
        If i = Asc(objDisk.DeviceID) Then
            j = i
            Exit For
        'letters which are not checked yet are possible only
        ElseIf i > j Then
            driveLetter = Chr(i) & ":"
            Exit For
        End If
    Next i
    'If a Drive-Letter is found exit the loop
    If driveLetter <> "" Then
        Exit For
    End If
Next
'define path to SharePoint
sharepointFolder = "https://spFolder/Sector Reports/"
'Map the sharePoint folder to the free Drive-Letter
objNet.MapNetworkDrive driveLetter, sharepointFolder
'set the folder to the mapped SharePoint-Path
Set folder = fs.GetFolder(driveLetter)

然后可以使用filesystemobject函数来处理该文件夹。

0