Access to Excel (Q for the Gurus) 1

[LonnieJohnson]

How do I take the value of one field of one row of a record source and place it in a particular cell in an existing excel spreadsheet?

I am assuming on the Access side I can just create a recordset and use the record ID to get the row and the
rs("FieldName") to get the value I want. How do I plug this into a particular cell.

(part two of the question) If I had 200 such values that have to go into particular cells, then is there an easy way?

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!

 

[PHV]

then is there an easy way?
Yes, simply play with OLE Automation.
The starting point is the GetObject function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Lightning]

Lonnie

If you have access to a copy of the Access Developers Handbook 2002 (desktop volume) then there is an example of how to do this. See Chapter 12 - Using Access as an Automation client. There is an example routine of populating an excel workbook on page 842.

HTH
Lightning

 

[jebry]

Hi Lonnie!

If there is a consistant way to determine which column in Excel gets what piece of data and if each row in Excel will represent a different record in the recordset then you can do this in a loop. Just let your loop counter serve as the row number like this:

For LoopCounter = 1 To rs.RecordCount
With ExcelSheet
.Range("A" & Format(LoopCounter)).FormulaR1C1 = rs!Field1
etc.
End With
Next LoopCounter

You can even move from sheet to sheet in your workbook using a loop:

For SheetCounter = To MaxSheets
ExcelSheet = ExcelWorkBook.Worksheets("Sheet" & Format(SheetCounter))
Go on with your code.
Next SheetCounter

Of course you would nest your loops to make this work.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[LonnieJohnson]

Thanks Lightning,

That did the trick. Actually I have the Office XP Developer's guide. We bought it a while back to make runtime versions of apps and never got around to it. I forgot we had it. Thanks again.

Thanks jebry,

Your ideas was great too. Unfortunately, management structured the layout of the spreadsheet that it will not allow a variable recordset. I have to do dlookups and paste into the ObjectCells according to a mapping scheme. But I will keep it in mind.

ProDev, MS Access Applications
Visit me at ==>

http://www.prodev.us

May God bless you beyond your imagination!!!