Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NOTICE: I am right in the path of Hurricane Irma, please read this   dismiss
 
 

Open Other Programs Seminar

Learn how to work with external file attachments and web pages in Access 
 

access open other programs seminar shell shellexec shellexecute

Have you ever wanted to build a database to store an unlimited number of documents or other file attachments with each record? Perhaps you'd like to double-click on any of those files and have them automatically open up in the appropriate program? Open a Word document representing the employee's resume, or automatically have an Excel spreadsheet open up for the client's purchase order. Perhaps you'd like to click on a button next to the customer's address and have your web browser open up directly to that address into Google Maps?

This seminar will teach you how to open other programs, documents, and custom web page addresses from inside your Microsoft Access databases. Topics include:

1. How to display external images in Access Forms
2. Build a database to allow unlimited external attachments per customer
3.
Use the SHELL function to open external files of known type
4. How to determine which application to open
5. Use SHELLEXECUTE to open file in default Windows handler app
6. Open a Web page to a custom URL in default browser
7. Load Google Maps to a Specific Address

Click here to watch the first lesson of the Seminar in our Online Theater:

 

 
Access Open Other Programs Seminar
Description: Learn how to work with external file attachments and web pages in Access.
Versions: I use Access 2013, however the topics covered should work fine with any version of Access.
Pre-Requisites: This course stands alone, however it is very strongly recommended that you at least know the basics (Access Beginner 1-3 would be a good start).
Running Time: 1 Hour, 19 Minutes
Cost: $49 - Order multiple courses to receive a discount up to 50% off

 

Here's What You'll Learn

We'll start out by building the sample database we'll use in class. This will be a simple customer table, with a related (one-to-many) table to store an unlimited number of attachments per customer. We'll create a centralized Attachments folder and we'll copy some sample files there. You'll learn how to display an external image on a form.

unlimited file attachments per customer

Next you will learn how to double-click on those attachment filenames and have them open up in the program you specify using an event with a little bit of VBA code and the SHELL function. You will learn how to determine what program to use to open the file (TXT files in Notepad, DOCX files in Word, etc.)

SHELL function access

It's nice to be able to specifically control which program you want to open a specific type of file, but sometimes you want Access to just use the default program that's installed in Windows. This is called the "default handler" for that file type. You may not know if they have Adobe Acrobat installed for PDF files, so in that case Windows might open them in Word. In the next lesson we'll learn about the ShellExecute function which can deal with this scenario.

shellexecute function shellexec

Next we'll build a button that we can click on to open the customer's address up directly in Google Maps. This will involve creating a custom URL and launching the default browser. You'll also see how you can open Windows Explorer to browse folders, and how to use a mailto link to send email.

open google maps to customer location

Finally we'll learn some advanced features about the ShellExec subroutine we build in class. We'll see how to set up error handling for when a file cannot be opened, learn about public and private constants, and learn about different arguments for ShellExec like "OPEN" and "PRINT".

If you want to be able to work with external files and programs with Access, this is the perfect seminar for you. You will learn everything mentioned above. You will have access to the full database that we build in class with all of these features. If you have any question whether this seminar is right for you, please feel free to contact me.

 

Opening Other Programs Outline

00. Intro (7:18)

01. Display External Images (17:29)
Create Customer Table
Setup File Attachments Folder
Copy Images to Folder
Create Customer Form
Display Customer Image on Form
Multiple Attachments Per Customer
Setup Attachments Table
Display File Extensions
Hide Extensions for Known File Types
Attachments Subform

02. Shell Command (23:33)
Command Button Build Event
SHELL Function
vbNormalFocus
System Path Variable
Command Prompt
Determining What Program Opens a File
On Double Click Event
Dealing with Spaces in File Names
Launching Internet Explorer
Command Line Arguments Switches

03. ShellExec Command (20:57)
ShellExecute Function
Create the ShellExec Sub
Code Sample Table and Form
Choose Default File Handler
Open Web Page with ShellExec
Open Google Maps to Customer Address
Build Google Maps URL
Replace Function
Open Folder with Windows Explorer
Sending Email with Mailto

04. ShellExec Command Advanced (7:24)
Private and Public Constants
Error Handling
Other OPEN type arguments

05. Review (2:35)

 


 

 
 

Student Interaction: Access Open Other Programs Seminar

Richard on 11/24/2014:  Have you ever wanted to build a database to store an unlimited number of documents or other file attachments with each record? Perhaps you'd like to double-click on any of those files and have them automatically open up in the appropriate program? Open a Word document representing the employee's resume, or automatically have an Excel spreadsheet open up for the client's purchase order. Perhaps you'd like to click on a button next to the customer's address and have your web browser open up directly to that address into Google Maps? This Access Open Other Programs Seminar will teach you how to open other programs, documents, and custom web page addresses from inside your Microsoft Access databases. Topics include: 1. How to display external images in Access Forms 2. Build a database to allow unlimited external attachments per customer 3. Use the SHELL function to open external files of known type 4. How to determine which application to open 5. Use SHELLEXECUTE to open file in default Windows handler app 6. Open a Web page to a custom URL in default browser 7. Load Google Maps to a Specific Address This seminar is 1 hour and 19 minutes long. Click here for more information on the <Access Open Other Programs Seminar, including a complete course outline, sample video, and lots more.
Selby Halfpenny on 11/27/2014: Dear Richard,

