Export a RecordSet to Excel

[TinkerMan]

Hi all

I have searched for this particular problem, but no solutions found so far. Forgive me if I have missed it. If I have, just point me in the right direction...

I have a query I need to run in VBA (DAO) as dnamic and it needs parameters. Also, if the query is run another way, the user will be prompted for that parameter (and that is not desireable).

As far as I can see, outputTo, transferSpreadsheet, etc. all work on stored tables/queries. Is there a way to export a DAO.RecordSet?

Suggestions welcome. I do not mind a temporary text file (csv or similar).

Is it easy to save a recordSet to a new temporary table? that will allow using standard export functions....

By the way, is there a good way of launching excel without specifying the path to it, and still be able to pass arguments?

(I'm using office 2K)

Thanks

 

[allan57]

I use the following method:

Dim dbs As DAO.Database

Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object

Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkBook

objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "EnterSheetNameHere"

Set rstGetRecordSet = dbs.OpenRecordset("PlaceRecordSetQueryHere;")

objActiveWkb.Worksheets("EnterSheetNameHere").Cells(1, 1).CopyFromRecordset rstGetRecordSet

objActiveWkb.Worksheets(1).SaveAs FileName:="EnterFileNameHere.xls"
objActiveWkb.Close

Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close
dbs.Close
Set rstGetRecordSet = Nothing
Set dbs = Nothing

 

[TinkerMan]

Excellent !!

Thanks allan57

This looks very promising. I will test it later today

As a short follow-up question: How would you go about it if you wanted to use a template in excel, such that the formatting would be a bit nicer?

Thanks

 

[allan57]

I tend to do the formatting directly from Access, below is some examples of formating commands I use:

objActiveWkb.Worksheets("EnterSheetNameHere").Cells(1, 1).Font.Bold = True
objActiveWkb.Worksheets("EnterSheetNameHere").Cells(1, 1).Interior.ColorIndex = 1
objActiveWkb.Worksheets("EnterSheetNameHere").Cells(1, 1).Interior.Pattern = xlSolid
objActiveWkb.Worksheets("EnterSheetNameHere").Cells(1, 1).Font.ColorIndex = 2
objActiveWkb.Worksheets("EnterSheetNameHere").Cells(1, 1).HorizontalAlignment = xlRight
objActiveWkb.Worksheets("EnterSheetNameHere").Cells(1, 1).NumberFormat = "0.000"
objActiveWkb.Worksheets("EnterSheetNameHere").Columns.AutoFit
objActiveWkb.Worksheets("EnterSheetNameHere").Cells(1, 1).Font.Name = "Arial"
objActiveWkb.Worksheets("EnterSheetNameHere").Cells(1, 1).Font.Size = 14

 

[TinkerMan]

Just what I needed

Thanks again

This is exactly what I need.

Many thanks for your help

TinkerMan