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.

 

[Mr. B]

Additional code to actually open the spreadsheet to which you are exporting the data. You are only putting the data into the sheet, you do not have the Excel file open.

Do some searching here on the forum for code that will actually open the spreadsheet. Then you will just need to apply the appropriate formatting to the desired areas of the sheet using code. You can acquire much of this code by recording macros and then using that code as needed.