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.
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
Jul 16 2021 07:39 AM
Sep 14 2021 11:01 PM
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?
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)