Transfer the data from access table to excelsheet

[aman]

Hi guys

I am writing the following code to export data from access table to excelsheet. But it doesn't do anything. If I tru transferspreadheet command then again it again doesn't display anything.

Private Sub Command41_Click()
strsql = "SELECT P2PRequestID,CustomerIntials as [Customer Firstname],CustomerSurName,DocumentID,DocumentDescription,RequesteeName,RequesteeDept,RequestedForName,RequestedForDept from tblDocumentRequest where RequestStartDateTime = #" & Format(Me.DTPickerFrom.Value, "dd/mm/yy") & "# order by P2PRequestID"
DoCmd.RunSQL strsql
If DCount("Name", "MSysobjects", "Name='qrytemp' and type=5") > 0 Then
DoCmd.DeleteObject acQuery, "qrytemp"
End If
Set qdf = CurrentDb.CreateQueryDef("qrytemp", strsql)
'need to add a reference to Microsoft Excel 11.0 (or the version you have) Object Library
    Dim objXls As Excel.Application
    Dim objWrkBk As Excel.Workbook
    Dim xprtFile As String
   'i = "select referenceid from completed_table where location='PWR' "
   
   xprtFile = "J:\WilliamsLea-AIMM\Unit Rate MI\Mailroom Audits\test1\Deliv.xls"
    DoCmd.OutputTo acOutputQuery, "qrytemp", acFormatXLS, xprtFile, False
     Set objXls = New Excel.Application
     objXls.Visible = False
     Const xlLandscape = 2
     Set objWrkBk = objXls.Workbooks.Open(xprtFile)
     objWrkBk.Sheets("qrytemp").Select
     Dim J As Integer
     J = objWrkBk.ActiveSheet.UsedRange.Rows.Count
      With objWrkBk.Sheets("qrytemp")
           .Application.Rows("1:1").Select
        .Application.Selection.Font.Bold = True
        End With
msgbox "The data has been exported"
End Sub

Thanks

 

[DCrake]

Your first error is that you cannot do DoCmd.RunSQL on a select query. Only action queries can use this command.