Access to Excel Automation Runtime Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
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.
 
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]"
 
Back
Top