Export Access Query To Excel

Folks,

I have a form with combo box (cboProjForRptSeltn ) and two command buttons. The combo box is filled with name of the reports. The combo box has two columns, column 1 is the name of the report and column 2 is the bound column which also is the link field (primary key) to limit the records.

When the user selects an item from cbo box and click the "Preview Risk Table" command button it will open a report in preview mode. The report's "Record Source" property is set to a UNION query (qryRprtRskTbl) which pull the records from several tables. The report's "Filter" property is set to the following criteria:
“qryRprtRskTbl.P.intProjectId=Forms!frmReportSelec tionBlrR1!cboProjForRptSeltn”

The above criteria is nothing but filter based on the value of the cbo box.

The click event procedure is as follows:
Private Sub cmdPreviewRprt_Click()
Dim strDocName As String
strDocName = "rptRskTblProjectWise"
DoCmd.OpenReport strDocName, acPreview, "qryRprtRskTblFilter_r1"
End Sub
"qryRprtRskTblFilter_r1" is another query out of the UNION query I mentioned above (qryRprtRskTbl). qryRprtRskTblFilter_r1 is pulling all the records from qryRprtRskTbl which meets the projectID field selected in cbo box, which is also the same as the "Filter" property value of the report as indicated above.

Everything works fine with cbo box and "Preview Risk Table" command button. It just pull all the records for the project selected under cbo box and display it as report in preview mode. Looks great!!

Here's my problem. I wanted to export the same report that was previewed by the user to Excel. For this I am using, another command button called “Export to Excel”. The click event of this procedure is as shown below:

Private Sub cmdExportToExcel_Click()
On Error Resume Next
Dim xlApp As Excel.Application
Dim xlSheet As Excel.Worksheet
Dim xlWorkbook As Excel.Workbook

Dim acQuery As QueryDef
Dim objRST As Recordset
Dim strQueryName As String
Dim strSearch As String
'Dim strSQL As String

'strSearch = Me![cboProjForRptSeltn]

strQueryName = "qryRprtRskTblFilter_r1"
'strQueryName = "qryEffcyAllProjtsForRprt"

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWorkbook = xlApp.Workbooks.Add
Set objRST = Application.CurrentDb.OpenRecordset(strQueryName)


'Loop through the fileds collection and make each field name a column heading in Excel
Set xlSheet = xlWorkbook.Sheets(1)
For lvlColumn = 0 To objRST.Fields.Count - 1
xlSheet.Cells(1, lvlColumn + 1).Value = objRST.Fields(lvlColumn).Name
Next
'Change the font to bold for header row
xlSheet.Range(xlSheet.Cells(1, 1), xlSheet.Cells(1, objRST.Fields.Count)).Font.Bold = True

' I have some codes here for formatting Excel cells …

'Send data from Recordset out to Excel
With xlSheet
.Range("A2").CopyFromRecordset objRST
.Name = Left(strQueryName, 31)
End With

Set xlSheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing

End Sub

When I click the command button, it loads an instance of Excel and adds a workbook under the query name "qryRprtRskTblFilter_r1", but no data.

If I use another query say, "qryEffcyAllProjtsForRprt", which has no connection to cbo box value then it is cool, exports all the data to Excel without any problem.

I guess, you folks understand what I am trying to achieve here. Basically, I wanted to give the user some flexibility, either they can view the data as Access report or Export to Excel with same formatting feature and add more later if they want after exporting, as they see on Access report preview.

I have attached some of the query files I have described here. May be I can clarify more down the road, if necessary.

My bottom line question is: why the “qryRprtRskTblFilter_r1” query runs perfectly on Access reports but not when I want to run to export to Excel?

I don’t want to use the TransferSpreadsheet or outputTo method of docmd object, because I wanted to do some formatting before I export to excel.

Any help is greatly appreciated.

Thanks

ShanVel