• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

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
0
KarenPRuskin
Asked:
KarenPRuskin
  • 2
  • 2
1 Solution
 
GRayLCommented:
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?
0
 
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.    
0
 
Nick67Commented:
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")
oSheet.Activate
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!)
0
 
KarenPRuskinAuthor Commented:
Don't know if my last comment has been saved, but your second option was a perfect solution.  Thanks so much.
0
 
Nick67Commented:
No problem.
The Canadians are done.
Go the All-Blacks
Glad it was what you need.

Nick
Grande Prairie AB
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now