Displaying a Worksheet in an ACCESS control

Posted on 2011-10-12
Last Modified: 2012-08-14
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
Question by:KarenPRuskin
    LVL 44

    Expert Comment

    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?

    Author Comment

    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.    
    LVL 26

    Accepted Solution

    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!)

    Author Closing Comment

    Don't know if my last comment has been saved, but your second option was a perfect solution.  Thanks so much.
    LVL 26

    Expert Comment

    No problem.
    The Canadians are done.
    Go the All-Blacks
    Glad it was what you need.

    Grande Prairie AB

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now