QueryTables.Add Method (Excel)

Question

strFile = Application.GetOpenFilename 'Workbooks.Open strFile

With ActiveSheet.QueryTables.Add( _
    Connection:="strFile", _
    Destination:=Range("$A$1"))

'I have tried with : With ActiveSheet.QueryTables.Add( _ Connection:="TEXT;strFile", _ Destination:=Range("$A$1"))

This is throwing up an error saying "Run-time error '1004'; So you have any clue why it is showing me error ??

It looks as if you are passing in the literal string "strFile" instead of the variable strFile. Try removing the quotes so Connection:="strFile" is Connection:=strFile — Soulfire
– Soulfire, Commented Jul 25, 2014 at 14:19

Soulfire · Accepted Answer · 2014-07-25 14:30:07Z

After some quick testing, I think I have found the issue. You need to be sure you are not passing in the string "strFile" into the connection, and you need to concatenate the TEXT to the connection string. This is demonstrated here:

strFile = Application.GetOpenFilename 'Workbooks.Open strFile

strFile = "TEXT;" & strFile 'Add the TEXT; to beginning of connection string

    With ActiveSheet.QueryTables.Add(Connection:= _
        strFile, Destination _
        :=Range("$A$1"))
        ... 'Remainder of code
    End With

For good measure, you may want to put the With ActiveSheet.QueryTables.Add ... code inside an IF statement that checks to make sure a file was selected.

For example:

Dim strFile As String

strFile = Application.GetOpenFilename 'Workbooks.Open strFile

strFile = "TEXT;" & strFile 

If strFile <> False Then
    With ActiveSheet.QueryTables.Add(Connection:= _
        strFile, Destination _
        :=Range("$A$1"))
        ... 'Remainder of code
    End With
Else
    MsgBox "No file was selected!"
End If

Collectives™ on Stack Overflow

QueryTables.Add Method (Excel)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related