Need macro to import text file from same folder workbook is located

[Liberteric]

I need to import the text file DPLOTUS.csv from whatever folder the workbook that contains this macro is located.
The macro below works great for me but when I give it to my fiance, the folder names will be different. What won't be different is the workbook AND the text file will be located in the same folder.



Sub Import()
'
' import Macro
'
'
Sheets("TB").Select
Columns("A:D").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A9").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\cbeaulieu\Desktop\DPLOTUS.csv", Destination:=Range("$B$9"))
.Name = "DPLOTUS"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 9, 1, 9, 9, 9, 9)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

 

[Domenic]

Try replacing...

"TEXT;C:\Users\cbeaulieu\Desktop\DPLOTUS.csv"

with

"TEXT;" & ThisWorkbook.Path & "\DPLOTUS.csv"

Hope this helps!

 

[Ragnar1211]

Domenic answered it while i was playing with your code ... anyway here is a bit of code to try as well:

Sub Import()
'
' import Macro

Dim SourceBook As Workbook
Dim TB As Worksheet
Dim wbPath As String

' Turn off screen updating.
    Application.ScreenUpdating = False

' Set object variables for the active book and active cell.
    Set SourceBook = ActiveWorkbook
    Set TB = SourceBook.Worksheets("TB")
    wbPath = SourceBook.Path & "\"
    
    
TB.Range("A:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    With TB.QueryTables.Add(Connection:="TEXT;" & wbPath & "DPLOTUS.csv", Destination:=TB.Range("$B$9"))
            .Name = "DPLOTUS"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = True
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(1, 1, 9, 1, 9, 9, 9, 9)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
    
    End With

' Turn off screen updating.
     Application.ScreenUpdating = False
End Sub

 

[Liberteric]

It worked like a clock. Thank you so much.