Displaying a Worksheet in an ACCESS control

I need to provide a toggle button that displays a specific worksheet when selected.  See attached document for clarification.  Thanks in advance you geniuses.  Or is that geni?? SpreadsheetControlRequirement.doc
Who is Participating?
Nick67Connect With a Mentor Commented:
Hi Karen,

Do you want them to see a control with an accurate representation of a specific sheet from a workbook on it (moderately hard)?
Or do you want Full blown Excel to open to a specific sheet (easy)?
Or both?

Getting a control to dynamically show what's on a specific sheet is hard.
I'd probably kludge that through Excel automation.
Have placeholder Excel workbook with no sheets in it.
Throw an unbound object frame, linked not embedded, on the form--pointed at the placeholder.
Properties Enabled = Yes, Locked = No
Then I'd use Excel automation to copy the desired sheet to the placeholder, and then update and reveal the unbound object frame.
They aren't pretty though.

Just opening the workbook to the correct sheet is a straight-forward bit of Excel Automation

Dim oApp As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet  
Set oBook = oApp.Workbooks.Open("SomePath\Transfieldtransquiptool.xls", , True)
Set oSheet = oBook.Worksheets("SomeSheetName")
oApp.Visible = True
oApp.UserControl = True

You need to add a reference to Microsoft Excel xx.0 Object library to make it go
XX being 11 for Office 2003, 12 for 2007 and 14 for 2010

(yes they skipped 13!)
That should be geniuses or genii ;-)

If you put the path and file name of the spreadsheet into a table field with a datatype Hyperlink, when you doubleclick on the field with the table in datasheet view, you will open the spreadsheet in it's native Excel application - is this what you want?
KarenPRuskinAuthor Commented:
I would like them to just see the specific WORKSHEET (in my document example that would be 341) within the spreadsheet that is named the same as the plant number.  I think that your suggestion would open the whole spreadsheet and then they'd need to find the correct worksheet. - there will be possibly hundreds of the little suckers.    
KarenPRuskinAuthor Commented:
Don't know if my last comment has been saved, but your second option was a perfect solution.  Thanks so much.
No problem.
The Canadians are done.
Go the All-Blacks
Glad it was what you need.

Grande Prairie AB
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.