We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Excel Automation - How to handle excel events from Access

rarich02
rarich02 asked
on
Medium Priority
379 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.
Comment
Watch Question

MS Access Systems Creator
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
jadedataMS Access Systems Creator
CERTIFIED EXPERT

Commented:
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-

Author

Commented:
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
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.