How do I gain control of an Excel Spreadsheet embedded in an Access form?

Colleyville Alan

I have been working on an application that queries data from Access, loads
it into an array, and then writes it to an Excel spreadsheet. I use the
array approach to have fine control over spacing, formatting, etc. I have
gotten the first part of this to work by opening an Excel spreadsheet,
writhing the data, saving the spreadsheet and closing it. But what I would
like to do is to not actually open up Excel outside of Access. I want to
write the data to a spreadsheet embedded on the main form.

When the form runs, I can click on the spreadsheet and manually enter data,
but I cannot figure out what VBA commands to use to grab hold of the
spreadsheet. Can anyone point me in the right direction? Thanks.

Nov 12 '05 #1

Rich P

Use an unbound Frame control from the toolbox in a Form's design view.
You can embed an Excel spreadsheet in this control. Just follow the
wizard.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #2

Colleyville Alan

"Rich P" <rp*****@aol.com> wrote in message
news:3f***********************@news.frii.net...

Use an unbound Frame control from the toolbox in a Form's design view.
You can embed an Excel spreadsheet in this control. Just follow the
wizard.

Thanks. I have done that, but when I want to write the information to the
Excel spreadsheet, how do I do that? In regual Excel, I declare an object
variable and instantiate it. Then I query the Access database, load the
recordset into an array of customtype, and write the data to individual
cells in the ActiveWorksheet. For example:

For iCtr = 1 To UBound(FundPerfArray)
oXLWKS.Range("B" & N) = FundPerfArray(iCtr).strFund_Name
oXLWKS.Range("C" & N) = FundPerfArray(iCtr).str_Category
oXLWKS.Range("D" & N) = FundPerfArray(iCtr).strTicker
...
Next iCtr

This code writes to cells in the ActiveWorksheet. I want to do the same
thing with the spreadsheet embedded in the form so that the user has a
preview of what the final output will be and also will have the opportunity
to modify it. But I cannot seem to find how to access the Range property of
the embedded spreadsheet. The object inspector only shows properties such
as boderstyle, height, width, etc. Can't you use VBA to write to this
embedded sheet as opposed to only using it in an interactive fashion?

Nov 12 '05 #3