none
Display and populate an Excel Template on an Access form RRS feed

  • Question

  • Hi,

    My company has many resources working on many projects.  I want to show (as a proof of concept) the value of having a resource tracker, and simultanelously moving away from creating hundreds of spreadsheets.  Simplest way of doing this (as I don't know SharePoint, Excel Services, PerformancePoint Services, Project Server etc. well enough) is to load some data into Acesss, and then demonstrate the dashboards of resource trackers/charts etc.

    In essence what I want is an access form that displays frozen headings (multiple rows of headings) and a frozen left pane of essential info, with the multiple columns of tracking info scrollable.  Took me ten minutes to knock up as an excel template.  But I don't want a separate excel file for each project (moving away from heaps of static documents to a MIS is the whole point).  Redesigning headings, frozen panes etc. in an access form is probable possible but a real pain in the neck.  There really ought to be a simple way of using the one Excel template displayed on the form which is, at run time, populated with the data from the tables, according to whichever project is selected (master/child combo-the easy bit).

    I can't make this work.  Any ideas?  I can email the example Excel tracker I put together to show what I want to get to-can't find a way of attaching it to this post either!


    • Edited by MikeMay Wednesday, March 29, 2017 1:52 PM
    Wednesday, March 29, 2017 1:51 PM

All replies

  • Hi Mike,

    Not sure I see the point of displaying Excel within Access. Are you saying the data is stored in Access and you just want to display them in Excel?

    Wednesday, March 29, 2017 2:35 PM
  • Hi,

    I think the 2nd paragraph of my post explains that.  I want freezable panes and scrollable areas - nigh on impossible within access.  So it would just be easier to display an excel worksheet and populate it with the data, which is in the db.

    Wednesday, March 29, 2017 2:39 PM
  • Hi,

    I think the 2nd paragraph of my post explains that.  I want freezable panes and scrollable areas - nigh on impossible within access.  So it would just be easier to display an excel worksheet and populate it with the data, which is in the db.

    Okay, if the formatted display is Excel, why not just display it in Excel. My confusion is why involve Access at all by embedding the Excel within an Access form? If the answer is because the data is in Access, then why not just use Excel query to pull the data from Access and then simply display Excel without an Access shell?

    Just curious...

    Wednesday, March 29, 2017 3:22 PM
  • Because then human nature would intervene and people would start saving the files everywhere and emailing them to each other, as I say one of the 2 key aims is to do away with millions of spreadsheets an replace it with a MIS.  I don't want Excel, but that's the only sensible way of having frozen panes and scrollable areas of datasheet.
    Wednesday, March 29, 2017 10:41 PM
  • Hi Mike, Thanks for the clarification. Can you describe the difficulties you encountered in trying to get this approach to work? Also, feel free to email me the sample files if it will help explain it better. Email address is in my profile or website (not posting it here to avoid email harvesters).
    Wednesday, March 29, 2017 11:43 PM
  • Have you tried using a BoundObjectFrame?

    https://msdn.microsoft.com/en-us/library/office/ff194514.aspx

    Also, in the past I've used the ActiveX ListView control which is part of the Windows Common Controls library.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, March 29, 2017 11:53 PM
  • Hi MikeMay,

    I think that you can try BoundObjectFrame as suggested by Paul P Clement IV.

    also you can try to make search regarding Office spreadsheet ActiveX control.

    you need to download that control and register it before use.

    you can download it from below link.

    Office 2003 Add-in: Office Web Components

    it was discontinued after Office 2003.

    so you can try to study it first and if you think that it can help you to solve your issue then you can try to implement it.

    otherwise you can try to use BoundObjectFrame. it will be an easy option for you.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 30, 2017 2:34 AM
    Moderator
  • Thanks for the answers but this is quite frustrating, nobody seems to quite grasp what I'm trying to do and how simple it is.  I'll start again:

    I'm generating a Proof Of Concept project in Access.  I need to be able to display "spreadsheet-like data" on an Access form.  The reason is simple - there are heaps of columns and possibly many rows and I want the headings and leftmost few columns to always be visible, but the rest of the data to scroll.  If there was another Access OCX that could achieve this, I wouldn't use Excel.  However, achieving this in Excel took 10 minutes so the simplest thing would be to be able to display that Excel file on the access page.

    Bound or Unboud OLE controls do the same thing, once you activate them, they open Excel.  That's not what I want.  I ant the data on the form, not in another program.  The whole point of this is to stop people creating millions of excel spreadsheets but use the proof of concept management information system instead.

    The Office Spreadsheet component is not a million miles away (at least it displays in the form) but doesn't allow any of the freeze panes functionality that this solution needs.  Here's a picture of my spreadsheet and I want columns A to O to stay on screen at all times, and rows 1-3 to stay on screen at all times - the rest scrolls.

    Tuesday, April 11, 2017 6:56 PM
  • The Office Spreadsheet component is not a million miles away (at least it displays in the form) but doesn't allow any of the freeze panes functionality that this solution needs.  Here's a picture of my spreadsheet and I want columns A to O to stay on screen at all times, and rows 1-3 to stay on screen at all times - the rest scrolls.

    Hi MikeMay,

    In your strive to get rid of the millions of spreadsheets, you could also think of a different presentation of the data than to mimic a spreadsheet.

    Using a continuous form, you can place the first three rows of youe example in the header of the form, while the row 4 to ... are (scrollable) in the detail section. But this is just the standard way to make continuous forms.

    The columns PQRS (and further on) can also be made "scrollable" by manipulating the Left-, Width-, Visible- and ControlSource-properties of the respective Controls. It depends a little if you need a to scroll per column, or you need to scroll per Period over 4 columns. However, this is not standard Access, and you have to write your own VBA-code.

    Within Access you can change almost unlimited all kind of Control-properties dynamical using VBA-routines. I use these kind of techniques to display any continuous form in any application using one standarized form, and meta-data in definition tables.

    But you have to decide if it is worth to go that direction, or that a different way of displaying is a better option. Instead of a form that displays "all" with the help of scrolling, you could also make a separate form to compare the different columns per row, or to compare the different rows per column (or group of columns).

    Imb.

    Tuesday, April 11, 2017 9:38 PM