VBA code to referance range for Column

[Lavenderchan]

Hello,

I am looking to export data from an access query to an excel workbook. I can do it for cell ranges using the following code:

ExcelSheet.Range("H144").FormulaR1C1 = Me.[Parts Number]

However, I need to export data into a column range and when I tried using the code below its not working right.

ExcelSheet.Range("F17:F105").Columns.Value= Me.[Cost]

What am I doing wrong?

Thanks,

Lavender

 

[SkipVought]

Hi,

ExcelSheet.Range("F17").Value= Me.[Cost]

It will begin at F17 and fill downward.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[SkipVought]

Actually, I'd query your Access table from Excel via Data > Get external data > From Access...

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Lavenderchan]

Hi SkipVought,

Thanks for the help but code

ExcelSheet.Range("F17").Value= Me.[Cost]

is only writing the first number of the cost column in F17 and repeating it all the way down. Would a loop next funtion help with this problem?

Thanks,
Lavender

 

[DjangMan]

If you can get your data into a recordset then you can drop it into Excel with:

Range.CopyFromRecordset

Copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range.

So if your result set only has one column then your output will be one column.

e.g.

ExcelSheet.Range("F17").CopyFromRecordset YourRecordSet