"Save As export:" function to have the option of naming the file before exporting.

[michelle copper]

I have this code up and running for exporting queries. Right now, when I click "EXPORT" button, it prompts me as to which folder I would like to have the file exported to. But it does not ask me to type in the "FILE NAME" or in other words, name the file that I will be exporting. Therefore, what should I be adding to my existing codes to add that option as well?



The codes I have so far is:

 
Private Sub cmdExport_Click()
 
On Error GoTo Err_cmdTest_Click
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
Dim dlgOpen As FileDialog
Dim strExportPath As String
Const conOBJECT_TO_EXPORT As String = "qryExportMetrics"
 
Set dlgOpen = Application.FileDialog(msoFileDialogFolderPicker)
 
With dlgOpen
  .ButtonName = "Export To"
  .InitialView = msoFileDialogViewLargeIcons
  .InitialFileName = CurrentProject.Path
     If .Show = -1 Then
       'Allow for Root Directory selection: C:\, D:\, etc.
       strExportPath = Replace(.SelectedItems(1) & "\", "\\", "\")
 
       Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:=conOBJECT_TO_EXPORT, _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
 
 Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qry3", _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qry4", _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qryCapacity", _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
 
 
 
 
       MsgBox "[" & conOBJECT_TO_EXPORT & "] has been Exported to " & strExportPath & _
               conOBJECT_TO_EXPORT & ".xls", vbInformation, "Export Complete"
     End If
End With
 
'Set the Object Variable to Nothing.
Set dlgOpen = Nothing
 
Exit_cmdTest_Click:
  Exit Sub
 
Err_cmdTest_Click:
  MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    Resume Exit_cmdTest_Click
 
 
End Sub
 
 

[NeoPa]

Michelle, for many people choosing ADezii's code to work with would be a great idea. At your level of understanding though, I suggest you'd be better advised taking the code from the linked article instead. Let me explain.

Most people would see this code and understand why and how it works. When they needed minor modifications such as what you ask for here, they would work with the code and make the changes, which are relatively straightforward. At your level of understanding though, where you need to ask for help for such things, it would make more sense to have a routine that is written to be flexible and to be used within various different scenarios (as is so for the code in the article referred to in the earlier thread - Select a File or Folder using the FileDialog Object). That way, all you need to understand is how to call this one procedure.

Let me see if I can give you some help setting this up :

1. Grab the code for FSBrowse() from the linked article and put it into a standard module in your project.
   1. From your Access Window press Alt-F11 to switch to the VBA IDE.
   2. Show the Project Explorer pane (Ctrl-R).
   3. Underneath your main project are up to three folder icons. The top one will contain modules for your main objects (Forms; Reports; etc). Underneath that may be one called Modules if any exist.
   4. If this exists there may be a number of modules connected to it. You can either choose one of these to add the code into or ignore these and follow the steps below to add one instead. If you add the code to an existing module then ensure that the first two lines go at the very top of it. If either is already present then it won't need to be duplicated.
   5. To add a new module, should you need or choose to, put the mouse inside the Project Explorer pane, right-click and select Insert... followed by Module.
   6. Paste the code you grabbed previously into here replacing anything already in there.
   You now have a project which includes the FSBrowse() function.
2. Now we just need to manipulate your code a little to use this function instead of coding it directly as ADezii's code did (which was good news for helping more advanced coders understand the subject better, but less so for you).
3. Replace your code then, with :
   

   'Must 1st set a Reference to the Microsoft Office XX.X Object Library
   Private Sub cmdExport_Click()
   On Error GoTo Err_cmdTest_Click
       Dim strExportPath As String
    
       strExportPath = CurrentProject.Path & "\"
       strExportPath = FSBrowse(strStart:=strExportPath, _
                                lngType:=msoFileDialogFilePicker, _
                                strPattern:="MS Excel Workbooks,*.XLS; *.XLSX~" & _
                                            "All Files,*.*")
       Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                      TableName:="qryExportMetrics", _
                                      FileName:=strExportPath)
       Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                      TableName:="qry3", _
                                      FileName:=strExportPath)
       Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                      TableName:="qry4", _
                                      FileName:=strExportPath)
       Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                      TableName:="qryCapacity", _
                                      FileName:=strExportPath)
       Call MsgBox(Prompt:="Your queries have been exported to " & strExportPath, _
                   Buttons:=vbInformation, _
                   Title:="Export Complete")
       Exit Sub
    
   Err_cmdTest_Click:
       Call MsgBox(Err.Description, vbExclamation, "Error in cmdTest_Click()")
   End Sub

This should leave you with a fully integrated system that you can use relatively straightforwardly wherever required in your project.

[michelle copper]

Thanks NeoPa! I know I am such a pain to you and to the rest of the experts.. I am trying my best to learn as much as possible as I am very new to Access.


Anyways, I tried what you just said. I created FSBrowse module by copy and paste.

Then I put the code to the export button and it is giving me error.
the codes are as follow:

Private Sub cmdExport_Click()
 
'Must 1st set a Reference to the Microsoft Office XX.X Object Library
 
On Error GoTo Err_cmdTest_Click
    Dim strExportPath As String
 
    strExportPath = CurrentProject.Path & "\"
    strExportPath = FSBrowse(strStart:=strExportPath, _
                             lngType:=msoFileDialogFilePicker, _
                             strPattern:="MS Excel Workbooks,*.XLS; *.XLSX~" & _
                                         "All Files,*.*")
    Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                   TableName:="qryExportMetrics", _
                                   FileName:=strExportPath)
    Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                   TableName:="qryCapacityBuilding", _
                                   FileName:=strExportPath)
    Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                   TableName:="qryPresentingProblem", _
                                   FileName:=strExportPath)
 
    Call MsgBox(Prompt:="Your queries have been exported to " & strExportPath, _
                Buttons:=vbInformation, _
                Title:="Export Complete")
    Exit Sub
 
