Create Query with variable for filename?

[JP Romano]

Hello...

I am building a macro that runs the process to create a data table via query, but I'm not able to get the syntax correct when I use a variable for the file name.

When I use the macro recorder, I get this beast at the top of the results

Please Login or Register  to view this content.

I am trying to get my variable, strFilename, to replace this mess:

Please Login or Register  to view this content.

I've tried messing with the quotation marks, but can't seem to get something like this to work, no matter what I try.

Please Login or Register  to view this content.

Anybody out there know how to do it?

Also, I know it seems odd to use the table query for another .xlsx file, but the source file has Asian Characters, and any other way I try to import them leaves a bunch of garbled characters (ANSI, I think). When I do the query manually, the results look perfect. I've tried using the DATA/IMPORT features as well, but to no avail.

Perhaps there is another way?

Thanks for any guidance!
JP

[dflak]

Does this show up as a data connection? If so, there are ways to tinker with the connection string. Basically you split it into an array on the semicolon, replace the part you want, and then merge it back together.

[JP Romano]

Dflak - No, it's not a data connection (which I am using on another import for the same project), it's a query... I'm pretty sure the part of the syntax that is messing me up is using the variable strFilename -- because when I leave it with the filename hardcoded, it works just fine.

Thanks for your time and feedback!

[rorya]

Close:

Please Login or Register  to view this content.

[JP Romano]

rorya - that did the trick...I was trying to add and remove quotations to/from the variable, in the string itself, and wow...that did it perfectly. THANK YOU!

[rorya]

Glad to help.