help with VBA Syntax - Text File Import (long)

Posted by Chris Rock on December 06, 2001 7:49 AM

A worksheet I have connects to a delimeted text file on our network. I have been using the Get External Data feature to connect to this file. However, each time I do this, it creates a new Name in the workbook, because I've selected the option to "Save Query Definition". By the end of the month, I've got far too many Named Ranges in my workbook, and when I refresh all data, it goes through every one of them.

The long and the short of it is that it is screwing up my data. So I'm writing a macro to run the Text Import Query without saving the query definition.

The text file on the network changes names each month, so I've written a formula in a cell on the worksheet that tells me the full path and filename of the appropriate file, based on the month of the report.

This works great. My problem lies in some syntax. Here's the VBA Code that's giving me problems:

DataFile = (Sheets("Cust Sat".Range.("G2023").Value
'This is equal to \\Tpa02app\custsat\200112.del

Sheets("Cust Sat").Select
With ActiveSheet.QueryTables.Add(Connection:=_
"TEXT;DataFile", Destination:=(Range("A2"))
. . . the properties of the text import query follow this.

My problem area is the place where I've referenced the variable "DataFile". I get an error when this runs that tells me Excel Cannot find the text file that this query refers to. I KNOW it is there. I believe I have a Syntax error in this statement:
"TEXT;DataFile", Destination:=(Range("A2")) -- I think Excel doesn't understand that I intend to use the DataFile variable, and is instead using the word DataFile as the filename.

I used the Macro Recorder to get the code for this, and I replaced the full path with my variable DataFile, which is the same exact value.

Any VBA gurus know how this works? I doubt I'll get any replies, but it's worth a try.
Thanks

Posted by Juan Pablo on December 06, 2001 7:57 AM

There are some problems with this line

DataFile = (Sheets("Cust Sat".Range.("G2023").Value

, change it to:

DataFile = Sheets("Cust Sat").Range("G2023")

and just to make sure Excel is finding the file, put this after it.

MsgBox Dir(DataFile)

If the MsgBox returns the file name, Excel found it (And should be able to work with it), if returns nothing (Blank message box), it's not finding it.

Juan Pablo G.

Posted by Chris Rock on December 06, 2001 8:04 AM

What does the when I type MsgBox Dir(DataFile) in the Immediate window, I get a Type Mismatch. When I type MsgBox (DataFile) in the Immediate Window, I get a blank message box.

Have you got a guess why that is?

Posted by Chris Rock on December 06, 2001 8:08 AM

Actually, when I run the macro, I DO get the filename returned to me, yet I STILL Get the "Excel cannot find the text file to refresh this external data range" message.

Your suggestions, however, did work. It is finding the file, but it has a hard time working with it.

Posted by Juan Pablo G. on December 06, 2001 8:24 AM

Ok, maybe, just throwing some ideas here (I have XL97 here, XL2000 at home, so no way to test right now), the problem is that the current directory is another (I'm not sure if this should affect). But try this:

DataFile = Sheets("Cust Sat").Range("G2023")
PathFile = Split(DataFile,"\")

ChDir(Left(DataFile,Len(DataFile)-Len(UBound(PathFile))))

Also, change this line:

With ActiveSheet.QueryTables.Add(Connection:=_
"TEXT;DataFile", Destination:=(Range("A2"))

With ActiveSheet.QueryTables.Add(Connection:=_
"TEXT;" & DataFile, Destination:=(Range("A2"))

I think this was the issue, but missed it before.

Juan Pablo G.