Solved

Excel Automation - How to handle excel events from Access

Posted on 2004-04-10
6
348 Views
Last Modified: 2012-06-21
I have an Access 2002 application that has a form with an embedded Excel OLE object.  Using a button, i allow the user to open the embedded excel app, and thus let the user interact with the Excel file.  Now, I would like to be able to detect which cell(s) the user has selected whenever they choose a different range of cells.  I realize that a WorkSheet object has a "SelectionChanged" event that fires when the user chooses a different cell, however, I want to be able to do this from the Access parent app.  I figure, the best way to do this would be to:

1) Handle the  "SelectionChanged" event from the Access parent application that is hosting the embedded OLE Excel file
2) Be able to RETURN to the Access application the new range of cells that have been selected

Any help would be appreciated.
0
Comment
Question by:rarich02
  • 2
  • 2
6 Comments
 
LVL 32

Accepted Solution

by:
jadedata earned 500 total points
ID: 10799045
Greetings rarich02!

  If you have an Excel object dimmed/instantiated and in scope you can trigger these events but you can not run them from Access.
  The firing and handling of that event is all handled by the parent application.  You could trigger these events or even call them from Access, but can't put event code for an Excel object in the Access project container and have it work.

  That code would need to be in the Excel workbook to fire, ie VBA in the Workbook/Worksheet module.
 
regards
jack
0
 

Author Comment

by:rarich02
ID: 10802888
jadedata,

That makes sense.  Now, my situation is such that the Access application I'm writing allows the user to pick which excel file he wants to work on at that particular moment.  I do this by opening up a file dialog box to allow the user to pick the Excel file, and then embedding that file into an unbound OLE frame.  Once embedded, I then open up the excel file from the OLE frame by using the OLE's Action property.  Because the user may pick a different Excel file every time, there's no way of pre-programming the Excel file to handle the events I want.  Therefore, I guess my next question would be, given that I programmatically embed different Excel Workbooks into my OLE frame,  how do I:

1) Programmatically add code to the Embedded Workbook in order to handle the events I need.  This way, when the event is fired, then the Code I placed in the Workbook can do what I need it to do.
2) "trigger these events or even call them from Access", as you put it.

Is there a reference you can point me to?

Thanks in advance.  If I could give more points, I would.
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10804620
Why don't you do a "sweep" routine on the Excel object FROM MSAccess as the user closes the book?

What is the nature of this code that is has to fire every time the user moves in the target spreadsheet??

So far this sounds to have a little built in inefficiency...

-j-
0
 

Author Comment

by:rarich02
ID: 10805697
Our client is a third party contractor who is hired by different companies in order to keep track of their employee's benefits.  For example, XYZ corp would hire our client to keep track of the benefits XYZ pays its employees.  Therefore, our client may have 10 different companies they are keeping track of, each of which may have several dozen employees.

The Access application we designed for our client helps them keep track of all these companies, their employees and their employees' benefits.  At the end of each month, each of these companies sends our client Excel spreadsheets containing what benefits were paid out to their employees, for the previous month.  Once our client has this information, they now need to manually go through each of these spread sheets and enter this data into our application.  We want to remove this 'manual' step.  Basically, what we'd like to do is create a 'Wizard' like add-in for the Access app that allows our client to import the necessary data into the database automatically.  To do this, our client needs to tell our Access app which columns/rows contain which piece of information.  

Therefore, the idea was this:  
1) The wizard would ask the user to pick which Excel file contains the data they want to import
2) The wizard would them embed the Excel file into an OLE object
3) Next, the user would be taken through a series of questions in order to determine where in the Excel spread sheet the required data lies.  For example, the user would be asked to click on the column that contains Employee's 401K information.  When the user clicks on the required column in the Excel spreadsheet, I'd like to capture that click, and save the column that they clicked on for future use.  This would go on and on, until all the required parameters have been set.
4) Finally, the wizard would iterate through the spreadsheet, given the specified parameters, and import the data into the Access application.

Therefore, as you can see, I'd like to capture events fired in the Excel spreadsheet so that I may then interact with my Access application.

Thanks again
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

920 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

14 Experts available now in Live!

Get 1:1 Help Now