Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA code to referance range for Column

Status
Not open for further replies.

Lavenderchan

Technical User
Jul 21, 2008
132
US
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:

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.

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

What am I doing wrong?

Thanks,

Lavender
 
Hi,

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

It will begin at F17 and fill downward.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein
 
Actually, I'd query your Access table from Excel via Data > Get external data > From Access...

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

Thanks for the help but code
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
 
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.
Code:
ExcelSheet.Range("F17").CopyFromRecordset YourRecordSet
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top