Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-19-2019, 12:21 AM   #1
Cark
Newly Registered User
 
Join Date: Dec 2016
Posts: 153
Thanks: 61
Thanked 1 Time in 1 Post
Cark is on a distinguished road
How do I use a filepath to open a file in VBA?

I have managed to dynamically reference a bunch of filepaths in my database saved to a field called FilePath. The links are full filepaths in the following format. I have added the square brackets to identify items that vary depending on the item uploaded:

H:\BackupOfAttackments\[ID]\[File Name including extension]

This gives something like the following:

H:\BackupOfAttachments\ID\Design 12335434 - Revision 1.xlsm

My current code for my open-file style Button is:

Code:
Private Sub FinishIconFleet_Click()

Dim avlist As String

avlist = Me.lstAvailable.Column(1)

Debug.Print avlist

End Sub
avlist is the full filepath of H:\BackupOfAttachments\ID\Design 12335434 - Revision 1.xlsm.

How do I get Access/VBA to open the file? I am looking for a way to do this that can handle all file extensions.

I don't think this needs an example database to be uploaded to aid with this, but if it does I would be more than happy to create one.

Cark is offline   Reply With Quote
Old 09-19-2019, 12:25 AM   #2
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,991
Thanks: 121
Thanked 3,332 Times in 3,006 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: How do I use a filepath to open a file in VBA?

Use Application.FollowHyperlink.
This will open any file using the default application (Word/Excel etc)

EDIT:
If there is no default application for that file type, you will be asked which application to use
OR use a file handler procedure such as those suggested by the other responders.
I also use the function fHandleFile as in moke's link but have updated it to work in 64-bit as well

Paste the code below into a standard module

Code:
Option Compare Database
Option Explicit

'=================================================
'checked 09/03/2019
#If VBA7 Then
    Public Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal HWnd As LongPtr, ByVal lpOperation As String, _
        ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As LongPtr
#Else
    Public 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
#End If

'=================================================

Public Const WIN_NORMAL = 1         'Open Normal
Public Const WIN_MAX = 2            'Open Maximized
Public Const WIN_MIN = 3            'Open Minimized

Private Const ERROR_SUCCESS = 32&
Private Const ERROR_NO_ASSOC = 31&
Private Const ERROR_OUT_OF_MEM = 0&
Private Const ERROR_FILE_NOT_FOUND = 2&
Private Const ERROR_PATH_NOT_FOUND = 3&
Private Const ERROR_BAD_FORMAT = 11&

Public Sub ShellEx(ByVal Path As String, Optional ByVal Parameters As String, Optional ByVal HideWindow As Boolean) 'CR v5186

    If Dir(Path) > "" Then
        ShellExecute 0, "open", Path, Parameters, "", IIf(HideWindow, 0, 1)
    Else
        MsgBox "Can't find program"
    End If

End Sub

Function fHandleFile(stFile As String, lShowHow As Long)

'09/03/2019 - Updated for 64-bit
#If VBA7 Then
    Dim lRet As LongPtr
#Else
    Dim lRet As Long
#End If

Dim varTaskID As Variant
Dim stRet As String

    'First try ShellExecute
    lRet = ShellExecute(hWndAccessApp, vbNullString, _
            stFile, vbNullString, vbNullString, lShowHow)
            
    If lRet > ERROR_SUCCESS Then
        stRet = vbNullString
        lRet = -1
    Else
        Select Case lRet
            Case ERROR_NO_ASSOC:
                'Try the OpenWith dialog
                varTaskID = Shell("rundll32.exe shell32.dll,OpenAs_RunDLL " _
                        & stFile, WIN_NORMAL)
                lRet = (varTaskID <> 0)
            Case ERROR_OUT_OF_MEM:
                stRet = "Error: Out of Memory/Resources. Couldn't Execute!"
            Case ERROR_FILE_NOT_FOUND:
                stRet = "Error: File not found.  Couldn't Execute!"
            Case ERROR_PATH_NOT_FOUND:
                stRet = "Error: Path not found. Couldn't Execute!"
            Case ERROR_BAD_FORMAT:
                stRet = "Error:  Bad File Format. Couldn't Execute!"
            Case Else:
        End Select
    End If
    
    fHandleFile = lRet & _
                IIf(stRet = "", vbNullString, ", " & stRet)
End Function

'Now all you need to do is call the application with the path of the file and let Windows do the rest.
'This code can be used to start any registered applications, including another instance of Access.
'If it doesn't know what application to open the file with, it just pops up the standard "Open With.." dialog.
'It can even handle URL's and mailto:

'Open a folder:
'  fHandleFile "C:\TEMP\",WIN_NORMAL

'Call Email app:
'  fHandleFile "mailto:bpo@yahoo.com",WIN_NORMAL

'Open URL:
' fHandleFile "http://uk.yahoo.com";, WIN_NORMAL

'Handle Unknown extensions:
' fHandleFile "C:\TEMP\TestThis",Win_Normal
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Spam, spam, spam, spam, spam, spam, spam
He's not the Messiah. He's a very naughty boy : Monty Python - The Life of Brian (Terry Jones - RIP)

Last edited by isladogs; 09-19-2019 at 01:32 AM. Reason: Added more detail & code after re-reading the question
isladogs is offline   Reply With Quote
The Following 2 Users Say Thank You to isladogs For This Useful Post:
Cark (09-19-2019), Tera (09-19-2019)
Old 09-19-2019, 12:30 AM   #3
moke123
AWF VIP
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 926
Thanks: 13
Thanked 272 Times in 256 Posts
moke123 will become famous soon enough
Re: How do I use a filepath to open a file in VBA?

Try this http://access.mvps.org/access/api/api0018.htm

moke123 is offline   Reply With Quote
The Following User Says Thank You to moke123 For This Useful Post:
Cark (09-19-2019)
Old 09-19-2019, 12:31 AM   #4
nhorton79
Newly Registered User
 
Join Date: Aug 2015
Posts: 129
Thanks: 16
Thanked 18 Times in 18 Posts
nhorton79 is on a distinguished road
Re: How do I use a filepath to open a file in VBA?

I’ve used the function on this page to open file using their default program:

https://www.devhut.net/2010/06/12/ms...a-open-a-file/

Then you would just call using:
Code:
ExecuteFile(avlist, “open”)

Sent from my iPhone using Tapatalk

nhorton79 is offline   Reply With Quote
The Following User Says Thank You to nhorton79 For This Useful Post:
Cark (09-19-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create subfolder and copy file. Filepath is variable WalterInOz Modules & VBA 12 04-29-2014 04:38 AM
How to use VBA to check whether an Excel .xlsx file is open and close it if open? lookingforK Modules & VBA 11 02-07-2013 12:28 PM
Using stored filepath to attach file to email. firestorm998 Macros 1 08-03-2009 06:37 AM
File Picking to fill a form with filepath notgoodataccess Forms 1 01-18-2006 11:47 AM
How to open a Access file exclusively just by double click the .mdb file name? amolin General 3 08-09-2004 09:27 PM




All times are GMT -8. The time now is 10:43 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World