Output to excel ==> replace worksheet

[shiro]

Usually out to excel will replace a workbook.Can we make it
to replace worksheet only.Thank's

 

[Arvin Meyer [MVP]]

Usually out to excel will replace a workbook.Can we make it
to replace worksheet only.Thank's

How about, just replacing the contents of a cell, or cells?

Dim appXL As Object
Dim wkb As Object
Dim wks As Object

Set appXL = CreateObject("Excel.Application")
Set wkb = appXL.Workbooks.Open("C:\MyFolder\MyWorkBook.xls")
Set wks = wkb.Worksheets(1)

wks.Cells(1, 1) = "Hello World"
wks.Cells(1, 2) = "Good-bye now"

 

[shiro]

I'm sorry Mr Arvin,
I haven't test your code,but I want to ask more question.
If I outputting a continuous form,and if there are some records
in it,does this code will insert all records of a field into one cell?
And then,how to cahnge the value of the cell with the value of my
query's fields or my form's fields.
Thank's for help

 

[Arvin Meyer [MVP]]

Instead of inserting values cell by cell, you can use Excel ranges to insert
records row by row. Here's some code that will do that:

http://www.mvps.org/access/modules/mdl0035.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[shiro]

I've read the code written by Mr Dev on thesite but that is not excatly what
I
want.Here is my condition:
Some data that I want to output to are on the form Header,and the others
are on the detail section of my continuous form.Below is where I've got so
far

Dim appXL As Object
Dim wkb As Object
Dim wks As Object

Set appXL = CreateObject("Excel.Application")
Set wkb = appXL.Workbooks.Open("C:\MyFolder\My Workbook.xls")
Set wks = wkb.Worksheets(1)
appXL.Visible = True

wks.Cells(4, 3) = [Customer]
wks.Cells(6, 3) = [LotNo]
wks.Cells(7, 3) = [Model]

Field 'Customer' and 'LotNo' are on the form header and 'Model' is on
the detail form.And I always limitting the number of record returned to
5 record only that mean excel cell (7, 3) until cell (11, 3) should contains
the 5 values of the 'Model'.How to do that.?

 

[Arvin Meyer [MVP]]

Field 'Customer' and 'LotNo' are on the form header and 'Model' is on
the detail form.And I always limitting the number of record returned to
5 record only that mean excel cell (7, 3) until cell (11, 3) should
contains
the 5 values of the 'Model'.How to do that.?

You must build a recordset or a query and either export the query with the
TransferSpreadsheet function (look it up in help)

Or loop through a recordset, writing the data to the Excel sheet one cell at
a time.