How do I use the following VBA code to create a command button on my form that produces "Save As" Dialog Box and exports Access queries?

Question

This question is related to the following two posts:

How to run a query when a button is pressed and export results to Excel File

“Save as…” dialog box in MSAccess vba: how?

I currently have a parameterized query (lets call it "qryExport") that fetches values from certain controls on a form (lets call it "Form A"). From the aforementioned link, I understand that I could use the following line of code for a "Form A" command button's "on click" event that could export "qryExport" as an Excel file at a fixed file path location.

DoCmd.TransferSpreadsheet acExport, , "qryExport", "C:\yourPath\exportedReport.xlsm", True

Problems:

However, I need to produce a command button for "Form A" that performs the following when a user clicks it:

1) Prompts the user to name the soon-to-be exported file.

2) Allows the user to designate where on their machine they would like to save the exported "qryExport" object.

3) Allows the user to select the type of file they would want to export "qryExport" as (e.g. Excel, XML, Txt, etc.).

4) Performs the export operation once the file path designation, file name, and desired file type has been selected by the user.

I will be distributing my form to multiple users (they work at different workstations), which necessitates that my command button meet the aforementioned requirements.

I've considered a possible solution is to add a "Save As" prompt when the user clicks the command button. It would asks the user to designate the file path, choose what file format they would like their file to be saved as (Excel, XML, Txt, etc.) in their workstation, and allow them to give a name to the new file.

I found VBA code that allows a command button to produce a "Save As" window (notably, it only gives users the option to save the file as an Excel file) (please see the code below the link):

VBA Save As origin

Option Compare Database
Private mstrFileName As String
Private mblnStatus As Boolean
'Declare needed functions
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
(pOpenfilename As OPENFILENAME) As Long

Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" _
(pOpenfilename As OPENFILENAME) As Long

'Declare OPENFILENAME custom Type
Private Type OPENFILENAME
  lStructSize As Long
  hwndOwner As Long
  hInstance As Long
  lpstrFilter As String
  lpstrCustomFilter As String
  nMaxCustFilter As Long
  nFilterIndex As Long
  lpstrFile As String
  nMaxFile As Long
  lpstrFileTitle As String
  nMaxFileTitle As Long
  lpstrInitialDir As String
  lpstrTitle As String
  Flags As Long
  nFileOffset As Integer
  nFileExtension As Integer
  lpstrDefExt As String
  lCustData As Long
  lpfnHook As Long
  lpTemplateName As String
End Type

'Function needed to call the "Save As" dialog
Public Function SaveFileDialog(lngFormHwnd As Long, _
lngAppInstance As Long, strInitDir As String, _
strFileFilter As String) As Long

Dim SaveFile As OPENFILENAME
Dim X As Long

If IsMissing(strFileName) Then strFileName = ""

With SaveFile
 .lStructSize = Len(SaveFile)
 .hwndOwner = lngFormHwnd
 .hInstance = lngAppInstance
 .lpstrFilter = strFileFilter
 .nFilterIndex = 1
 .lpstrFile = String(257, 0)
 'Use for a Default File SaveAs Name - [UD]
 '.lpstrFile = "testfile.txt" & String(257 - Len("testfile.txt"), 0)
 .nMaxFile = Len(SaveFile.lpstrFile) - 1
 .lpstrFileTitle = SaveFile.lpstrFile
 .nMaxFileTitle = SaveFile.nMaxFile
 .lpstrInitialDir = strInitDir
 .lpstrTitle = "Enter a Filename to Save As"        '[UD]
 .Flags = 0
 .lpstrDefExt = ".xls"   'Sets default file extension to Excel,
                         'in case user does not type it - [UD]
End With

X = GetSaveFileName(SaveFile)

If X = 0 Then
  mstrFileName = "none"
  mblnStatus = False
Else
  mstrFileName = Trim(SaveFile.lpstrFile)
  mblnStatus = True
End If
End Function
Public Property Let GetName(strName As String)
  mstrFileName = strName
End Property
Public Property Get GetName() As String
  GetName = mstrFileName
End Property
Public Property Let GetStatus(blnStatus As Boolean)
  mblnStatus = blnStatus
End Property
Public Property Get GetStatus() As Boolean
  GetStatus = mblnStatus
End Property

However, that code does not meet all of me need. I have no idea on how I can use combine all of this code to produce the desired command button on my form.

As always,thank you for your time.

Please note: I am using Access 2010

Answer 1

Have you looked at the capabilities of the FileDialog option? You can do everything you mentioned using this; no need for the WinAPI stuff. The real issue is that the DoCmd.TransferSpreadsheet command will only produce an Excel file.

If you want to create some other filetype, you will need to:

• Create the FileDialog object set fd = Application.FileDialog(msoFileDialogFilePicker)
• Setup the FileDialog object
  • Clear previous extensions from object fd.Filters.Clear
  • Provide available extensions fd.Filters.Add "Query Output", "*.xls; *.txt; *.xml"
  • Turn off multiselect in this case fd.AllowMultiSelect = False
  • Provide default filename fd.InitialFilename = "<desired start path>\<desired filename>.<desired extension>"
• .Show it to the user and return the result of fd.Show
  • If fd.Show Then (they didn't hit cancel or close the dialog box)(Show = true if they press save)
  • Look at the extension of the filename they chose for the next steps
    • If Instr(fd.SelectedItems(0), ".xls") > 0 Then 'Do the xls stuff
    • If Instr(fd.SelectedItems(0), ".xml") > 0 Then 'Do the text stuff
    • Else 'Do the xml stuff
  • Parse the output of the query into the format of the user's choosing
    • Create textfile or XML by parsing the contents of the query result.
    • Create XLS using the TransferSpreadsheet command.

The command will not just create the file type you want. You need to control that.
Read this link for more info for 2013 and 2010.
Read this link for better descriptions of the properties of the FileDialog object

To make this part of a command button click event, just write the code in the command button's Click Event procedure for the form.