1

This is directly related to the following two posts:

How do I export 2 queries to a single worksheet in excel from access

Saving a custom export from an Access 2007 script

I am using Access 2010, and I have created a command button on a form with the following VBA in its Click event. The VBA allows me to export multiple queries, into different worksheets, within the same Excel file:

VBA #1: This exports multiple queries into one Excel file

Private Sub CommandBtn_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query1", "test1.xlsx", True, "NameofWorksheet"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Query2", "test1.xlsx", True, "NameofSecondWorksheet"
End Sub

Edit(4/7/2014: 11:26 AM): Clarified what I want below.

Desire

I want to add the following feature to my command button's Click event: A "Save As" dialog window that prompts the user to choose where to save the newly created Excel file (test1.xlsx).

Challenges:

I do not know what VBA code would be appropriate to use for doing so. I ran into the FileDialog(msoFileDialogSaveAs) option when doing my research on this. However, I have not been able to successfully use it to produce what I want.

Community
  • 1
  • 1
ealfons1
  • 353
  • 1
  • 6
  • 24

1 Answers1

1

I think you want to get the file selection before DoCmd.TransferSpreadsheet, because the file selection is the transfer target.

I'm not sure exactly what you want, but I think this should be a step closer ...

Private Sub CommandBtn_Click()
    Dim fileSelection As Object
    Dim strPath As String

    Set fileSelection = Application.FileDialog(2)
    With fileSelection
        .AllowMultiSelect = False
        If .Show = True Then
            strPath = .SelectedItems(1)
            ' use strPath with TransferSpreadsheet ...
            'DoCmd.TransferSpreadsheet
        Else
            MsgBox "no file selected"
        End If
    End With
End Sub
HansUp
  • 95,961
  • 11
  • 77
  • 135
  • I want to export queries into one excel file, and then have the dialog window open up and ask the user where you want the newly created Excel file to be saved. I will reword my question to help clarify this. – ealfons1 Apr 07 '14 at 15:01
  • So you want to first export the queries to `test1.xlsx` and then rename `test1.xlsx` with a name chosen by the user? – HansUp Apr 07 '14 at 15:40
  • Yes, that is correct HansUp. I want them to also have the ability to select where on their machine they can save the file as well. – ealfons1 Apr 07 '14 at 15:54
  • You can use a `Name` statement to rename a file. Is that the missing piece? – HansUp Apr 07 '14 at 16:03
  • Upon successfully using the code above, I was mistaken in believing there was something missing. The code you provided meet my criteria. Therefore, there is no need for a Name statement. – ealfons1 Apr 07 '14 at 17:04