Solved

Excel Automation - How to handle excel events from Access

Posted on 2004-04-10
6
347 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 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

12 Experts available now in Live!

Get 1:1 Help Now