locked
Grabbing the embedded excel sheet in an unbound object frame on an Access form RRS feed

  • Question

  • I am using an embedded excel sheet in an unbound object frame in an access form for data entry.  For the most part, it works for what I need it to do.  The problem is that about 25% of the time, my code isn't grabbing the sheet correctly.  Either it will grab the wrong sheet or it can't even find the object; it errors out when trying or when I double click the object, it runs office setup (as if that would actually fix whatever that problem is).  My current code for grabbing the sheet is:

    Private Sub GrabWorksheet()

    On Error GoTo Fail Set robj = RateSheet.Object.Application objv = robj.Visible RateSheet.Action = acOLEActivate Set rwks = RateSheet.Object.Application.ActiveSheet Set rwkb = rwks.Parent Set robj = rwkb.Parent robj.SendKeys "{esc}" robj.Visible = objv On Error GoTo 0 Exit Sub Fail: MsgBox "That didn't work, please try again"

    End Sub

    Often, when I run that, it leaves the excel window open and shows the object as if it is disabled.  Any help on getting a more reliable/cleaner method and/or a way to create a new instance of excel each time the form opens would be most helpful.

    Thanks.

    Cassandra Morphy

    Tuesday, December 10, 2013 9:05 PM

All replies

  • it runs office setup (as if that would actually fix whatever that problem is).

    Sounds like you may have more than one version of Access on your PC or a different version of Access then either Outlook or Visio. Is this the case?

    Chris Ward

    Tuesday, December 10, 2013 9:27 PM
  • Nope, all 2007 (no Visio though)
    Wednesday, December 11, 2013 1:07 PM
  • Well perhaps you can remove this line

    robj.Visible = objv only because you already have objv = robj.Visible

    I don't see where you are declaring anything. Is this the actual code you are using?

    Seems like the following may need to be declared

    Excel Application

    Active Workbook

    Active Worksheet


    Chris Ward

    Wednesday, December 11, 2013 2:20 PM
  • Very interesting in that you are using Excel as a data entry vehicle. Why ?
    It's so difficult to validate data in Excel whereas in Access, there is much more control over the whole data entry process.

    Regarding your problems:
    1) If Office tries reinstalling itself, that indicates a registry problem.
    You should go to the Control Panel, Programs, Add/Remove, and then click on Microsoft Office. There should be a repair option present.

    2) RateSheet.Action = acOLEActivate
    Very interesting statement above.  I don't understand why you can't just use:
    RateSheet.Activate

    We definitely need to see how you established all of the object variables.


    Wednesday, December 11, 2013 3:36 PM
  • I'm using Excel as a data entry vehicle because I'm inputing a lot of data at once.  Doing it through the forms would require multiple text boxes.  I tried that first, it got to be a bit troublesome.  It would also be a pain to put all the data in at once.  It's basically input of tables of varying sizes and locations in the source document.

    1)  Tried that, problem persists.

    2)  RateSheet is the object, not the worksheet.

    As for the declarations, that's just

    Global robj As Excel.Application
    Global rwkb As Excel.Workbook
    Global rwks As Excel.Worksheet

    And the visible toggle is so whatever visibility the sheet was before activating the object, it will be after I'm done.  This is mainly because it has a habit of just creating a hidden workbook in whatever instance of Excel I have open at the time when the form gets launched.  This is pretty much where the entire issue is.  If it would just launch a new instance when the form gets launched, or, better yet, when Access opens or when I tell it to, there would only be the appropriate workbooks in an easily grabbable instance.
    Friday, December 13, 2013 1:40 PM
  • A couple thoughts that come to mind

    1) Why not open a new instance of Excel instead of using one that is already open. Then you are not dependent on the state and can remove the toggle all together. When you are finished with it, kill it.

    2) Instead of using Excel at all, use a Form in continuous view. Place a command button in the Form Header (not the page header) to add 10 records or some number or even have a text box next to the button where you can enter the desired number of Records to add. Then click the button and that many records are added and the form is filtered to that series of records.

    This would allow you to mimic using a spreadsheet without using a spreadsheet at all.


    Chris Ward

    • Proposed as answer by Syswizard Friday, December 13, 2013 2:40 PM
    Friday, December 13, 2013 2:17 PM
  • I totally agree with Chris's ideas.

    However, with embedded OLE, I don't think there is a way to insure a new instance of Excel is opened.

    Set robj = RateSheet.Object.Application

    It looks like the above does all of the work. I've never done this before, but I definitely want to experiment with it....
    especially with Excel Chart sheets. I see the need for better graphics within Access.
    This could be the way to go if the technical issues can be overcome.
    No doubt the 25% failure rate here is due to certain conditions being present.

    Friday, December 13, 2013 2:46 PM
  • I totally agree with Chris's ideas.

    Sorry, I must be completely wrong if we agree :-)

    Don't you just love paradoxical statements?


    Chris Ward


    • Edited by KCDW Friday, December 13, 2013 3:32 PM
    Friday, December 13, 2013 3:30 PM
  • 1) Ah, if only Set RateSet.Object = New Excel.Application would work

    2)  That is an interesting take on it, however part of the issue is that a lot of the tables are on the large size (for this type of entry), like 20x6 or so.  It's a lot easier to copy the whole thing and paste back into an excel sheet rather than having to do each cell one at a time.

    Friday, December 13, 2013 8:58 PM
  • If the range is the same in Access, you can copy and paste multiple rows and columns directly from one to the other.

    But I still don't understand the objection to using Access alone. The method above gives you the Spreadsheet like view and you can add to each cell as you please. If you information is already in a spreadsheet somewhere else that you are copying and pasting, you would be better served to import the data rather than copy and paste.

    If you are thinking 20 rows of 6 columns is a lot...That is pretty small and can be imported in a microsecond without human error.


    Chris Ward

    Friday, December 13, 2013 10:49 PM
  • I'm using Excel as a data entry vehicle because I'm inputing a lot of data at once.  Doing it through the forms would require multiple text boxes.  I tried that first, it got to be a bit troublesome.  It would also be a pain to put all the data in at once.  It's basically input of tables of varying sizes and locations in the source document.

    --multiple textboxes sounds like you have repetitive data types. I will guess (from experience here) that you probably only need a few textboxes and an ID field to keep your data together for what would be your "Record". Access is a relational database system and reads data from top down -- instead of horizontally. Here is an example of a "Normalized" table ("Normalized" means you are using Relational features)

    tblx
    orderID fld1 fld2 fld3
    1 hammer black 3
    1 hammer yellow 4
    1 bowls small 7
    1 bowls large 6
    2 plates small 5
    2 plates large 8
    2 cups red 13
    2 cups green 9
    2 vase skinny 4
    2 vase wide 3
    2 napkins white 10

    This sample table is an Order table and keeps the orders together with the OrderID field (Which is not unique).  This would like a foreign key field for a Master type table.

    Excel is a Spreadsheet database system, and it's features are spreadsheet type functions/charts/formulas for analyzing segments of data from a large data store -- like an Access DB. Ideally, you store data in a relational database system like Access (or if you have more than 2 gigs of data to store -- use a server based Database system like MS sql server).


    Rich P


    • Edited by Rich P123 Friday, December 13, 2013 11:16 PM ...
    Friday, December 13, 2013 11:15 PM
  • The problem is the source data isn't strutctured, the tables differ greatly from table to table (what the column labels and row labels are) and I won't be the one doing all the data entry (just the initial data entry).  The idea is to set it up so anyone who has access can import a new table and it will go in properly.  Currently, I have the user select what the labels are, and I'm thinking that's probably stretching the capabilities of some of the people that would need to use this.

    I don't think 20 rows of 6 columns is a lot, just a lot for cell by cell copying.

    Monday, December 16, 2013 1:31 PM
  • 1) The problem is the source data isn't structured, the tables differ greatly from table to table (what the column labels and row labels are)

    2) I won't be the one doing all the data entry (just the initial data entry). 

    3) The idea is to set it up so anyone who has access can import a new table and it will go in properly. 

    4) Currently, I have the user select what the labels are, and I'm thinking that's probably stretching the capabilities of some of the people that would need to use this.

    5) I don't think 20 rows of 6 columns is a lot, just a lot for cell by cell copying.

    The intent of my below comments are meant to impress upon you the importance of making correct decisions in building your application.

    1) If you are wanting users to have a tool they can use to make things in their environment better, then it stands to reason to have a good and sound structure going into it. Otherwise you may as well continue to use spreadsheets because a database is not going to suit your needs. Access is not a spreadsheet and will not give you the same results you get in a spreadsheet.

    2) Your responsibility as the application developer is to protect data integrity and facilitate the job by making it user friendly. What you are describing is quite the opposite. There is so much room for data errors that the users may as well be manually entering the data.

    3) Data entry is not magic. There is no miracle for getting the data into your database. Having said that, there are many great tools at your disposal for getting the data into the database correctly. The real wonder of a database is reducing redundancies and protecting your data and manipulating the data in many meaningful ways to better analyze, track & understand it. Importing a Spreadsheet into an Access control the way you described, does not "make the data go in properly".

    It doesn't really do anything to facilitate the work at all. You already have it electronic. You can already copy and paste the fields from Excel to Access. What do you gain by taking the spreadsheet and putting into a control on the database Form and still copy and paste the data. It is a needless step. It would be far better to devise a method to import the spreadsheets data to where it belongs.

    4) Don't underestimate those doing the picking of labels. They may surprise you with their abilities if they are cultured correctly. If you worry about the users' abilities, then build the application to suit there abilities. It is the application developer's responsibility to make the application "unbreakable".

    5) There should be no "cell to cell copying". Set up correctly, there should only be importing of the data directly into the Tables. If there is more than one layout of the spreadsheets, you should organize them in groups so you can analyze the import process to manipulate the data where it belongs. you can have more than one import structure. Remember the labels of the Columns are only guidelines of where the data is imported to. You can import the spreadsheets into "new Tables" and then use an append query to move the data from one Table to another and pick the columns at that point. that would be more efficient.

    You mention column labels and row labels. Are your row labels simply what Excel uses along the side, a series of numbers? if so, then does the data in the spreadsheet correlate specifically with the row number as a recordset? Or is the spreadsheet structured like a cross-tabbed document? Or is it basically unstructured.

    We are concerned for what you are trying to do and the best method for achieving your goal. Your structure is in doubt at this point. Your method for importing the data is in question as well.

    If you feel you already have the best method then we wish you the best and welcome further questions you may have along the way.

    Thank you!


    Chris Ward

    Monday, December 16, 2013 3:20 PM
  • I'm not just dumping it in there and calling it a day.  I have functions that take the table, as pasted, and sticks it into a single table in a meaningful way.  This could be just as easily done with selecting the chart in the source worksheet and pressing the same button.  Down the line, I'm going to be using this data to compare apples to oranges.  I'm trying to do this in such a way as to not have it be too complicated, work, and not have to have the user straight out say "this apple is here in this sheet, this orange is there in that sheet"

    Monday, December 16, 2013 3:56 PM