VBA coding for Sharepoint

New Contributor

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

 

3 Replies
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

@Amber_Element 

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)