Exporting a query to a specified cell range in excell

[OPMCoordinator]

Hello all!

I am a complete newbie to VB and would appreciate some help. I have my first proper training this friday but need to square this one away asap.

If I have a database in the root directory db1.mdb that has a table called table1 that has a field aaa (data=a,b,c,d,e,f...) and I use query1 to assemble a record set that I want to copy (automatically) to c:\1.xls sheet1 starting at a1, what is the code. Surely it must be possible to write the code simply?!?!
(vb ver 6, access 2000)
nb i dont think transfer spreadsheet works as it overwrites the sheet (which i dont want to do) and send object also overwrites more than just cell range. I think it is the copyrecordset command that i need to use but the dims, sets, call, public subs, x as x, etc is what is stumping me (mostly).

Cheers
Mike C

 

[crosmill]

You can use the Transer Spreadsheet function in a macro and specify the range there.

HTH

 

[OPMCoordinator]

Automatically copy a query recordset to a specific range in excell

Nathan and Mario were both very helpful in giving me instruction and code to hlep solve my problem. This code is the result and exact solution I wanted.
References to excell (latest) and DAO (latest) were needed.

*******************************************

Sub yourname()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim dbs As DAO.database
Dim rst As DAO.recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("query1", dbOpenDynaset) 'name of your query = query1
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\Book1.xls") 'location and name of your file = C:\Book1.xls

xlApp.Windows(1).Visible = True
xlBook.Sheets(1).Range("A1").CopyFromRecordset rst 'sheet 1, starting at a1 - copies down and accross to fit record set
xlBook.Close savechanges:=True
xlApp.Quit

Set rst = Nothing
Set dbs = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

End Sub

*****************************

Thanks heaps guys for helping out!

Mike C