How to embed existing Excel worksheet in Access form

thechrisrobertstoo
thechrisrobertstoo used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Simon BallChief information Officer

Commented:
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.
Chief information Officer
Commented:
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

Author

Commented:
Hi Sudonim

I like that, but how do I add the relevant sheet name to the SourceDoc file path?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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?
Most Valuable Expert 2012
Top Expert 2014

Commented:
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).

Author

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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
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 Liason
Most Valuable Expert 2012

Commented:
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

Author

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 Officer

Commented:
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

Author

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 Officer

Commented:
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...

Author

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.

Author

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!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial