Solved

Excel Automation - How to handle excel events from Access

Posted on 2004-04-10
6
354 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

726 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