Export Queries to Excel tabs with Save as Dialog box

[deepanadhi]

Hi,

I have to export 4 queries into 4 sheets of single excel file. I dont want the file path to be hard coded, instead user should be able to select the file location unsing Save Dialog box. My Code works fine for getting 4 sheets into Excel sheet well. Also please check in the following code, that where should i add code to format the Excel sheet, i,e Font, color, Bold etc plz give sample code for that.



What changes and additions shud be made inthe following code. Thanks in Advance.

Private Sub Command42_Click()
Dim fDialog As Object
Dim varFile As Variant
Set fDialog = Application.FileDialog(MsoFileDialogType)
With fDialog
.AllowMultiSelect = False
.Title = "Select File Location to Export XLSx :"
.InitialFileName = "DN.xlsx"
If .Show = True Then
For Each varFile In .SelectedItems
getFileName = varFile
Next
End If
End With
On Error GoTo Do_Nothing
 
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_apcc", "C:\Users\Public\Documents\Discp_ASN.xls", True, "D"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_asn", "C:\Users\Public\Documents\Discp_ASN.xls", True, "AN"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_apcc_part", "C:\Users\Public\Documents\Discp_ASN.xls", True, "Pal"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qry_apcc_cfg", "C:\Users\Public\Documents\Discp_ASN.xls", True, "Cal"
    
    MsgBox "The tables have been successfully exported"
Exit Sub
Do_Nothing:
    MsgBox "Export has failed.  An error occurred or the user terminated the operation."
    
End Sub

Thanks.

[June7]

Review https://www.accessforums.net/showthr...c-Excel-format