Thanks so much for another great great seminar, I work with Images in my databases. Always wanted to know how to open other files with different extensions. Thanks to you, now I can. Have your Imaging Seminar.

All the best to you.
Regards

Selby

Wallis W on 12/4/2014: The techniques demonstrated in this seminar were exactly what we needed to take our database presentations to the next level and to provide additional user access to important information. That we were shown how to do all of this without storing the files in the database was a tremendous plus. Hats off for a great resource.

Reply from Richard Rost:

Thanks, Wallis. Glad you liked it.

Josep Lluis Ortega on 12/14/2014: What would be the code for ShellExec under Windows/Access 2013 64 bit?

Reply from Alex Hedley:

May I ask why you're using 64Bit, it's not normally necessary.
Check this Blog Post from Rich.

You can do a check, although this is VBA7 which isn't 64-Bit, 64-Bit uses VBA7 so it works.
The Keyword needed is
PtrSafe



#If VBA7 Then
Private Declare PtrSafe Function ShellExecute ...
#Else
Private Declare Function ShellExecute ...
#End If


Josep Lluís O on 12/14/2014: I have Windows 8.1 64 bit, so I installed Access 2013 also 64 bit. I found the solution navigating Internet and applied the some code you give, except I didnt include Private. Now it works on my computer. I have to check it at work with 32 bit systems.
Richard Lanoue on 1/29/2015: I really enjoyed this seminar and it brought a lot of ideas to my projects...
Will you do a continuation on this such as:
Use data collected and use it to display pie charts, bar and line graphs?

Also, I enjoyed how I can have a Form where a frame displays an image from file, I want to have it display video in a frame on a form that plays mp4 wmv etc... That plays when you open the form up...Will you do seminars like that?


Reply from Alex Hedley:

Did you look at the Imaging Seminar mentioned in the Course?

It doesn't cover video but you'll learn the techniques needed.
There was a wmv active x control you could add for older video files
VLC etc might add there own once installed.
Or you could build a dynamic local webpage to at the video.

Nicholas S on 1/30/2015: Where do I find the sample database with the code in it please?
regards
Nick

Reply from Alex Hedley:

That would be the Student Databases page.
Make sure to have your course key handy to unlock it.

Nicholas S on 1/31/2015: ok thanks, I have it now, couldn't find a link anywhere.
However, the code doesn't work on 64 bit Access 2013, I have a compile error.

"The code in this project must be updated for use on 64-bit systems. Please review and update Declare statements and then mark them with the PtrSafe attribute"

Any advise would be most welcome please.

kind regards

Nick

Reply from Alex Hedley:

The main piece of advice is don't use 64 bit.
Rich wrote a blog article on it.
Unless you have a really good reason to there isn't much point in having it just because your system can.

There was a similar question in this thread.
You'd have to wrap the code in IF statements and server the correct function.

Richard Lanoue on 1/31/2015: THERE HAS TO BE A BETTER WAY... I would like to know the vbcode for dblclick a text field and have it open a window that when I select an object ( such as an mp4 or wmv file) the PATH placed into the text field. Thereby eliminating the task of go to the window where the object is stored copy the path and paste it in the field...

Reply from Alex Hedley:

This is shown in the Imaging Seminar.

Richard Lanoue on 2/4/2015: This is probably one of my favorite seminars... What I'd like to know is when we open a window for example:

Attachment = LaunchCD(Me, "C:\Movies")

It always opened to C:\Movies... Is there away where when I open the window it opens where I last left? For Example:

I left C:\Movies\Vol1\Welcome Package\

The next time I open it, opens at C:\Movies\Vol1\Welcome Package\...NOT C:\Movies... I next go to C:\Movies\Vol4\Maintence Video... and it opens from C:\Movies... I next go to C:\Movies\Vol4\Maintence Video


Reply from Alex Hedley:

I don't think it remembers
You could get the last location from the chosen file and store that in a table for use later
InStrRev to find the last slash and get that path

sumaila on 4/13/2015: hi richard. am new here and i want to kow if the shell function method can be use to open a folder and upload images onto a form? if not then how

Reply from Alex Hedley:

The Shell Function can be used to open Explorer which will be your Folder, you can go to a specific folder with images in it.
But this is opening another app, if you are wanting to choose an image from a Folder/File picker you'll want to use a Common Dialog control as shown in the Imaging Seminar





Richard Lanoue on 9/9/2015: This is probably my favorite lesson from you... I wanted to know is rather than call up a video file that uses a videoplayer... is therea script that will embed the video in a form? similar to your site but not with a webpage but a form

Reply from Alex Hedley:

You could look for an ActiveX control that you could add to your Form but if you need to distribute the db this might not work.

One option I need to write up is generating a on the fly HTML page with the VIDEO tag that links to your video file, you could then set a web browser control to have this source, and then let the web page handle the native controls for play/pause etc

