Run Solver Function from Access

[Catherine Castle]

I am trying use the solver functionality via Access.
Here is my situation:
I am creating a dataset in Access. I tranfer that to a excel spreadsheet.
I need to run an optimization on that data using the solver function. I then
need to transfer the results back into Access.

Here is my experience:
I can transfer the data into the spreadsheet using the
docmd.transferspreadsheet code.
I cannot get the solver function to work without locking up the computer and
having to reboot.

I am using Office XP
Any suggestions?

 

[Tom Wickerath]

Hi Catherine,

I'm not sure why you are experiencing lock up problems on your PC. Do you have the Solver Add-in
installed in Excel (ie. do you see it in the list when you click on Tools > Add-Ins... when you
have Excel open?

Recently, I was working on an Access ---> Excel automation issue that involved using the Analysis
Toolpak add-in. I found that even though this add-in was checked in the newly created Excel
spreadsheet, it was not activated. I was getting a #Name error in the Excel spreadsheet. I sent
a question to a contact of mine at Microsoft. Here is the answer that I got back:

**************************
Here is a comment that was made by someone on the Excel team. Let me know if this helps.

Automation does not load the add-ins and if they are already set to True, it isn't going to try
and load it again as it assumes it already was.

This is usually the best way to open the add-in from automation.

XL.Application.Workbooks.Open(XL.Application.AddIns("Analysis ToolPak").FullName)

And for some add-ins you also need to trigger the Autpen

XL.Application.Workbooks.Open(XL.Application.AddIns("Analysis ToolPak").FullName).RunAutoMacros
xlAutoOpen

**************************


Tom
________________________________


I am trying use the solver functionality via Access.
Here is my situation:
I am creating a dataset in Access. I transfer that to a excel spreadsheet.
I need to run an optimization on that data using the solver function. I then
need to transfer the results back into Access.

Here is my experience:
I can transfer the data into the spreadsheet using the
docmd.transferspreadsheet code.
I cannot get the solver function to work without locking up the computer and
having to reboot.

I am using Office XP
Any suggestions?