Err_cmdTest_Click:
    Call MsgBox(Err.Description, vbExclamation, "Error in cmdTest_Click()")
 
 
End Sub
 
 

The error message says "COMPILE ERROR: EXPECTED VARIABLE OR PROCEDURE, NOT MODULE" for line 9

[NeoPa]

That makes no sense to me. Just to be clear, you're saying the code that's highlighted when you compile it is :

    strExportPath = FSBrowse(strStart:=strExportPath, _
                             lngType:=msoFileDialogFilePicker, _
                             strPattern:="MS Excel Workbooks,*.XLS; *.XLSX~" & _
                                         "All Files,*.*")

If that is true then I'm very confused.

In that case (that you confirm this is exactly what you are getting) you may want to attach a copy of the database. There are some important instructions to follow for that which can be found in Attach Database (or other work). It looks complicated but if you follow each instruction one at a time it's pretty straightforward really. If you do that I'll look into it for you and see if I can work out what's going wrong.

[ADezii]

@Michele: I hope I am not confusing matters, but it appears as though everything that you are saying indicates the need for a 'True' Save As Dialog Box. Simply download the Attachment and see if it is what you need, it needs no References and is completely self contained.

[NeoPa]

I'm not sure that's the problem here ADezii, but if so lngType:=msoFileDialogSaveAs would give that facility. It probably is a better way to use the function actually, as I expect it gives the opportunity to enter a non-existent filename rather than simply select an existing one.

I just tested it and that's certainly a better way of doing it. Thanks to ADezii for noticing that :-)

PS. I like the new avatar ADezii. Different.

[michelle copper]

I just tried what ADezii had posted and that is what I was looking for and wanted.
Its working perfectly when I add those codes to my database but if you see my codes, I am exporting multiple queries as well. I tried adding this codes but it is giving me error

Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                                   TableName:="qryCapacityBuilding", _
                                   FileName:=strExportPath)
 
 

Line 3 is the error
and says "Compile error: Variable not defined"

I tried changing it to "conOBJECT_TO_EXPORT" for FileName but it did not work either

[NeoPa]

I'm not sure why it would make sense to try on a completely different tack at this stage of the question Michelle. Do so if you think it would help of course, but it's not clear that's the case here. Why not post an answer to my post #4 anyway and that way there are more solutions to choose from.

I'm sure ADezii's solution can work, but you're not going to find it any easier incorporating that into your project than you will the FileDialog approach.

[ADezii]

@Michele: I am totally at a loss at this point, so I created this Final Demo as a last ditch effort. This Demo will allow you to enter a Filename in a Save As Dialog Box, then Export 5 Querys to Excel Spreadsheets named after the Filename you entered. Let's suppose that you wish to Save the Exports as DemoExport.xls in a Folder named C:\MyExports. Here is how the 5 Querys named Query1...Query5 will be Exported and named:

Query1 ==> C:\MyExports\DemoExport.xls
Query2 ==> C:\MyExports\DemoExport_2.xls
Query3 ==> C:\MyExports\DemoExport_3.xls
Query4 ==> C:\MyExports\DemoExport_4.xls
Query5 ==> C:\MyExports\DemoExport_5.xls
 

All you have to do is to substitute your Query Names in Code Lines# 22, 23, 25, 27, and 29:

'Module Code intentionally not shown
Dim cDlg As New CommonDialogAPI         'Instantiate CommonDialog
Dim lngFormHwnd As Long
Dim lngAppInstance As Long
Dim strInitDir As String
Dim strFileFilter As String
Dim lngResult As Long
Dim strBaseName As String
 
lngFormHwnd = Me.Hwnd                           'Form Handle
lngAppInstance = Application.hWndAccessApp      'Application Handle
strInitDir = CurrentProject.Path                'Initial Directory - [UD]
 
'Create any Filters here - [UD]
strFileFilter = "Excel Files (*.xls)" & Chr(0) & "*.xls"
 
lngResult = cDlg.SaveFileDialog(lngFormHwnd, _
            lngAppInstance, strInitDir, strFileFilter)
 
If cDlg.GetStatus = True Then
 strBaseName = Split(cDlg.GetName, ".")(0)
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", cDlg.GetName
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query2", _
                                      strBaseName & "_2.xls"
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query3", _
                                      strBaseName & "_3.xls"
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query4", _
                                      strBaseName & "_4.xls"
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query5", _
                                      strBaseName & "_5.xls"
Else
  Exit Sub
End If
 

P.S.-It is imperative that you download the Attachment and change nothing but the Code Lines previously mentioned, or Remove/Add a TransferSpreadsheet Line.

[michelle copper]

Thanks to you two! I will try them soon and see which one works.

[neelsfer]

Thx Adezi for that code - i have been looking for it.
I used it in my project to export a race report to Excel - works 200% as usual!!
For anybody else requiring this slight variation to export a report to Excel, and not a Query

DoCmd.OutputTo acOutputReport, "ReportName", acFormatXLS, cDlg.GetName

[NeoPa]

Michelle:
Thanks to you two! I will try them soon and see which one works.

I'm sure both can work Michelle, but let us know how you get on anyway.

[ADezii]

@neelsfer: Glad you found the Code useful. Be advised that you do not need to type the File Extension for the actual Save As Filename. It will automatically append a .xls Extension if none is provided. This Default can be changed if so desired.