Jul 16 2021 07:23 AM
Jul 16 2021 07:23 AM
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.
Here is the current macro with the old file path (text in red):
Dim nxt_row As Long
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
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
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileTrailingMinusNumbers = False
strExtension = Dir
Application.ScreenUpdating = True
Sep 15 2021 01:21 AM
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?
Find more infos in this Link
I would be happy to know if I could help.
I know I don't know anything (Socrates)
Jul 18 2023 02:55 PM