Exporting File to a Path

[khurram7x]

How I would be able to give user the option to select the path, like a File Selection dialog box, to save the exported file in below command?
At the moment I'm just able to export in a set path, like "d:\test" in below code.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tblBoQ", "d:\test"

K

 

[arnelgp]

Public Function DirPicker(Optional ByVal strWindowTitle As String = "Select a folder to save...") As String
    Dim fd As Office.FileDialog
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    fd.Title = strWindowTitle
    If fd.Show = -1 Then
        DirPicker = fd.SelectedItems(1)
    Else
        DirPicker = vbNullString
    End If
    Set fd = Nothing
End Function

to use:

Dim strPath As String
strPath = DirPicker()
If strPath <> "" Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "tblBoQ", strPath
End If

you must set reference to Microsoft Office Object XX.X on VBE.

 

[khurram7x]

Thank you Arnel, got it worked.