Error in query statement in VBA

[Mac_Wood]

Private Sub btnExportToExcel_Click()
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordset As New ADODB.Recordset
MyRecordset.ActiveConnection = cnn

Dim MySQL As String
MySQL = "SELECT XRayedConsignments.[dteEntryDateTime], XRayedConsignments.EntryTime, XRayedConsignments.AWBNumber, XRayedConsignments.Pieces, XRayedConsignments.Weight, XRayedConsignments.OpsIDNumber, XRayedConsignments.Mail, XRayedConsignments.Transhipment, XRayedConsignments.Iberia, XRayedConsignments.UnitedAirlines, XRayedConsignments.ELAL, XRayedConsignments.AirMauritus FROM XRayedConsignments"
MyRecordset.Open "Select * FROM XRayedConsignments"


                                                                         
Dim MySheetPath As String
MySheetPath = "E:\Worksheets\XRayedConsignments.xls"
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

Set Xl = NewExcel.Application
Set XlBook = Xl.Workbooks.Open(MySheetPath)



Xl.Visible = True
XlBook.Windows(1).Visible = True

Set XlSheet = XlBook.Worksheets(1)

XlSheet.Range("A4").CopyFromRecordset MyRecordset

MyRecordset.Close
Set cnn = Nothing
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
End Sub

Hope you can find something Bob

 

[Mac_Wood]

Sorry Bob forgot to mark the offending line, it is:

Set Xl = NewExcel.Application

 

[Rabbie]

Should be Set Xl = New Excel.Application

 

[boblarson]

Should be Set Xl = New Excel.Application

Yep Mac, I would have said the same thing Rabbie said. You have to make sure that you have a space between the keyword NEW and the object that is the new object.

 

[Mac_Wood]

Thanks a million guys. It is all sorted now

 

[boblarson]

Thanks a million guys. It is all sorted now

Glad to hear!