Using Querytable to load Text files

Question

Currently working on a macro that'll import txt files onto excel spreadsheet...very basic querytable code but I cant seem to integrate the application.getfilename component.

Dim DataF
DataF = Application.GetOpenFilename("Text Files (*.txt), *.txt")
With activesheet.querytables.add(connection:="Text; & DataF", Destination:_
=Range("$A$1"))
End with

Code isn't coming up with any errors...but then again the txt data aren't showing up... I've referred to this previous post vba: Importing text file into excel sheet but still stumped on this basic code.

Anyone here encountered this issue before?

score 0 · Accepted Answer · answered Dec 20 '13 at 22:03

Anything within quotes will be considered as a string whereas DataF is a variable.

Try this

connection:="Text;" & DataF

Also ensure that you check what DataF returns as your code can fail if user presses Cancel. For example

Dim DataF

DataF = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")

If DataF <> False Then
    '
    '~~> Rest of your code
    '
End If

Using Querytable to load Text files

1 Answers1