By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,466 Members | 1,309 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,466 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
"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

This discussion thread is closed

Replies have been disabled for this discussion.