Excel

Open any file or application.

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

johnske

Description:

After running the code any required file, application, tool, etc. is available for your use. 

Discussion:

There may be times where you need to access something ordinary or even quite out of the ordinary when running your code. By simply programming a hyperlink to whatever it is you need to be run, it can be opened and run whenever you like by simply following the link. All you need to know is the "method" and the path, and to ensure that you have a suitable program installed to "read" the given file.... In this example you can either open a Word document, an Access database, An Adobe Acrobat document, Windows Explorer, a Web page, or run either the Windows clean-up manager or the Windows disc defragmenter. 

Code:

instructions for use

			

Option Explicit '<< OPEN THE CLEAN UP MANAGER >> Sub StartCleanUpManager() On Error Goto 1 'ActiveWorkbook.FollowHyperlink "c:\Windows\System32\Cleanmgr.exe", _ 'NewWindow:=True '< for WinXP ActiveWorkbook.FollowHyperlink "c:\Windows\Cleanmgr.exe", NewWindow:=True Exit Sub 1: MsgBox Err.Description End Sub '<< START DISC DEFRAGMENTER >> Sub StartDefrag() On Error Goto 1 'ActiveWorkbook.FollowHyperlink "c:\Windows\System32\Defrag.exe", _ 'NewWindow:=True '< for WinXP ActiveWorkbook.FollowHyperlink "c:\Windows\Defrag.exe", NewWindow:=True Exit Sub 1: MsgBox Err.Description End Sub '<< OPEN A WORD DOCUMENT >> Sub OpenWord() On Error Goto 1 ActiveWorkbook.FollowHyperlink (ActiveWorkbook.Path & "\TestDoc.doc"), NewWindow:=True Exit Sub 1: MsgBox Err.Description End Sub '<< OPEN ANYTHING >> Sub OpenWindowsExplorer() On Error Goto 1 ActiveWorkbook.FollowHyperlink "C:\Windows\explorer.exe", NewWindow:=True Exit Sub 1: MsgBox Err.Description End Sub '<< OPEN AN ACCESS DATABASE >> Sub OpenDatabase() On Error Goto 1 ActiveWorkbook.FollowHyperlink ActiveWorkbook.Path & _ "\Testdb.mdb", NewWindow:=True Exit Sub 1: MsgBox Err.Description End Sub '<< OPEN A PDF DOCUMENT >> Sub OpenPDFdoc() MsgBox "This will fail if you don't have Adobe Acrobat installed :o)" On Error Goto 1 ActiveWorkbook.FollowHyperlink ActiveWorkbook.Path & _ "\AA cover.pdf", NewWindow:=True Exit Sub 1: MsgBox Err.Description End Sub '<< OPEN A WEB PAGE >> Sub OpenWebPage() On Error Goto 1 ActiveWorkbook.FollowHyperlink "http://www.vbaexpress.com/portal.php", NewWindow:=True Exit Sub 1: MsgBox Err.Description End Sub

How to use:

  1. Open an Excel workbook
  2. Select Tools/Macro/Visual Basic Editor
  3. In the VBE window, select Insert/Module
  4. Copy and paste the code for any of the subs above into the Module
  5. (If using XP, uncomment the lines that refer to XP (remove the leading apostrophes) and comment or delete the line directly below the currently commented one)
  6. If you want, you can change the paths to anything else you want to open.
  7. Now select File/Close and Return To Microsoft Excel
  8. Dont forget to save your changes...
 

Test the code:

  1. Download the attachment
  2. Open the folder "OpenWithHyperlinks
  3. Open the workbook "OpenThingies"
  4. Click one of the "Forms" buttons on the sheet
  5. If you have extracted the code and are not using the attachment you will need to ensure that:
  6. You alter the paths to a file or application on your PC
  7. Note: If you have windows installed on a drive other than C you will need to change the paths in the code accordingly...
 

Sample File:

OpenWith Hyperlinks.zip 32.85KB 

Approved by mdmackillop


This entry has been viewed 539 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2014 VBA Express