Gerhard Marais on 9/15/2015: Please note that I received the "You either do NOT own this course, OR you have been timed out due to inactivity OR you just recently ordered this course. See below."
I have restarted the browser and open the training from within my course.
Please investigate and revert back!

Reply from Alex Hedley:

Which browser are you viewing the course in?
Did you try clearing the internet cache?

See the WalkThru or see this blog post.

Dana Michaels on 8/26/2016: I too loath the alternating colors so I simple deleted the 2nd color and got the same results as you. I never thought of using the same color...

Eric Michalek on 11/1/2016: I don't understand the Double Double quote in open program seminar lesson 2 10 minutes in. The explanation is that because there is spaces but why should that matter?

Reply from Alex Hedley:

If you open up a command prompt (cmd) and use the command "cd" which is change directory and then pass it an argument of a file path like "c:/program files/office/" etc, if you don't quote the path the command prompt will think the space is another command so
cd "c:/program" "files/office/"
Whereas if you quote it, it will pass the whole string as a single argument
cd "c:/program files/office/"

It's like when you create a function that expects multiple arguments.

function createname(firstname, lastname)
return first name & " " & lastname
end function

When you call this function you will get the string "Alex Hedley" returned
createname("Alex", "Hedley")

Eric Michalek on 11/1/2016: My goal is to click my command button which opens a word document, do stuff (such as automatically fill out data from my database, save it with a new name to maintain the integrity of the original document, print that document, and or send it to my boss electronically). Which of your classes does that? (I purchased many classes so far, but I need to fill in the gaps)

Reply from Alex Hedley:

There isn't much on Word Automation. Expert 19 covers basic Mail Merge.

Emailing is covered extensively in Email Seminar.
How complex is the Word Document? Could you not copy it into an MS Access Report and email that instead?
You could write a routine that saves the Report as a PDF.

Eric Michalek on 11/16/2016: Unfortunately, I am required to use Word. These documents only contain one or two pages of text and possibly one table for text.

Reply from Alex Hedley:

Would the mail merge not work then?

Richard Lanoue on 1/2/2017: Is there a way where When I open a folder by clicking a button, it not only opens the folder but formats it so the Icons are always very large, sorted alphabetically and grouped by type and have one group selecting collapse so only jpg are shown?

Reply from Alex Hedley:

MS Forum

1. Open up a window in File Explorer (or hit START BUTTON followed by the letter E).

2. Open up the View Ribbon, and select the view option. In your case, you would select "large icons".

3. Still in the View Ribbon, select the "Options" icon (on the far right). If a menu drops down, select "change folder and search options".

4. From this point, things should look just like Windows XP or Windows 7. The "Folder Options" dialog should pop up. Select the "View" tab, and click the "Apply to Folders" button.

Richard Lanoue on 3/4/2017: I'd like to know if there is a VB code that will open a folder the way you use theses ShellExec Commands...BUT the folder ipes up to specific View Settings namely Extra Large all the time. I tried saving it but the settings eventually go to something else. Also some folders I want the icons to be in List View and others in Extra Large view... Can you also make the window that opens up have a specific Groupby setting all the time?

Reply from Alex Hedley:

Found on another Forum. I haven't tested this.

http://www.vbforums.com/showthread.php?409476-RESOLVED-brain-freeze-how-would-open-a-particular-folder-%28in-Windows%29-from-my-app

Option Explicit

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" ( _
ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" ( _
ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" ( _
ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Private Enum FolderView
viewDEFAULT = 0
viewICON = &H7029
viewLIST = &H702B
viewREPORT = &H702C
viewTHUMBNAIL = &H702D
viewTILE = &H702E
End Enum

Private Const SW_SHOWNORMAL As Long = 1
Private Const WM_COMMAND = &H111

Private Sub Command1_Click()
OpenFolder "C:\", viewREPORT
End Sub

Private Sub OpenFolder(ByVal sFolderPath As String, Optional ByVal eView As FolderView = viewDEFAULT)
Dim N As Long, lhWnd As Long, lPrevhWnd As Long
If Len(Dir(sFolderPath, vbDirectory)) = 0 Then Exit Sub

lPrevhWnd = FindWindow("CabinetWClass", vbNullString)
ShellExecute Me.hwnd, "Open", sFolderPath, vbNullString, vbNullString, SW_SHOWNORMAL

If eView Then
Do
DoEvents: N = N + 1
lhWnd = FindWindow("CabinetWClass", vbNullString)
Loop Until Not (lPrevhWnd = lhWnd Or lhWnd = 0) Or N = 100000

If N = 100000 Or lhWnd = 0 Then Exit Sub
Call Sleep(100)

lhWnd = FindWindowEx(lhWnd, 0&, "SHELLDLL_DefView", vbNullString)
SendMessage lhWnd, WM_COMMAND, ByVal eView, 0&
End If
End Sub



GetOpenFileName: Set an Open/Save Dialog's Initial Listview View
http://vbnet.mvps.org/index.html?code/hooks/fileopensavedlghooklvview.htm

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP