Create excel sheet 2

[LARiot]

Hi,

Does anyone have a simple reference or code sample to write data to an Excel sheet from Access VBA?

Basically to create a new excel file, write to it's cells then close. The threads I found here did strange stuff and didn't work.

Thanks.

-Neema

 

[Remou]

Why not TransferSpreadsheet?

 

[LARiot]

'Cause it wouldn't work. A lot of literals and the data doesn't fit into a regular recordset format. That is, the fields do not go horizontally across the top. Ten rows constitute one record.

-Neema

 

[Remou]

So do you want to use automation?

Dim xl As Excel.Application
Dim xwb As Excel.Workbook
Dim xws As Excel.Worksheet

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("tbl")
Set xl = CreateObject("Excel.Application")
Set xwb = xl.Workbooks.Add

Set xws = xwb.Worksheets.Add

xws.Range("A1") = rs.Fields(1)

xl.Visible = True
xl.UserControl = True

 

[LARiot]

Thanks Remou. Do I have to create any references? It says User-Defined type no defined off the first line.


-Neema

 

[Remou]

Yes, you will need a reference to the Microsoft Excel x.x Object Library.

 

[LARiot]

OK so it was the Microsoft Excel 11.0 Object Library.

What's automation? Sounds like an 80's dance song.

-Neema

 

[Remou]

la, de dum, la yeah ...

http://en.wikipedia.org/wiki/OLE_Automation

 

[LARiot]

Hey we could start an 80s cover band. What do you think Remou? You in?

-Neema

 

[LARiot]

I am having another slight problem.

When I write:

xws.Range(sLetter & sNumber) = "94000.1170"

It drops the zero at the far right. It's treating it as a number instead of a string expression. It's in quotes and I tried cstr("94000.1170") I tried putting it into a string... any ideas?

Thanks.

-Neema

 

[Remou]

You need a single quote:

xws.Range(sLetter & sNumber) = "[COLOR=red yellow]'[/color]94000.1170"

 

[LARiot]

Wow Remou, is there anything you can't do?

-Neema