Export Query Data to Excel Range

[Danny_H]

Hi

Im trying to use the following code to copy the values in a Query to a cell range in Excel when a button is clilcked.

Private Sub Command120_Click()

'We'll start by creating a recordset named MyRecordset.
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordset As New ADODB.Recordset
MyRecordset.ActiveConnection = cnn

'Build the SQL Statement.
Dim MySQL As String
MySQL = "SELECT Query1.Tracker"
MySQL = MySQL & "FROM Query1"
MySQL = MySQL & "WHERE (((Query1.Tracker) Is Not Null));"
MyRecordset.Open MySQL
'Now MyRecordset contains records to be exported.

'Now for the Excel rigmarole.
'Note: You must change the path and filename below
'to an actual Excel .xlsx file on your own computer.
MySheetPath = "C:\Users\u549032.RRLOCAL\Documents\SABRe deployment governance PSSU.xlsx"

'Set up object variables to refer to Excel and objects.
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

'Open an instance of Excel, open the workbook.
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)

'Make sure everything is visible on the screen.
Xl.Visible = True
XlBook.Windows(1).Visible = True

'Define the topmost sheet in the workbook as XlSheet.
Set XlSheet = XlBook.Worksheets(2)

'Copy the recordset to worksheet starting at cell B3.
XlSheet.Range("AC45:AC98").CopyFromRecordset MyRecordset

'Clean up and end with worksheet visible on the screen.
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
End Sub

When the button is clicked I get the following

Run-time error '-2147217900 (80040e14)':

Syntax error (missing operator) in query expression
'Query1.TrackerFROM Query1WHERE (((Query1.Tracker) Is Not
Null))'.

Bit stumped why it does not work as I copied the code straight out of my Training Book?

The debug window shows where I have highlighted red as yellow background

Hope this makes sense.lol

Any help appreciated

Thanks

 

[pwbrown]

What is the actual error message?

Edit : ignore the above, somehow managed to miss where you posted the error message.

Try this anyway: MyRecordset.Open MySQL, CurrentProject.Connection

 

[Danny_H]

Hi pwbrown

I have tried that but unfortunately still get the same error message (see below)

Run-time error '-2147217900 (80040e14)':

Syntax error (missing operator) in query expression
'Query1.TrackerFROM Query1WHERE (((Query1.Tracker) Is Not
Null))'.

 

[pwbrown]

You haven't got a space before FROM and WHERE.

 

[Danny_H]

Ive added the space in front of FROM and WHERE and now have a different error message (See Below)

Run-time error '-2147217900 (80040e14)':

No value given for one or more required parameters.

 

[pwbrown]

Try this:

MySQL = "SELECT Query1.Tracker FROM Query1 WHERE (((Query1.Tracker) Is Not Null));"

I don't actually know what Query1.Tracker is or Query1, this is not how I would get values from a query. Did the book show this?
Maybe someone else here can help you.

 

[Danny_H]

Yes, All code was copied exactly from my Book?

Ive copied your text and still gives the same error?

Query1 just contains 1 column, with 54 records that are single numbers ie, line = 1, line = 3 etc.

I just want to copy these numbers into a range on the Excel worksheet.

Is there another easy way I could this?

If i just want to copy a table it works fine, could I copy a single column from a table?

 

[pwbrown]

I would do something simple like:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", 
"C:\Users\u549032.RRLOCAL\Documents\SABRe deployment governance PSSU.xlsx", True

This would replace all the code you have there!

It depends what you want to do, this may not be what you need.

 

[Alansidman]

There is an alternative method to doing this if you have created your query in the QBE. You can use the transferspreadsheet method.

Read here to get started: http://accessblog.net/2006/07/export-to-excel-range.html

If you are unfamiliar with transferspreadsheet, then look here

http://msdn.microsoft.com/en-us/library/office/bb214134(v=office.12).aspx

 

[aarif786000]

Just use single cell while selecting range with excel application also use space between "Select * from query1.tracker ............ ".