This is a multi-part question...

1) From within my Access-2000 application (button-clicked event on a form), I want to open an Excel file that has macros in it AND launch a macro.

2) From within the now opened Excel file, I want to reference the values of Unbound fields on the forms in the Access application.

I created an excel file to test -- initially with no macros.

This event code worked fine to open the file, and make it the visible and active application:

Dim MyXL as Object
Set MyXL = GetObject("C:\QUERYOUTPUT_001.XLS")
MyXL.Application.Visible = True
MyXL.Parent.Windows(1).Visible = True

Next, I added a simple macro (Macro1 -- it puts the current row number in the active cell). Also note that Macro security is set to Medium. With this setting, if I open the excel file manually, I get a pop-up window that ask me to choose "Enable" or "Disable" macros.

When I run the event code to open the file with macros, I get a different pop-up titled "Microsoft Excel" that has more verbiage (about the file has macros, yadda-yadda...) and offers three choices "Yes", "No", "Cancel"

If I click on "No", the macro does not function (as expected).

If I click on "Yes", the macro still does not function -- and I get a pop-up box titled "Microsoft Excel" that says "The macro 'Macro1' cannot be found."

Any help would be greatly appreciated.

-smr

You can "Simply" run your code from within access thus avoiding the excel security...

In Access just add myxl. infront of every line you would have in excel, and pronto... It works...

Regards

I am hoping to make it all invisible to the end-user. So, if there is a way to automate the process of getting past the pop-up box for security and still be able to run the macros, that'd be great.

Anyone?

Not sure if this would be the "best" way to do it (or even if it would work), but you could consider using the send key function to press enter (or tab to select the right button, then enter) to enable macros when excel starts, then start your macro after that.

Excel does not ask me if I want to enable macros when I open it. I have my Macro saved to my personal macro workbook, as appossed to "new workbook" or "this workbook", so that may be why.

Hope that helps, let me know if need some more info...I'll try.

Noobie

I must be misunderstanding something....

This time I tried creating the test file (File1) with no macros. I also created a 2nd file (File2) with the macro (Macro1) I want to ultimately be used.

So I can open File1 using the code shown above fine. But how do I now execute any macros?

-smr

When I make a macro in excel, I always start it by going to Tools / Macro / Record New Macro. When the pop up window to select the keys to run the macro appears, I name the macro, I select what every key combination I want (ie ctrl-shift-p), and then I select store macro in "Personal Macro Workbook". That way the macro can be run for any excel workbook you open or create. Does that make sense?

So what are you doing with this file? Are you going to keep adding more data to the same file, or are you going to make a new file with new data every time?

Once the excel file is open from the access code, I just used the Sendkey function (in Access code) to start the macro. Ie: SendKey "^+p", 5

Hope that helps,
Noobie