VBA Express : Excel - Open any file or application.

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:

Open an Excel workbook
Select Tools/Macro/Visual Basic Editor
In the VBE window, select Insert/Module
Copy and paste the code for any of the subs above into the Module
(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)
If you want, you can change the paths to anything else you want to open.
Now select File/Close and Return To Microsoft Excel
Dont forget to save your changes...

Test the code:

Download the attachment
Open the folder "OpenWithHyperlinks
Open the workbook "OpenThingies"
Click one of the "Forms" buttons on the sheet
If you have extracted the code and are not using the attachment you will need to ensure that:
You alter the paths to a file or application on your PC
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 585 times.