Export DAO recordset to an Excel file? 1

[MrPeds]

Hi,

I am writing a VBA Access 97 module that carries out a simple query on an acces table.

I would like it to be able to use a DAO recordset to hold the result of the query, and then export the result to an Excel 97 spreadsheet.

I know it is possible to export from an Access table to Excel, just wondered if it is possible to do this from a recordset? or do I need to insert into a table first?

Thanks,

MrPeds

 

[assets]

I do this all the time use the iffice link on the tool bar and it will export in to excel. you will then have to cleanup any unwanted fields/format.

 

[EliseFreedman]

If you want to have more control over your export than the office links button offers, try the following.

Open up Excel, place a button on the worksheet and attach the following code to it

Dim daoapp As dao.DBEngine
Dim dbsales As dao.Database
Dim rssales As dao.Recordset

Dim i As Integer
Dim wks As Worksheet
Dim icount As Integer

Set daoapp = New dao.DBEngine
Set dbsales = dapenDatabase("Your Database Name")
Set rssales = dbsales.OpenRecordset("Your Recordset Name")
Set wks = Worksheets.Add
icount = rssales.Fields.Count
For i = 0 To icount - 1
wks.Cells(1, i + 1).Value = rssales.Fields(i).Name
Next

wks.Range("A2").CopyFromRecordset rssales


You can then add code to format the spreadsheet. For example to alter the size of a column to fit the selection, use the following

With ActiveSheet.Range("a1..FE1").Resize(1, icount)
.EntireColumn.AutoFit
End With

A good way to learn Excel VBA is to record a macro to perform the required task and then have a look at the code

Hope this helps

Elise

 

[MrPeds]

thanks to you both for your help.

I think the 2nd suggestion is more useful in as much as it is more automated, and i am doing as much as i can in code.

MrPeds

 

[SkyHigh]

Hi
I am using the above example to create some reports in excel where I need to copy multiple recordset to the same worksheet so my question is how I would know what is the next cell range available after writing the first record set to the worksheet thanks :

Dim daoapp As dao.DBEngine
Dim dbsales As dao.Database
Dim rssales As dao.Recordset

Dim i As Integer
Dim wks As Worksheet
Dim icount As Integer

Set daoapp = New dao.DBEngine
Set dbsales = dapenDatabase("Your Database Name")
Set rssales = dbsales.OpenRecordset("Your Recordset Name")
Set wks = Worksheets.Add
icount = rssales.Fields.Count
For i = 0 To icount - 1
wks.Cells(1, i + 1).Value = rssales.Fields(i).Name
Next

wks.Range("A2").CopyFromRecordset rssales

.......
Now what is the next active cell available to write next recordset/information etc
......

 

[PHV]

Replace this:
wks.Range("A2").CopyFromRecordset rssales
by this:
Dim nbRows As Long
nbRows = wks.Range("A2").CopyFromRecordset(rssales)
So, the next available excel row is nbRows + 2

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkyHigh]

Great that worked fine for me, here's another question, I am trying to write all customers reports in one workbook but in separate worksheets for each customer, the following code as is obvious keeps overwriting reports in worksheet(1), how can I change it to write next customer's report to a new worksheet - thanks

Set RsID = db.OpenRecordset("SELECT DISTINCT tblTransactions.Regid AS Regid FROM tblTransactions", dbOpenDynaset, dbReadOnly)
Do Until RsID.EOF
Set WS = WB.Worksheets(1)
Sheets(1).Select
Sheets(1).Name = RsID!regid
WS.Activate
......
.......
RsID.MoveNext
Loop

 

[PHV]

Use the Add method of the Worksheets collection.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884