Export Access Data to an Excel Template 1

[rchandr3]

Access Experts!

I want to export data from Access 2000 to Excel 2000. Presently I use Transferspreadsheet, which creates or replaces Excel file with no formatting. I plan to have a formatted Excel Template with data getting there with no manual intervention. Now we have to do formatting after the Access export to Excel. Also, is there a way to append data in Excel? Please guide me.



Cheers,

Ravi

 

[idbr]

Hi Ravi,

You can use the CopyFromRecordset method to append or output data:

Sub output()

Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Dim intStart As Integer

Dim appXL As Excel.Application


Set dbs = CurrentDb


Set appXL = New Excel.Application

'Select the data you want to output
Set rst = dbs.OpenRecordset("SELECT * FROM tblLists;")

'Open the receiving book and activate the required sheet
appXL.Workbooks.Open "C:\Test.xls"
appXL.Worksheets(1).Select

'=============
'Use either/or
'=============

'======
'Append

'Find the current number of rows, will start from row 2 on a blank sheet though
intStart = appXL.ActiveSheet.Range("A1").CurrentRegion.Rows.Count + 1


appXL.ActiveSheet.Range("A" & intStart).CopyFromRecordset rst
'=====================================================================


'=========
'Overwrite

appXL.Cells.Select
appXL.Selection.Clear

appXL.ActiveSheet.Range("A1").CopyFromRecordset rst
'===============================================

appXL.ActiveWorkbook.Save
appXL.Workbooks.Close

appXL.Quit
Set appXL = Nothing


End Sub

This should help, you might want to enumerate the recordset field names to a header row too - it's covered in the help topic.

Cheers, Iain

 

[idbr]

Oops:

appXL.Selection.Clear

Should read

appXL.Selection.ClearContents

to preserve the formatting...

 

[rchandr3]

Thanks a lot, idbr!

Cheers,

Ravi

 

[SalGal]

Hello all -

I have a similar situation - I've copied the code above, but when I try to compile, I get "Compile error: User-defined type not defined" on the
Dim appXL as Excel.Application statement. From digging around, I believe I need to somehow define the appXL variable. When I change it to appAccess, I get a list of choices, but not with the appXL.

How do I go about adding this? I have the reference to Microsoft DAO 3.6 Object Library selected, but that doesn't seem to fix the problem.

I'll be the first to admit that I DO NOT KNOW MUCH ABOUT CODING, but am gamely trying to learn on my own.

Many Thanks,

 

[PHV]

You have to reference Microsoft Excel

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

 

[SalGal]

I understand that I need to reference it - but I don't know where or how to reference Excel.

Again, sorry that this is so elementary. I appreciate the feedback.

Thanks.

 

[PHV]

menu Tools -> References ...

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

 

[SalGal]

Duh. . .

Thanks a bunch - compiled and is working.