error 1004 using variable as a filename in opentext

I am getting an error when I try to use a filename as a variable with the workbooks.opentext command. If I hardcode the file path and file name, it will work. The variable is being taken from a table on an xlsx file using the CELLS(1,1) and/or RANGE("A2") commands.

I get the following error--that also includes my FILENAME in the error message:

Runtime Error '1004'
File could not be accessed. try one of the following
- make sure the specified folder exists
- make sure the folder that contains the file is not read-only
- make sure the filename does not contain any of the characters < > ? [ ] : Sub01.txt or *
- make sure file/path name does not contain more than 128

WHY am I getting this error? How can I pass my filename as a variable into the OpenText command using VBA?

Other code online shows that it should work... Should my fname and fpath variables not be declared as STRING?

Please help! Thanks.

Here is a snipet of my code:

Please Login or Register  to view this content.

Originally Posted by Paul

Just checking, but does your fPath end with "\"? If not, then you might be trying to open something like:

"F:\Data\AllPMapTogether\AllExportTopZeroTable.xlsx"

instead of

"F:\Data\AllPMapTogether\AllExport\TopZeroTable.xlsx"

Yes, my path has the quote included with it. I have also tried to omit the "\" and add it by:

Please Login or Register  to view this content.

but neither solved the error.

As I have troubleshot the problem, it appears to be an issue with reading a value into a program from a txt or xlsx file. The Workbook.OpenText will not work when I read the value using Cells() or Range(), and then put that variable in as the filename.

When I hardcode the filename or declare it in the code, the Workbook.OpenText will work.

Any thoughts as to why it won't read in the value from a txt/xlsx file? Any ideas how to solve it?