Opening Various File types from an Excel Worksheet

[Derek Dowle]

I am using office excel 2003

I have a single worksheet workbook which is a database. Each record of the
database contains a file name and its path. The idea is that if the user
double-clicks on a cell containing the file name then the file opens. The
database contains various file types including Word, Excel, Project,
PowerPoint, pdfs, etc.

I have considered using hyperlinks, but have ruled the idea out as at the
end of the day they will be thousands of them. Code will more easily get
around changes of file path or file name.

I have written the code to establish that the file and the path exist, but I
have not been able to write the code to open the file.

From research I have found code using:
Dim oWord As Word.Application
But I get a compile error: User-defined type not defined.

Can anyone please advise how I can overcome this error?
It appears from looking at examples that separate lines of code may be
needed for each file type. Is this the case and how will the code differ
for each file type?

Many thanks in anticipation!

 

[Joel]

You have to add the reference to the VBA project

From VBA menu

Tools - Refferences - Microsoft Office 11.0 object library

(or latest version on your PC)

Make sure you click on the X box and press OK.

 

[Derek Dowle]

Joel

I followed your advice and found that both the
Microsoft Office 11.0 Object Library, and
Microsoft Excel 11.0 Object Library
were already checked in the list of references.

Your reply gave me an idea so I checked the Microsoft Word 12.0 Object
Library in the list of references.

By doing so I no longer get the compile error, but now need to complete my
code and see whether I can now open the word document.

It looks as though my code must differentiate between different file types,
and have specific lines of code to open each file type. I will probably
need to check a few more Object Library references!

 

[Joel]

Usually I will open a word document using

set obj = getobject("document.doc")

By declaring App as a word application I was told speeds up the code a
little bit because VBA doesn't have to learn the object during the getobject
operation.

The other method is to use createobject

Set Obj = createobject("Word.Application")
Obj = open(filename:="document.doc")

Using create object VBA know the structure of the application. Getobject is
a general object and VBA has to learn the structure during the Get operation.