How to I create my Export to have "Save Exported File As:" ?

[michelle copper]

I have codes where I can export multiple queries to one single excel workbook but the problem with my code is that it does not ask me where I would like them to be exported to. Like to my desktop or my documents.

Right now the codes which you see below exports automatically to C Drive.

Does anyone know what I have to add to my codes for it to prompt me where I would like the file to be saved before exporting?

 

Public Sub ExportXLS()
#If Not CC_Debug Then
  On Error GoTo ErrProc
#Else
  On Error GoTo ExitProc
#End If
 

Const conBASE_PATH As String = "C:\Exports\"
Const conEXPORT_OBJ  As String = "qryExportMetrics"
 

'If the Folder C:\Exports does not exist, creatwe it
If Dir$(conBASE_PATH, vbDirectory) = "" Then
 MkDir "C:\Exports"
End If
 

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, conEXPORT_OBJ, _
                            conBASE_PATH & conEXPORT_OBJ & ".xls", True
 
                               
Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qryActivity", _
                               FileName:="C:\Exports\qryExportMetrics.XLS") 


Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qryCalled911", _
                               FileName:="C:\Exports\qryExportMetrics.XLS")


Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qryCalled911Activity", _
                               FileName:="C:\Exports\qryExportMetrics.XLS")


   

ExitProc:
    Exit Sub
ErrProc:
    ErrMsg Err, Err.Description, Err.Source
    Resume ExitProc
End Sub

Thanks and any help will be appreciated!

[ADezii]

Michele, this scenario is the perfect candidate for the Microsoft Office File Dialog. I'll post the Code as well as an Attachment that will fully demonstrate the concept. Should you have any questions, feel free to ask.

P.S. - You must have a Reference set the the Microsoft Office XX.X Object Library.

'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 = "Employees"

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")
                               
       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

[NeoPa]

I may still knock up an article for this Michelle as it's an important subject that's often required, but clearly ADezii's beaten me to the punch already on this one. I haven't checked it through in any detail, but because it comes from him I can tell you (if you even needed telling after he's already helped you in some of your other threads) that it reliably answers your current need.

If/when I get the article finished I'll post a link to it in here for interest.

[ADezii]

@NeoPa:
It seems as though many individuals resort to rather complex API Code for the File/Folder Open Dialog, when, in the majority of cases, a more simplistic approach (Office Dialog) is all that is needed.

[NeoPa]

See Select a File or Folder using the FileDialog Object for more.

Yes ADezii. I'm with you on this one. I suspect most people just fail to notice this facility is already available as standard in MS Office.

[Sedrick]

Do you know offhand if this works with Access 97? I noticed it is using the Office11 library.

Thanks!

Sedrick

[ADezii]

To the best of my knowledge, the Office FileDialog Object does not exist in Access 97.

[Sedrick]

Thanks! That must be why I went CommonDialogAPI a while back.

[michelle copper]

Sorry NeoPa and Adezii..
I tried working on it over the weekends.
I still did not figure out about the Dialog box.
How do I go about? or where is it located?

[ADezii]

Michele, did you download my Attachment in Post #2?

[michelle copper]

I did.

Anyways, I will try again now and let you know soon

thanks

[NeoPa]

I would expect that what ADezii has posted (including the attachment) would be enough to enable most people to accomplish this. Failing that, my article (linked to in post #5) goes into fairly exhaustive detail and explains it all fairly clearly. If you find, after reading all that and looking at the attachment there (The article includes its own attached database for further illustration of the subject), that you are still unsure of what's what then please let us know exactly what you are struggling with and I'm sure one of us can put you straight. It's hard to respond directly though, if you don't give any indication of what your confusion is and we've already explained it in detail.

[michelle copper]

I copied and paste what Adezii gave in post 2 and I changed EMPLOYEES to qryExportMetric s (because that was my query name).
But it won't allow me to export. I even copied by the attachment and paste it, it still won't work. It highlights and take me back to the codes. I know I have done something wrong.
and I went to tools/references to check if my Microsoft Office XX.X Object Library is included/set and it is.

Is there something where I am missing? I tried to read what Neopa posted.
But anyways, I will still work on it and see where I went wrong

[NeoPa]

Michelle:
I tried to read what NeoPa posted.

And?

Was there something there that was confusing?

It's written to cover the subject quite fully. Is there something missing? Was the attached database not illustration enough of how everything works? I'd like to help but it's difficult with no information communicated.

[michelle copper]

:S

okay.. this is what I have so far. I was able to have that "Save As" prompt but my other queries were not exporting.

Public Sub ExportXLS()

#If Not CC_Debug Then
On Error GoTo ErrProc
#End If
    
    Const cQuery As String = "qryExportMetrics"
                 
  
    Dim fc As FileChooser
    Dim strFileName As String

    Set fc = New FileChooser
    fc.DialogTitle = "Select file to save"
    fc.OpenTitle = "Save"
    fc.Filter = "Excel Files (*.xls)"
    strFileName = Nz(fc.SaveFile, "")
    Set fc = Nothing
    
    ' If user selected nothing or canceled, quit
    If Len(strFileName) = 0 Then
        Exit Sub
    ' If file already exists, delete it
    ElseIf Len(Dir(strFileName)) > 0 Then
        Kill strFileName
    End If
    
    DoCmd.TransferSpreadsheet _
        acExport, _
        acSpreadsheetTypeExcel9, _
        cQuery, _
        strFileName, _
        HasFieldNames:=True

Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qry3", _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
                               
          Call DoCmd.TransferSpreadsheet(TransferType:=acExport, _
                               TableName:="qryCapacityBuilding", _
                               FileName:=strExportPath & conOBJECT_TO_EXPORT & ".xls")
        
          MsgBox "Your download is completed."
        
ExitProc:
    Exit Sub
ErrProc:
    ErrMsg Err, Err.Description, Err.Source
    Resume ExitProc
End Sub