[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Excel Automation - How to handle excel events from Access

Posted on 2004-04-10
6
Medium Priority
?
364 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
4 Comments
 
LVL 32

Accepted Solution

by:
jadedata earned 2000 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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

591 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