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 sharepoint. I have attempted to update these but having issues with the path/location. This macro is pulling a txt file and exporting the data into excel.
Please help!!
Here is the current macro with the old file path (text in red):
Sub Import_All_Text_Files_2007()
Dim nxt_row As Long
'Change Path
Const strPath As String = "S:\VMA\WEX TRANSACTIONS\WEX DQRY Data\NEW BULKS-CURRENT WEEK\"
Dim strExtension As String
'Stop Screen Flickering
Application.ScreenUpdating = False
ChDir strPath
'Change extension
strExtension = Dir(strPath & "*.txt")
Do While strExtension <> ""
'Adds File Name as title on next row
Range("A65536").End(xlUp).Offset(1, 0).Value = strExtension
'Sets Row Number for Data to Begin
nxt_row = Range("A65536").End(xlUp).Offset(1, 0).Row
'Below is from a recorded macro importing a text file
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & strPath & strExtension, Destination:=Range("$A$" & nxt_row))
.Name = strExtension
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
'Delimiter Settings:
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileTrailingMinusNumbers = False
.Refresh BackgroundQuery:=False
End With
strExtension = Dir
Loop
Application.ScreenUpdating = True
End Sub
4 Replies
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.
- I did attempt to update the path with the one drive location but still not working
- Hey, have you found a solution to this? sitting with the same issue
