Link to Solver32.dll from Access VBA

Question

I have an Access database that will export data to a procedurally created excel template for visibility, more complex calculations / visualization and, most importantly, allows the user to play more with the data.

One of the main things that this template should do is complete the resolution procedure. Solver does not exist in any of the standard reference libraries in access (what I know). I'm a little new to this area of ​​things, but I looked around the network and found out where Solver32.dll is located, I pointed to it, it didn’t work. I looked a little more and saw that I might need to register it with regsvr32, since it is not in the system32 directory, I tried this and it did not work.

This may be due to some admin-level access issues that I have (a working computer ... sigh), and I'm trying to sort them, but I have a feeling that this will not fix it.

In any case, the question is:

1) I just need administrator rights to register Solver32.dll, and then my reference work will be easy?

2) If not, is there any other way to reference the solver library?

3) If not, or if I can’t get the right to register solver32.dll (maybe my IT department can be mean), is there a way to open the plugin resolver code on the excel sheet that I use and it just works there? Because if the code is very simple:

........

AddIns("Solver Add-In").Installed = True solveradd cellref:="$D$6", Relation:=1, FormulaText:="1" solveradd cellref:="$D$6", Relation:=3, FormulaText:="0" SolverOk SetCell:="$F$6", MaxMinVal:=2, ValueOf:=0, ByChange:="$B$6:$D$6", _ Engine:=1, EngineDesc:="GRG Nonlinear" SolverSolve 

............

In any case, we were looking for a solution for this big piece of the day. This MS does not help; in it, infinite wisdom chose the name of this Access program, which really helps with googling searches (sarcasm).

Edit: by answering some questions in the comments

Version: Access 2010

Error: When I try to link to a dll from the Access VBA IDE, I get this error "I can not add a link to the specified file"

When I try to run regsvre.exe, I get this error "The module" C: \ ProgramFiles ............. \ Solver32.dll "is loaded, but the entry point DllRegisterServer was not found. Make sure "C: \ ProgramFiles ......... \ Solver32.dll" is a valid DLL or OCX file, and then try again. "

Answer 1

Well, I realized that an adhesive tape is suitable for this. Depending on the security settings in your location, this may or may not work. I'm not even sure how well this will work in my location, but for now it is. God knows what will happen when I breed this

I basically insert a macro into an Excel workbook (from Access) to start Solver, since I cannot get solver32.dll to work from Access to Access. Here is the code

 Private Sub InjectSolverMacro(ByRef xlbook As Excel.Workbook, ByRef xlapp As Excel.Application) Dim xlVBProj As VBProject Dim xlModule As VBIDE.VBComponent Dim sCode As String xlbook.Worksheets(1).Select AddIns("Solver Add-In").Installed = True Set xlVBProj = xlbook.VBProject xlVBProj.References.AddFromFile ("C:\Program Files\Microsoft Office\Office14\Library\SOLVER\Solver.XLAM") Set xlModule = xlbook.VBProject.VBComponents.Add(vbext_ct_StdModule) sCode = "Private sub SolverMacro()" & vbCr _ & "SolverAdd CellRef:=""$D$6"", Relation:=1, FormulaText:=""1"" " & vbCr _ & "SolverAdd CellRef:=""$D$6"", Relation:=3, FormulaText:=""0"" " & vbCr _ & "SolverOk SetCell:=""$F$6"", MaxMinVal:=2, ValueOf:=0, ByChange:=""$B$6:$D$6"", Engine:=1, EngineDesc:=""GRG Nonlinear"" " & vbCr _ & "SolverSolve" & vbCr _ & "End Sub" xlModule.CodeModule.AddFromString (sCode) End Sub 

First I had a link to a link to Solver32.dll. This did not work. Then I saw this thread , where someone said you need to point to Solver.XLA. God knows why this is. But it does work.

A few other notes. For access, you will need a link to the Microsoft Visual Basic for Applications Extensibility 5.3 library in Access. Then you will need to go to the macro settings in the trust center in Excel (File-> Options-> Trust center → Trust Center Settings → Macro Settings) and select "Trust access to the VBA project object model." This will allow you to enter the code. It may also work poorly when I deploy it. My company probably would not be very interested in having all my users disable this security.

But it does work.