Writing Output to Excel

[SymbionA]

I want to write some data to excel worksheet using

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

However, I get a run time error 430

Class does not support automation or does not support expected interface.

Could somebody advise how to fix this?

Thanks in advance.

 

[PHV]

Without seeing more code it's hard to say something meaningful ...
How are defined/instantiated appXL and rst ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

Activesheet implies the Excel Application Object.

Use a specific WorkSheet object instead...

appXL.[b]Worksheets("YourSheetName")[/b].Range("A" & intStart).CopyFromRecordset rst

Skip,

_[red][/red]

 

[SymbionA]

OK I still get the run time error, I have detailed the code below.

Any ideas?



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 OutputFile;")

'Open the receiving book and activate the required sheet
appXL.Workbooks.Open "\\S-healthcare\commonHC\Distribution Services\National Buying\Inv_pur\Development\InvBuySuggested 2007Test.xls"
appXL.Worksheets(1).Select

'======
'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.Worksheets("Sheet1").Range("A" & intStart).CopyFromRecordset rst

 

[JerryKlmns]

Although help file states that the following should raise an error

DoCmd.TransferSpreadsheet 1, 8, "Channel", "C:\a.xls", False, "B5:AB459"

works just fine exporting to the existing worksheet named Channel starting at B5 cell. Just make sure you know exactly how many fields and records you have in your recordset!

I think works for A2k0 - A2k3

 

[SymbionA]

Thank you.

I have actually created a table in Access and want to export the table to Excel hence my code above. I googled the error and found that I should update MDAC which I have done. However, I still get the same error!

 

[SkipVought]

Dim wb As Excel.Workbook
Set wb = appXL.Workbooks.Open("\\S-healthcare\commonHC\Distribution Services\National Buying\Inv_pur\Development\InvBuySuggested 2007Test.xls")
intStart = wb.Worksheets(1).Range("A1").CurrentRegion.Rows.Count + 1
wb.Worksheets("Sheet1").Range("A" & intStart).CopyFromRecordset rst

Skip,

_[red][/red]