Access Automation from Excel

[aniphilip]

Hi All,

I have a requirement to automate Access functionality from Excel. I need to open Access, trigger button events and close Access from Excel.

I Achieved opening Access from Excel. I cannot find a way to trigger a button click event from Excel for an Access form.

To be more precise,
I have Access db called a.mdb. a.mdb has a.form and a.command button inside the form
I have xls sheet called b.xls. How I will fire an onclick event for a.command if I have already access a.form and a.mdb.


Any help greatly apprecaited, How a tight deadline coming up

 

[easyit]

Hi,

To my knowledge, this is not possible. Could you tell us what these buttons should accomplish?

There are several ways of getting data to and from access <-> excel. the most suitable solution depends on your needs.

EasyIT

"Do you think that’s air you're breathing?

 

[aniphilip]

The Requirement in Simple Terms is

1) Open Access from Excel (Done)
2) Open Access Form (Done)
3) Initiate a Command Button Click Event( To be done)
4) The Command Button Will pop open a MsgBox Dialogue. This need to be close from Excel( HandsFree)
5) Close Access (Done)

 

[easyit]

...what I mean is what do these action do? If it is just about showing a msg box...

EasyIT

"Do you think that’s air you're breathing?

 

[aniphilip]

The command button has the click event coded to import an Excel sheet into the table.

The most trickier is closing the MsgBox. it is a modal dialogue and code will hang there for user input. How to close it automatically ?

 

[easyit]

...why not skip the form? Create a query in access that imports the excel (link the sheet). Execute the query from excel. Or run code from a form that you set as startup form, withoud any popups or user interaction.

EasyIT

"Do you think that’s air you're breathing?

 

[aniphilip]

Cannot Do that. The Requirement is very clear on this. Need to run with existing Access code i.e either by user running form or by automated through excel.

 

[easyit]

To my knowledge, this is not possible.

...create another AccessDB, perform all necessary task in there?





EasyIT

"Do you think that’s air you're breathing?

 

[snayjay]

Why exactly can't you do as EasyIT suggested...

Make a new form "STARTUPfrm", set it to open on Startup thru the startup menu. on the Form Open event, set your code to do what you want and close the database afterwards.

As per your requirements:

The Requirement in Simple Terms is

1) Open Access from Excel (Done)
2) Open Access Form (Done)
3) Initiate a Command Button Click Event( To be done)
4) The Command Button Will pop open a MsgBox Dialogue. This need to be close from Excel( HandsFree)
5) Close Access (Done)

An On Form_Open Event will initiate as per requirement #3, and then just handle the msgbox dialogue from there. (What exactly is this msgbox dialogue??) If it's an error msg, then just handle it with error handling code.

Sounds easy, but maybe I'm missing something. Can you tell us why this won't work?