Opening an excel file from a cell reference

[happyfingers]

Hiya,

I want to use a macro to open an existing file on my c drive where the reference to that file is contained in a cell.

E.g. In my sheet, the cell A1 contains the following text "C:\Documents and Settings\Test". Therefore the file I want to open using a macro will be Test.xls.

However the file I wish to open (and therefore the reference text in cell A1) could change (and it could also change to be a word document) so it needs to be able to adapt when a new valid file (and document type) reference is entered into the cell.

Help!

Also, as an aside, when I want to change the file referred to in A1, the easiest way I've found is to find the file I want in windows explorer, go to properties and copy the file reference and then paste it into A1. However the reference (from the properties tab) seems to miss the ".xls" or ".doc" at the end (see above). Is this an issue? If yes, then I'm happy to manually type the .xls or .doc extension at the end.

Is there an easier way to do this?

Thanks a bunch

Henry

[happyfingers]

Note to self... always check previous posts before asking a question. Here's the code...

    Dim myFileName As String
    
    myFileName = Range("A1").Text
    Workbooks.Open Filename:=myFileName  

However I get an error if the file name doesn't exist! Can anyone help me with some code that will give a simple message box stating the file name doesn't exist?

Many thanks

Henry

[shg]

Please Login or Register  to view this content.

[happyfingers]

Thank you so much for your reply. But I noticed that it didn't work if A1 contained an address to a file that didn't exist. I think I've managed to work around it by doing this...

Please Login or Register  to view this content.

HOWEVER, this only works if the target file is an Excel document. Unfortunately the target files could be either in Excel, Word or even (possibly) Powerpoint. Any ideas how to code it to open any file type? Or am I dreaming?

Thank you for your help.

Kind regards

Henry

[shg]

But I noticed that it didn't work if A1 contained an address to a file that didn't exist.

Just put Exit Sub after the MsgBox. Then point was to illustrate how to know if a file exists.

The only file types you can open, in the sense of seeing them in Excel, are Excel files, text, csv, ... . You can open any type of file as binary or a textstream object, but I doubt that's what you want to do. (Your title does say open an Excel file.)

If you want to open Word and PPT files in their native applications from VBA, you would have to create the correct application, and then open the file in the application, or use Shell.

What are you trying to do?

[happyfingers]

If you want to open Word and PPT files in their native applications from VBA, you would have to create the correct application, and then open the file in the application, or use Shell.

What are you trying to do?

Thank you and you make a valid point... I thought that the file address in cell A1 would always be targeting an Excel file. However it turns out it could be an Excel file or a Word document (forget about Powerpoint).

I drew up the code to launch a Word document...

Please Login or Register  to view this content.

[/CODE]

... but I now can't figure out how to make it flexible so that the macro will always launch the right application and then open the file, whether it be an Excel or Word file.

Does that make sense?

Regards

Henry

[happyfingers]

BTW apologies for being a bit vague in my initial posts.

[shg]

How do you suppose Windows decides what application to use?

[BigBas]

I've never had to do anything like this, but maybe an option is to add the extension into cell A1 (ie, C:\Documents and Settings\Test.doc), then query the last three letters of the cell to determine file type. Next use a Select Case statement to determine the correct app.

Please Login or Register  to view this content.

[shg]

BigBas has the idea. I'd add references to Word and PowerPoint, and then use early binding so IntelliSense works:

Please Login or Register  to view this content.

[happyfingers]

Now that is a stroke of genius! I love the logic. Both sets of code work perfectly. Thank you both so much for your efforts.

I remain, truly indebted

Henry