CODEWITHSUNDEEP
excelimportinputboxvba
Skrelly Joe
Skrelly Joe

Reputation: 25

VBA: How to use InputBox to prompt user for .txt file path when importing .txt to Excel?

I have been following a thread else where that showed me how to use Query Tables in the VBA editor to import a .txt file from a specific path into a worksheet.

The code is as follows:

Sub Sample()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Sample.txt", Destination:=Range("$A$1") _
        )
        .Name = "Sample"
        .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, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

I have tried to modify the code so that instead of having to hardcode the path each time, instead a user is prompted for the path with an InputBox that stores the path as a string in a variable, then that variable is called upon instead of the path.

I keep getting errors regarding the .Refresh BackgroundQuery:=False line.

Here is my modified code below. 

Option Explicit
Sub importEXP()

Dim txtloc As String

txtloc = InputBox("Provide path of .txt file to analyze")

    With ActiveSheet.QueryTables.Add(Connection:="TEXT;textloc", destination:=Range("$A$1"))

        .Name = "Sample"
        .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, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

End Sub

Any help is appreciated,

Thank You

Upvotes: 1

Views: 883

Answers (3)

Mike
Mike

Reputation: 1

You need to change

With ActiveSheet.QueryTables.Add(Connection:="TEXT;textloc", destination:=Range("$A$1"))

to

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & textloc, destination:=Range("$A$1"))

textloc is a variable so it mustn't be placed inside the quotes.

Upvotes: 0

aucuparia
aucuparia

Reputation: 2051

You need to replace the line:

With ActiveSheet.QueryTables.Add(Connection:="TEXT;textloc", destination:=Range("$A$1"))

With:

With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & textloc, destination:=Range("$A$1"))

Double quotes in VBA don't expand variables (like you can do in PowerShell and Perl); you have to explicitly concatenate.

Upvotes: 0

Sam
Sam

Reputation: 5721

Change
Connection:="TEXT;textloc"
to
Connection:="TEXT;" & textloc

Upvotes: 1

Related Questions