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

Liberteric

New Member
Joined
Sep 29, 2014
Messages
28
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,305
Try replacing...

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

with

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

Hope this helps!
 

Ragnar1211

Well-known Member
Joined
Jul 10, 2008
Messages
571
Domenic answered it while i was playing with your code :)... anyway here is a bit of code to try as well:

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,026
Messages
5,526,324
Members
409,695
Latest member
EmmaFos

This Week's Hot Topics

Top