Forum Discussion
VBA coding for Sharepoint
We recently moved all of our drives to Sharepoint. The problem that we are having is the file paths that were set up for our macros no longer works as they are directing to the drive and not sharepo...
Since the code doesn't work, I would guess it has nothing to do with SharePoint.
The local path is wrong. Either just a typo or the path is too long (approx. 250 characters).
Don't you have to have a double backslash in front of it? ... https?
Get the content of a sharepoint folder with Excel VBA
Find more infos in this Link
I would be happy to know if I could help.
NikolinoDE
I know I don't know anything (Socrates)
Hi, 2021 post but if anyone is still watching, here's the solution.
Mount the Sharepoint https path on a mapped drive. How to do that is available elsewhere on the web. It's not perfect. File Explorer freaks out when you do something it doesn't expect. Sometimes the SP location mounts ok but then all of Windows claims that it didn't. Allow for that in your code.
You'll need to find what drive letters are available with something like this:
Public Function GetNextDrive() As String
Dim fso As Object
Dim colDrives As Object
Dim ascii As Long
GetNextDrive = ""
On Error GoTo Catch
'Find out next available drive letter
Set fso = CreateObject("Scripting.FileSystemObject")
Set colDrives = fso.Drives
ascii = Asc("c")
While fso.DriveExists(VBA.Chr(ascii) + ":")
ascii = ascii + 1 'infiloop?
Wend
GetNextDrive = VBA.UCase(VBA.Chr(ascii))
Catch:
Set fso = Nothing
End Function 'GetNextDrive
Make sure that the map exists (sometimes Windows takes it away from you) somehow:
Function fn_validate_drive(ByVal G As String) As Boolean
'https://answers.microsoft.com/en-us/msoffice/forum/all/vba-to-verify-that-network-drive-is-connected/f83b861d-abea-481b-9159-16aaa5eebea9
Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
fn_validate_drive = fso.DriveExists(G)
Set fso = Nothing
End Function ' fn_validate_drive
This code to mount the drive also came from somewhere on the web:
Public Sub soMapDrive(dd As String, PP As String)
Dim oNetwork As Object
Dim fso As Object
dd = Left(dd, 1) & ":"
Set oNetwork = CreateObject("WScript.Network")
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.DriveExists(dd) Then
oNetwork.MapNetworkDrive dd, PP, False '' <- Relies on your logon creds to see the object. Peruse in your browser to set up SSO.
End If
Set fso = Nothing
Set oNetwork = Nothing
End Sub 'soMapDrive
When you're done, clean it all up. (I found it helpful to keep a collection of mapped drives and then unmount them all after the main loop. File Explorer doesn't deal well with re-using mounts.)
Public Sub soUnmount(dd As String)
'https://stackoverflow.com/questions/563028/script-to-map-network-drive
Dim oNetwork As Object
Dim fso As Object
dd = Left(dd, 1) & ":"
Set oNetwork = CreateObject("WScript.Network")
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.DriveExists(dd) Then
oNetwork.removenetworkdrive dd, True, True
End If
Set fso = Nothing
Set oNetwork = Nothing
End Sub 'soUnmount
I added very little to the snippets above. They're all available elsewhere.
Mount the Sharepoint https path on a mapped drive. How to do that is available elsewhere on the web. It's not perfect. File Explorer freaks out when you do something it doesn't expect. Sometimes the SP location mounts ok but then all of Windows claims that it didn't. Allow for that in your code.
You'll need to find what drive letters are available with something like this:
Public Function GetNextDrive() As String
Dim fso As Object
Dim colDrives As Object
Dim ascii As Long
GetNextDrive = ""
On Error GoTo Catch
'Find out next available drive letter
Set fso = CreateObject("Scripting.FileSystemObject")
Set colDrives = fso.Drives
ascii = Asc("c")
While fso.DriveExists(VBA.Chr(ascii) + ":")
ascii = ascii + 1 'infiloop?
Wend
GetNextDrive = VBA.UCase(VBA.Chr(ascii))
Catch:
Set fso = Nothing
End Function 'GetNextDrive
Make sure that the map exists (sometimes Windows takes it away from you) somehow:
Function fn_validate_drive(ByVal G As String) As Boolean
'https://answers.microsoft.com/en-us/msoffice/forum/all/vba-to-verify-that-network-drive-is-connected/f83b861d-abea-481b-9159-16aaa5eebea9
Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
fn_validate_drive = fso.DriveExists(G)
Set fso = Nothing
End Function ' fn_validate_drive
This code to mount the drive also came from somewhere on the web:
Public Sub soMapDrive(dd As String, PP As String)
Dim oNetwork As Object
Dim fso As Object
dd = Left(dd, 1) & ":"
Set oNetwork = CreateObject("WScript.Network")
Set fso = CreateObject("Scripting.FileSystemObject")
If Not fso.DriveExists(dd) Then
oNetwork.MapNetworkDrive dd, PP, False '' <- Relies on your logon creds to see the object. Peruse in your browser to set up SSO.
End If
Set fso = Nothing
Set oNetwork = Nothing
End Sub 'soMapDrive
When you're done, clean it all up. (I found it helpful to keep a collection of mapped drives and then unmount them all after the main loop. File Explorer doesn't deal well with re-using mounts.)
Public Sub soUnmount(dd As String)
'https://stackoverflow.com/questions/563028/script-to-map-network-drive
Dim oNetwork As Object
Dim fso As Object
dd = Left(dd, 1) & ":"
Set oNetwork = CreateObject("WScript.Network")
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.DriveExists(dd) Then
oNetwork.removenetworkdrive dd, True, True
End If
Set fso = Nothing
Set oNetwork = Nothing
End Sub 'soUnmount
I added very little to the snippets above. They're all available elsewhere.