How do I use a filepath to open a file in VBA? (1 Viewer)

Cark

Registered User.
Local time
Today, 14:18
Joined
Dec 13, 2016
Messages
153
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.
 

isladogs

MVP / VIP
Local time
Today, 21:18
Joined
Jan 14, 2017
Messages
18,186
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
 
Last edited:

Dreamweaver

Well-known member
Local time
Today, 21:18
Joined
Nov 28, 2005
Messages
2,466
Thanks @isladogs Been trying to get the code I had work with both 32/64 bit for my examples

This was the bit I missed lol
Code:
#If VBA7 Then
    Dim lRet As LongPtr
#Else
    Dim lRet As Long
#End If

Hope you don't mind me including it with the templates I'm working on, I'l prob add it to the next version of the master template Here
As Always There will be credit

thanks mick
 

zeroaccess

Active member
Local time
Today, 16:18
Joined
Jan 30, 2020
Messages
671
Just

SQL:
    Application.FollowHyperlink "C:\Temp\File.xlsx"
 

isladogs

MVP / VIP
Local time
Today, 21:18
Joined
Jan 14, 2017
Messages
18,186
No. Application.FollowHyperlink opens any file for which there is a default application. See post #2.
 

Kayleigh

Member
Local time
Today, 21:18
Joined
Sep 24, 2020
Messages
706
Is it possible to create folders using VBA, following specific naming convention?
And can I move files from one folder to another (say from Scans to specific staff records) within Access?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 16:18
Joined
Feb 28, 2001
Messages
27,001
@Krayna - you need to look up something called the File System Object (FSO), which has the ability to create folders. Here is a starting point for reading about its abilities:


If you have set up an FSO in your code, and then have set up the appropriate path string, there is a .CreateFolder method you could use.

Moving files from one folder to another can be done with VBA's Name As verb (which you can also look up) as long as the two folders are on the same drive. If they are on different drives, the File System Object has the ability to do a .MoveFile method that will do a disk-to-disk file move as well as a folder-to-folder move.

Once you have identified a folder, you can get a list of file names within that folder using a Folder object


Browse around within the links I just gave you to see the tools that would do what you asked.

As a side note, you could have opened a new thread for this. You posted at the end of an old, more-or-less abandoned thread that had seen no action for over 6 months. This is NOT an attempt to chastise you, just a note that it is SOMETIMES better to start a new thread. You get more attention with a new thread. But you didn't do anything WRONG.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:18
Joined
Oct 29, 2018
Messages
21,358
Is it possible to create folders using VBA, following specific naming convention?
And can I move files from one folder to another (say from Scans to specific staff records) within Access?
Also, take a look at this other thread.
 

Users who are viewing this thread

Top Bottom