Invalid character message in string

Question

I am trying to write a vba procedure that takes a variable containing a path and file name and concatenate into a string that will start a query procedure to import the file identified in the variable name containing the desired file. In the code, the import is to start at cell A2 ($A$2). I get an error message at the $ symbol to the left of the A in the cell reference. I would appreciate any help on this. Below is the code I am working with:

 Sub GetInfo()

       //get the full path to the file

        strFilePath = Application.GetOpenFilename()

    If strFilePath <> False Then

        MsgBox "Open " & strFilePath

    End If

    //get the file name from strFilePath

    sFileName = Mid(strFilePath, InStrRev(strFilePath, "") + 1, Len(strFilePath))

    Call ImportData(strFilePath, sFileName)

End Sub

Sub ImportData(strFilePath, sFileName)

'

' ImportData Macro

'

   'Make connection using the two passed parameters

    With ActiveSheet.QueryTables.Add(Connection:= _

        "TEXT;" & strFilePath & ", Destination:=Range("$A$2"))"      'Get error at $ in this line

        .Name = " ' " & sFileName & " ' "

        .............

End Sub

Answer 1

Rick,

Thanks for the reply but I still get errors with the first solution you presented (which should be the correct logic. I thought that it might be helpful to show you the code that is generated if I record the steps as a macro:

'    With ActiveSheet.QueryTables.Add(Connection:= _

'        "TEXT;C:\Users\FCC\Desktop\Data_2_Import.PRN", _

'        Destination:=Range("$A$2"))

It looks like the correct syntax should be this then...

With ActiveSheet.QueryTables.Add(Connection:= "TEXT;" & strFilePath, Destination:=Range("$A$2"))

Answer 2

Thank you for looking at my problem. Rick Rothstein hit the solution on the nose. It works fine now. Have a great day.

Answer 3

Might be good to see more of the code.  This is starting with a "With..." statement but we don't see where the End With is also.  While I'd expect a different error if the End With is missing, perhaps the whole syntax thing is getting confused as it sometimes does when you miss the ending block statement in a bunch of nested blocks?

Answer 4

Rick,

Thanks for the reply but I still get errors with the first solution you presented (which should be the correct logic. I thought that it might be helpful to show you the code that is generated if I record the steps as a macro:

'    With ActiveSheet.QueryTables.Add(Connection:= _

'        "TEXT;C:\Users\FCC\Desktop\Data_2_Import.PRN", _

'        Destination:=Range("$A$2"))

Hope this helps.

Answer 5

I have never used QueryTables, so I can't test this, but this part of your code line...

"TEXT;" & strFilePath & ", Destination:=Range("$A$2"))"

How to fix it depends on what is supposed to be set to Destination. If it is the text Range("$A$2"), then you need to double up the quote marks because they are embedded inside surrounding quote marks...

"TEXT;" & strFilePath & ", Destination:=Range(""$A$2""))"

On the other hand, if the contents of the cell at A2 is what should be assigned, the you need to concatenate the Range into the string, not include it within the string...

"TEXT;" & strFilePath & ", Destination:=" & Range("$A$2").Value)

 Just as a guess, my bet is on the first method though.