VBA - Browsing, selecting file and saving file as

Question

I am creating an access database with forms and reports. in one of my forms I want to be able to go to a folder selecting a file (.pdf, .doc, .xls) and then saving it in a dedicated "attachments" folder.

I know about OLE objects and the attachment functions, but both of these would be saving the attachments within the database perse. I am trying to stay away from this since it is imperative to have readily access to the attachments and also to make the database lighter.

I have been playing in VBA with

Application.FileDialog(msoFileDialogFilePicker)

and

Application.FileDialog(msoFileDialogSaveAs)

this is what I have so far but it is not working:

Option Compare Database

Private Sub Select_Save_Click()

Call SelectFile

End Sub


Public Function SelectFile() As String




 Dim FD As FileDialog
 Dim File_Name As String
 Dim path As String

path = "O:\foldername"

Set FD = Application.FileDialog(msoFileDialogFilePicker)

With FD
    .AllowMultiSelect = False
    .Title = "Please select file to save as attachment"
    If .Show = True Then

        File_Name = Dir(.SelectedItems(1))

        SelectFile = .SelectedItems(1)

        new_name = path & File_Name

        .SelectedItems.Item(1).SaveAsFile new_name

        Me.Attach_Save = new_name


    Else
        Exit Function
    End If

    Set FD = Nothing

End With



End Function

Answer 1

Consider using FileCopy as .SaveAsFile method may not work inside the FD object. Plus, SaveAsFile method is primarily associated with the MS Outlook VBA using email attachments.

Also, use Dir() on the string variable outside the FD object and not on the variant array .SelectedItems(1) inside the FD object.

Finally, notice I add a final back slash in the path string.

Dim FD As FileDialog
Dim File_Name As String
Dim path As String

path = "O:\foldername\"

Set FD = Application.FileDialog(msoFileDialogFilePicker)

With FD
    .AllowMultiSelect = False
    .Title = "Please select file to save as attachment"
    If .Show = True Then
        SelectFile = .SelectedItems(1)
    Else
        Exit Function        
    End If
End With

File_Name = Dir(SelectFile)       

FileCopy SelectFile, path_name & File_Name

Set FD = Nothing