Writing Values to Cells in Excel

  • Thread starter Thread starter Keith Wilby
  • Start date Start date
K

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 said:
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.
Click to expand...
Doesn't matter, found these:

http://www.mvps.org/access/modules/mdl0035.htm
 
Keith Wilby said:
Doesn't matter, found these:

http://www.mvps.org/access/modules/mdl0035.htm
Click to expand...
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.
 
Change the fourth line to
..Range("B8").CopyFromRecordset rs

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

Barry
 
Barry Gilbert said:
Change the fourth line to
.Range("B8").CopyFromRecordset rs

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

Barry
Click to expand...

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

Keith.
 
Back
Top