how to open no fixed name (*.xls*) format in VBA

[venkiatmaruthi]

Hi,

I have open a workbook and copy the data from that workbook to another workbook.

Here the problem is workbook name is not fixed. How to oepn that workbook.
I used the belwo statement but giving error "no file name"

Please Login or Register  to view this content.

The Markets is the starting name of the file after that no fixed name.
It might be .xls extenstion or .xlsx extension that's why I kept .xls*



Thanks in advance,
Venkat

[Domski]

Don't think you can use wildcards when opening a file.

It's a bit long winded but this might help, adapted from another post I answered ealier.

Please Login or Register  to view this content.

Dom

[venkiatmaruthi]

Don't think you can use wildcards when opening a file.

It's a bit long winded but this might help, adapted from another post I answered ealier.

Please Login or Register  to view this content.

Dom

Thanks for your sugguestion Domski.

Even I used your code but no use still same error is coming when opening the file. But Dir() function found that the file is available.

I understand the problem. The excel file is coming from SAP tool so I think it is saved in a different format. When I open the file manually and do the SAVE AS with other name and type as Micorsoft excel file. Now if I try to open the newly named file then it is working.


Thanks in advance,
Venkat.

[Richard Schollar]

Venkat

What's the value of myFile when you step thru the code (after the myFile = Dir...) line? If you are getting "No file found" then it suggests that Dir isn't identifying the file to open. What is the full name and path of the file that SAP is outputting that you are expecting to open (please give an example of an actual SAP outputted file, not one you've created yourself).

Richard

[venkiatmaruthi]

Venkat

What's the value of myFile when you step thru the code (after the myFile = Dir...) line? If you are getting "No file found" then it suggests that Dir isn't identifying the file to open. What is the full name and path of the file that SAP is outputting that you are expecting to open (please give an example of an actual SAP outputted file, not one you've created yourself).

Richard

I foudn the solution really now, I thought the error is because the file was coming from SAP sheet. But the problem here is __
variable myFile is getting only the file name(Markets_nov09.xls), not the full path. Dir is only returning filename. So we have to add the directory(where it is located) also to that filename then it is opening the file without any problem.