How to embed existing Excel worksheet in Access form

Hello

I have an Excel workbook that imports data from an Access database to a specific worksheet.  On another worksheet (or 4) I have a number of tables summarising the company data using quite complicated formulae.

My customer would now like that summary analysis to also be available from within the Access database front end.  In order to avoid the need to maintain two independent methods of performing the same analysis, I would like to simply embed the summary analysis worksheet in an Access form.

Please can you tell me if this is possible?

Many thanks.
thechrisrobertstooAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Simon BallChief information OfficerCommented:
I've not done it personally,

but i have seen it done years ago but i didn't know how.

google reveals it was probably done using an object frame with path pointing at excel..not sure how it would work with multiple worksheets..

http://stackoverflow.com/questions/2541665/ms-access-2003-embedded-excel-spreadsheet-on-access-form

I am not sure that its going to work for you though, as that seems to be for using excel as a datasource, and from your question you seem to be using it more as a bespoke report for summarising the core data.

Might be better to make a report in access which provides the same data as the excel calculation...even though that will be annoying.
Simon BallChief information OfficerCommented:
try this one:

add a Bound object frame to form called OLE1

Sub Command1_Click
    OLE1.Class = "Excel.Sheet"    ' Set class name.
    ' Specify type of object.
    OLE1.OLETypeAllowed = acOLELinked
    ' Specify source file.
    OLE1.SourceDoc = "C:\Excel\Oletext.xls"
    ' Specify data to create link to.
    OLE1.SourceItem = "R1C1:R5C5"
    ' Create linked object.
    OLE1.Action = acOLECreateLink
    ' Adjust control size.
    OLE1.SizeMode = acOLESizeZoom
End Sub 

Open in new window


from post #11
http://www.vbforums.com/showthread.php?t=382095

Amend it to your sourcedoc...sourceitem might need full address, e.g. worksheet name and range

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
thechrisrobertstooAuthor Commented:
Hi Sudonim

I like that, but how do I add the relevant sheet name to the SourceDoc file path?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

thechrisrobertstooAuthor Commented:
Another point, in the absence of scrollbars I can dynamically change the range to be displayed from the analysis sheet by allowing the user to choose the type of analysis they want to view from my summary sheet, but if I want to avoid pre-defining those ranges within the database (as they'll be subject to change if the analysis summary sheet is amended) can I also use range names in the SourceItem property?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you storing the "relevant sheet name" somewhere, or is this a fixed value (i.e. you always want to open C:\SomeFolder\MyWorkbook.xls).
thechrisrobertstooAuthor Commented:
The file is fixed - it's always in the same place and always has the same name.  It also has multiple sheets, and I want to be able to specify which sheet to view as it isn't the first sheet in the workbook.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I don't think you can specify the worksheet, only the workbook. I could be wrong, since I don't use OLE objects in Access (they tend to cause bloat and corruption).

Perhaps someone else will weigh in on this.
Jeffrey CoachmanMIS LiasonCommented:
perhaps I am missing something...?

What the reason why this needs to "touch" Excel at all?

There is no indication, that all this "summarizing" cant be done in Access.

So instead of Exporting the Access data to Excel, then trying to import/link/embed the Excel data (which contains the same Access data) back into Access???, ..why not do it all in Access?

Can you post an example of what you are doing in Excel that is impossible to do in Access...?

JeffCoachman
thechrisrobertstooAuthor Commented:
I've not said that it can't be done in Access.  The customer wants the analysis in Excel because:

a) They're used to it;
b) They can easily copy and paste information from Excel to use elsewhere

Now they are asking that this analysis be visible in their database as well as still being available in their spreadsheet.
Simon BallChief information OfficerCommented:
hmm,

the example i found did not mention sheet name, but a range can be defined including sheet name, as i specified in my earlier comment.

it would be better i think for you to say "no" to the customer, and give them a button in access which opens the excel sheet.

http://www.techrepublic.com/article/create-a-command-button-to-open-an-excel-spreadsheet-from-an-access-form/6025059
thechrisrobertstooAuthor Commented:
Maybe you're right.  It does accept range names, but not if they aren't on the first sheet of the workbook.  The customer already has a button to open the excel file so I'm thinking my only option would be to use additional Excel files that refer to my analysis summary.

I like the method you suggested above as a general tool so although my issue is not completely solved it may be that it's unsolvable.  In which case, I guess I just award you all the points!
Simon BallChief information OfficerCommented:
bit of research leads to another experts exchange page:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23119345.html


OLE1.SourceItem = "Sheet2!R1C1:R5C5"

caveat: SourceItem string cannot exceed 50 characters...
thechrisrobertstooAuthor Commented:
Well, I tried that, and got a Run-time error '2785', The OLE server wasn't able to open the object.

If it's on the first sheet I don't get the error, even if it's a named range.
thechrisrobertstooAuthor Commented:
Sorry for the delay in awarding points.  I like this solution, although it seems it isn't possible to do exactly what I wanted.  Thanks for your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.