Access to Excel Automation Runtime Error

[Guest]

I am trying to learn the basics of Access for VBA but so far it is kicking my
butt.

I am trying to export a table to specific ranges in the excel spreadsheet.
Every time I try and run the code I get an error at "myrecordset.Open mysql"
stating that a variable is missing.

Below is the code I have so far, if you could please look at it and tell me
where I am going wrong I would greatly appreciate it.

Public Sub createexcel()

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim myrecordset As New ADODB.Recordset
myrecordset.ActiveConnection = cnn

Dim mysql As String

mysql = " SELECT QryMainTbl.Loc, tblmain.item"

myrecordset.Open mysql

Dim mysheetpath As String

mysheetpath = "C:\TransferStation\MarketIntelligence.xls"

Dim xl As Excel.Application
Dim xlbook As Excel.workbook
Dim xlsheet As Excel.worksheet

Set xl = CreateObject("excel.application")
Set xlbook = GetObject(mysheetpath)

xlbook.Windows(1).Visible = True

Set xlsheet = xlbook.worksheets(1)

xlsheet.range("a3").CopyFromRecordset myrecordset

myrecordset.Close

xlbook.SaveAs ("C:\TransferStation\MarketIntelligence2.xls")

xlsheet.Application.Quit

Set xl = Nothing
Set xlbook = Nothing
Set xlsheet = Nothing

End Sub

 

[Perry]

myrecordset.Open mysql, cnn

Connection as second parameter to yr .Open()
While there, look at the other (optional) parameters to ADO recordset Open()
command

Krgrds,
Perry

 

[Ed Adamthwaite]

Gidday Rob,
it looks like your SQL for the recordset is wrong. Where is the "FROM"
clause?
Try pasting your SQL into the SQL view of a query and check that it works.
HTH,
Ed.

 

[Guest]

You were right it was my sql that was stopping the code. The only way I was
able to clear it up was deleteing it all together and replacing it with

myRecordSet.Open "[Tblmaintbl]"