Writing Values to Cells in Excel

[Keith Wilby]

Does anyone have any sample code that invokes automation to write values
from an Access query to a column of cells on an *existing* Excel worksheet,
ie overwrite what's already there? I'm now aware that SP2 disables the
functionality to have a linked table from an Excel worksheet and am trying
to mitigate for this but automation is completely new to me.

Doug Steele's example is excellent but for me as a newbie it doesn't make it
clear how to loop down through a column of cells in Excel.

Many thanks.

Keith.

 

[Keith Wilby]

Does anyone have any sample code that invokes automation to write values
from an Access query to a column of cells on an *existing* Excel
worksheet, ie overwrite what's already there? I'm now aware that SP2
disables the functionality to have a linked table from an Excel worksheet
and am trying to mitigate for this but automation is completely new to me.

Doug Steele's example is excellent but for me as a newbie it doesn't make
it clear how to loop down through a column of cells in Excel.

Many thanks.

Keith.

Doesn't matter, found these:

http://www.mvps.org/access/modules/mdl0035.htm

 

[Keith Wilby]

Doesn't matter, found these:

http://www.mvps.org/access/modules/mdl0035.htm

In Sub sCopyRSExample(), how would I adapt the following code to reference,
for example, cells B8 to B330?

With objSht
.Range(.Cells(1, 1), .Cells(conMAX_ROWS, intLastCol)).ClearContents
.Range(.Cells(1, 1), .Cells(1, rs.Fields.Count)).Font.Bold = True
.Range("A2").CopyFromRecordset rs
End With

Many thanks.
Keith.

 

[Barry Gilbert]

Change the fourth line to
..Range("B8").CopyFromRecordset rs

If you have 322 rows in your recordset, it will copy down to B330.

Barry

 

[Keith Wilby]

Change the fourth line to
.Range("B8").CopyFromRecordset rs

If you have 322 rows in your recordset, it will copy down to B330.

Barry

So you only need specify a starting point? Good, thanks Barry.

Keith.