CODEWITHSUNDEEP
ms-accessvbams-access-2013attachment-field
user2521720
user2521720

Reputation: 59

Referring to the filename of an attached file in Access 2013 with VBA

If I have Files in Attachments fields, how can I get a filename as a string using VBA? They're excel files so I want to do something like this:

xlApp.Workbooks.Open("M:\strFilename.xlsx")

Upvotes: 1

Views: 2990

Answers (1)

armstrhb
armstrhb

Reputation: 4152

I found this Access blog that I believe will accomplish what you're looking for. Basically, you save a copy of the file to your %temp% directory and open it.

Useful excerpt:

Public Function TestOpenFirstAttachmentAsTempFile()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Const strTable = "Table1"
    Const strField = "Files" ' Attachment field in Table1

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable)
    'rst.MoveNext ' Uncomment this line to go to the 2nd row in the Table.
    OpenFirstAttachmentAsTempFile rst, strField
    rst.Close
End Function 

Public Function OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String) As String

    Dim rstChild As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim strFilePath As String
    Dim strTempDir As String

    strTempDir = Environ("Temp") ' Get the Temp directory from the environment variable.
    If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.
        Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying recordset.
        strFilePath = strTempDir & rstChild.Fields("FileName").Value ' Append the name of the first (and only) attached file to temp dir.
        If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.
        VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill strFilePath ' delete the file.
    End If

    Set fldAttach = rstChild.Fields("FileData") ' The binary data of the file.
    fldAttach.SaveToFile strFilePath
    rstChild.Close ' cleanup
    VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch  the file.

End Function 'OpenFirstAttachmentAsTempFile

Upvotes: 1

Related Questions