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
