|
Home > Archive > MS Access database support > April 2006 > Writing Values to Cells in Excel
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Writing Values to Cells in Excel
|
|
| Keith Wilby 2006-04-06, 7:41 am |
| 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 2006-04-06, 7:41 am |
| "Keith Wilby" <here@there.com> wrote in message
news:4434edf5_1@glka
s0286.greenlnk.net...
> 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 2006-04-06, 9:35 am |
| "Keith Wilby" <here@there.com> wrote in message
news:4434f1d7$1_1@gl
kas0286.greenlnk.net...
> 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 2006-04-06, 9:35 am |
| 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 2006-04-06, 9:35 am |
| "Barry Gilbert" <blgilbert@gmail.com> wrote in message
news:1144329646.084392.299630@t31g2000cwb.googlegroups.com...
> 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.
|
|
|
|
|