I see this question OFTEN.

You have a form and you want to open a specific Word or Excel file by just clicking on a button. Maybe the file name is always the same, maybe it comes from a list, maybe even a file open dialogue box.

(article continues after sponsor spot)

Type 1 - Let's do the easy one first.

• create a form
• add a command button to the form (mine is named cmdWordFile)
• modify the Hyperlink Address property of the button, typing the full file name (path included)
• That's all it takes. Now test it.

Type 2 - How to handle variable file names

Let's say you have a list box or a text box where the user chooses a name or types one in. Here is how to handle this one.

• do the same as in Type 1 above (put in a working default file name)
• add VBA code similar to the following to the On_Click event of the button
     Me.cmdWordFile.HyperlinkAddress = "C:\My Documents\Data\TestFile.doc"
     or
     Me.cmdWordFile.HyperlinkAddress = me.lstWordFiles

The first example is still "hardcoded" whereas the second example gets the file name from a listbox on the form. You could also have the folder name hard coded and just have the file name inputed.
eg.
   Me.cmdWordFile.HyperlinkAddress = "C:\My Documents\Data\" & _
      me.lstWordFiles & ".doc"

Further Suggestions

For the last example, with the list box, you need some error handling. For example, what if the user clisks on the button without choosing anything from the list.

The ideal in many cases is to have an official windows file open dialogue box. That is beyond the scope of this "tip". If you want it badly, visit google.com and do some searching. The answer is in there somewhere.

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Happy Coding

 
Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.
 

© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved.