Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127

    How to add attachments to via a button that opens a browse dialog to select file from


    Hi every one. I have a table "table1" and attachment field "files". I have been trying for weeks searching for a code to add attachments to my table from a button on a form that opens a browser dialog window to choose a file or files from and the chosen files are automatically added to my table. I am new to vba. but using several different codes I was able to open the browser dialog. But non of the codes I try with that attaches the files. Please help me coding this

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Here is some code...
    Attached Files Attached Files

  3. #3
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Hi Its Me thanks a lot. It works just as expected. Thanks a lot. But I just wonder if it could be modified or if you could give one more code to attach multiple for one record and save multiples back to a folder just like this one attach or save one record. Thanks in advance

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Correct, you will need additional code to enumerate a folder and or an attachment field.

    When I get back to my computer I will see what I can find for examples. Meanwhile, you might search on how to loop. Maybe do while, while, do until....

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Here is an example of using Do While to loop through the attachment field of a given DAO recordset.
    https://www.accessforums.net/import-...der-25651.html

    Here is an example of looping through a folder and finding and killing all files of a given extension type. The example is for transfertext or spreadsheet or something, but the idea is the same.
    https://www.accessforums.net/import-...tml#post212117

    You may want to use the filesystemobject library to get files from of folder instead of using the string method. I didn't find any examples on the web buit can dig up some code from one of my DB's if you want.

  6. #6
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    Have a look in the folder for code atachments (this site, main menu at the bottom).

    I have provided vba code to send emails - you can select from a list of preselected employees (table with send email check box),
    then attach multiple files to this (the file paths are saved in another table),

  7. #7
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    yep that would be very kind of you. I am very bad at vba. we will try thank you

  8. #8
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    The following is an example of using the filesystemobject ref library to create some system objects and delete all of the PDF files in a given folder.

    Code:
    'Delete the PDF files in the temp folder
    Dim strFile As String
        strFile = "\\ServerName\FolderName\TempFolder"
    Dim fsoFile As New FileSystemObject
    Dim objFolder As Folder
    Dim objFile As File
        Set objFolder = fsoFile.GetFolder(strFile)
        
            For Each objFile In objFolder.Files
            
                If InStr(objFile.Name, ".PDF") Then  'locate a filename that contains .PDF in the name
                    objFile.Delete True   'Delete the file
                End If
            
            Next
        
        Set objFolder = Nothing
    'end 'Delete the PDF files in the temp folder
    You can also use the filesystemobject to copy an entire directory. This may help to copy a folder located by the user via the filepicker to a temp folder. The idea of the temp folder is to loop through and delete. If you do not delete a file you will be in a perpetual loop. The other approach would be to enumerate the files and then go back through your enumerated list to import the files into your DB.


    Here is some sample code to copy a folder.

    Code:
    Dim sourceFile As String, destinationFile As String
    Dim aFSO As Object
    
    sourceFile = "C:\Test\Sub1"
    destinationFile = "C:Test\NewFolder"
    Set aFSO = CreateObject("Scripting.FileSystemObject")
    CopyFolder = False
    aFSO.CopyFolder sourceFile, destinationFile
    CopyFolder = True

  9. #9
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Hi its me Its really very help ful. I appreciate your help. You have really helped me solve the first part: Browsing and attaching a file to table in the above sample data base. Now I wan't browse and attach more than onefile. multiselect and attach. as you said looping. I have been trying different codes. all I get is errors. If we can modify the codes in that database it should work. But so far I can't see a matching code over the net.

  10. #10
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    What have you tried so far?

  11. #11
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    Thanks Its me. today I was trying to modify this code DoCmd.GoToRecord , "", acLast
    Forms!AddNew!AddID = Me.ID
    'Const strRootFolder As String = "Me.[Folder]" " previously this was a path c:\ etc. now I write the path in a text box "folder". It adds all file to a separate record.
    AddAttachmentsFromFolder Me.Folder, "Table1", "Files", _
    "*.*", True
    MsgBox "Done adding files from: " & vbCrLf & strRootFolder, _
    vbInformation, "File Import Process Completed"
    --------------------------
    so I have finished testing if it works when the folder path is given in a field or text box. It does. so now I have to find how I can get the folder name without the file name.
    on code I tested just now gives the folder path and file name. but It should give only the path without the name. I use a module with the pasted code. but I don't know how to find the forum code paste button.

  12. #12
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127
    here is the module I use with the ccode
    -----------------------------
    Option Compare Database
    ' -------------------------------------------------------------------------
    ' Procedure : StoreFilesInTable
    ' Purpose : Adds all files matching the specified file mask from the
    ' : specified folder to an attachment field.
    ' : Each row in the represents all files stored from the folder.
    ' Arguments : strFolder - The path to the folder stored in the attachment field.
    ' : strTable - The name of the table containing the attachment field.
    ' : strPathField - The name of the field for the archived folder.
    ' : strAttachmentField - The name of the attachment field. [Files]
    ' : strPattern - File mask. [*.*]
    ' : blnIncludeSubfolders - Recurse into subfolders. [False]
    ' : db1 - The database to operate on. [CurrentDb]
    ' Comments : The db1 param is included so this can be used to store files in
    ' : a separate database, since using the attachment field this way
    ' : can quickly push a database beyond the 2gb file size limit.
    ' -------------------------------------------------------------------------
    Public Function StoreFilesInTable( _
    ByVal strFolder As String, _
    ByVal strTable As String, _
    ByVal strPathField As String, _
    Optional ByVal strAttachmentField As String = "Files", _
    Optional ByVal strPattern As String = "*.*", _
    Optional ByVal blnIncludeSubfolders As Boolean = False, _
    Optional ByRef db1 As DAO.Database)
    Const CALLER = "StoreFilesInTable"
    On Error GoTo StoreFilesInTable_ErrorHandler
    Dim strFilePath As String
    Dim rstParent As DAO.Recordset2
    Dim rstChild As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    ' These objects require a reference to the "Microsoft Scripting Runtime"
    ' but are defined as "Object" instead to use late binding and avoid that.
    ' If you've added the reference, remove the "Object" and uncomment the
    ' following lines to get the intellisense autocomplete for these objects.
    Dim objFso As Object ' Scripting.FileSystemObject
    Dim objFolder As Object ' Scripting.Folder
    Dim objSubFolder As Object ' Scripting.Folder
    Dim objFile As Object ' Scripting.File
    ' If the user did not specify a database, use the current one.
    If db1 Is Nothing Then Set db1 = Application.CurrentDb
    ' Instantiate the FileSystemObject.
    Set objFso = CreateObject("Scripting.FileSystemObject")
    ' Make sure the folder path always ends with a "\".
    If (Right(strFolder, 1) <> "\") Then strFolder = strFolder & "\"
    ' Make sure the folder exists.
    If Not objFso.FolderExists(strFolder) Then
    MsgBox "Folder does not exist: " & strFolder, _
    vbExclamation, CALLER
    Exit Function
    End If
    ' It exists, so get the folder object.
    Set objFolder = objFso.GetFolder(strFolder)
    ' Open the table containing the attachment field
    Set rstParent = db1.OpenRecordset(strTable)
    rstParent.AddNew
    rstParent.Fields(strPathField).Value = objFolder.Path
    ' Get the first file in this directory.
    strFilePath = Dir(strFolder & strPattern)
    ' Store each file that meets the pattern
    While (Len(strFilePath) > 0)
    Set rstChild = rstParent.Fields(strAttachmentField).Value
    rstChild.AddNew
    Set fldAttach = rstChild.Fields("FileData")
    fldAttach.LoadFromFile strFolder & strFilePath
    rstChild.Update
    rstChild.close
    strFilePath = Dir() ' Get the next file
    Wend
    ' Commit the new row with the attachments field populated
    ' with all of the files from the current folder.
    rstParent.Update
    ' Recurse into subfolders if requested.
    If (blnIncludeSubfolders) Then
    For Each objSubFolder In objFolder.SubFolders
    StoreFilesInTable objSubFolder.Path, strTable, _
    strPathField, strAttachmentField, _
    strPattern, blnIncludeSubfolders, db1
    Next
    End If
    Cleanup:
    rstParent.close
    Set rstParent = Nothing
    Exit Function
    StoreFilesInTable_ErrorHandler:
    Debug.Print "Error # " & Err.Number & " in " & CALLER & " : " & Err.Description
    MsgBox Err.Description, vbCritical, "Error # " & Err.Number & " in " & CALLER
    GoTo Cleanup
    End Function 'StoreFilesInTable
    ----------------------------------------------

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    Quote Originally Posted by aspen View Post
    ... but I don't know how to find the forum code paste button.
    Click, "Go Advanced" and then the hashtag (#) to insert code tags into the body of the post.

    In the sample DB, the form that exports files from the attachment field uses the file picker. It will select a folder path and store the path in strPath. The string variable is declared in the header of the form's module so the string variable (path) is available throughout the form.

    You could test that code and try to get strPath to display in your textbox to make sure you are getting it done correctly.

  14. #14
    aspen is offline Competent Performer
    Windows Vista Access 2010 64bit
    Join Date
    Jun 2011
    Posts
    127

    Smile

    Code:
    ' Grab a copy of the Office file dialog
    With Application.FileDialog(msoFileDialogFolderPicker)
    ' Set the dialog title
    .Title = "Locate a folder to export"
    ' Set the button caption
    .ButtonName = "Choose"
     
    .InitialFileName = "C:\"
    ' Show files as thumbnails
    .InitialView = msoFileDialogViewThumbnail
    ' Show the dialog and test the return
    If .Show = 0 Then
    ' Didn't pick a file - bail
    Exit Sub
    End If
    ' Should be only one filename - grab it
    strPath = Trim(.SelectedItems(1))
    Me.Folder = strPath & "\"
    End With
    ' Put focus in a safe place
    Me.SetFocus
    Thanks a lot for the tip. I tried the save code from your db and Its working just the way I wanted. It gives the folder and attach ALL files in folder to table as seprate records. But What I really need is to attache some selected files from the folder. I am able to get the full file path of selected files to a list box. I have tried different codes to get thaem attached to table. but sofr no success. Please see if you can work that out

  15. #15
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    What you have there is the msoFileDialogFolderPicker. This will get the folder directory and there is not an option to select a file name.

    The other example uses the File Picker. You can adjust the File Picker to select multiple files. This will create an array. It will look something like this..
    .SelectedItems(1)

    You will need to change the multiselect option for your file picker and then iterate through the results. Iterate .SelectedItems()

    Maybe something like a For i = 0 through....

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 20
    Last Post: 10-28-2013, 06:26 PM
  2. Button to Browse for File!
    By floyd in forum Forms
    Replies: 5
    Last Post: 08-23-2013, 09:09 AM
  3. Replies: 3
    Last Post: 09-11-2011, 01:25 PM
  4. Replies: 2
    Last Post: 09-11-2011, 05:19 AM
  5. Browse for file
    By ccpine@comcast.net in forum Database Design
    Replies: 0
    Last Post: 08-24-2008, 10:12 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums