Export Query to Excel with Flexible Fields and Format

[sun_after_rain]

Hi!

I am a beginner programmer. What I need to do is export a query into excel. I want the user to have the flexiblility to choose the desired fields to export. Also, I need the excel sheet to be formatted with bolded headers, calibri 10 pt font, and autofit columns. Would some help?

My code looks like this right now:

Private Sub Exportexcel_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strBrowseMsg As String
Dim strSQL As String, strTemp As String
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Const strFileName As String = "Query"
Const strQName As String = "zExportQuery"
Set dbs = CurrentDb
strSQL = "SELECT title, package, start_date, end_date, license_fee FROM Search_Query"
Set qdf = dbs.CreateQueryDef(strQName, strSQL)
qdf.Close

strBrowseMsg = "Select the folder where the new EXCEL file will be created:"
strPath = BrowseFolder(strBrowseMsg)
If strPath = "" Then
MsgBox "No folder was selected.", vbOK, "No Selection"
Exit Sub
End If
' Replace filename.xls with the real name of the EXCEL file
' that is to be created and into which the data are to be
' exported
strFile = "Query.xls"
strPathFile = strPath & "\" & strFile

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "zExportQuery", strPathFile, True

If MsgBox("Do you want to view the file?", vbYesNo) = vbYes Then
FollowHyperlink strPathFile
End If

End
dbs.QueryDefs.Delete "zExportQuery"
dbs.Close
Set dbs = Nothing
Dim ObjExcel
Set ObjExcel = CreateObject("Excel.Application")
ObjExcel.Visible = True

Set Objsheet = ObjExcel.ActiveWorkbook.Worksheets(1)

With Objsheet
.Rows("1:1").Font.Bold = True
.EntireColumn.AutoFit

End With

End Sub

 

[Uncle Gizmo]

You Said:
I want the user to have the flexiblility to choose the desired fields to export.

You should get an idea of how that can be achieved from this useful tool here:

http://www.ecademy.com/node.php?id=98809

 

[Uncle Gizmo]

I found these two knowledge base articles by Microsoft very helpful when I was the first manipulating spreadsheets from MS Access.