Necronic
Staff member
Ok, I am having a real problem with a program I am writing. I am a self-taught programmer so when I enter into a new realm of things I start having real problems. Right now I am dealing with .dll's and I have little to no experience with this and I think it's showing.
So here's what I am doing. I have an Access database that programatically generates an excel spreadsheet. The last step in the spreadsheet requires Solver. The problem is that there is no reference to Solver in the Access IDE. I tried pointing it to the Solver32.dll and that didn't work ("Can't add a reference to the specified file").
Then I tried regsvr32.exe on the .dll and I got "The module 'C:\ProgramFiles.............\Solver32.dll" was loaded but the entry-point DllRegisterServer was not found. Make sure that "C:\ProgramFiles.........\Solver32.dll" is a valid DLL or OCX file and then try again"
Then I tried to have the access program generate the code within the excel spreadsheet and programatically add the reference within the Excel side of the VBA with the following:
The line "xlVBProj.References.Addfromfile...." crashes saying 'Error in loading .dll'. Now, if I remove that line and run it the code goes into the spreadsheets VBA, which (after I manually load the reference), I can run. But I can't figure out how to programattically add a reference to the dll.
I talked to our IT department and got lucky, the guy was an ex-programmer. He said that it's possible that this dll is dependant on another, but he didn't know which one. I thought that maybe the regsvr was failing because I didn't have admin access, but he tried it with admin and it still didn't work.
This is driving me nuts. I know we have some programmers in here. So I'll just say this once:
HALP
So here's what I am doing. I have an Access database that programatically generates an excel spreadsheet. The last step in the spreadsheet requires Solver. The problem is that there is no reference to Solver in the Access IDE. I tried pointing it to the Solver32.dll and that didn't work ("Can't add a reference to the specified file").
Then I tried regsvr32.exe on the .dll and I got "The module 'C:\ProgramFiles.............\Solver32.dll" was loaded but the entry-point DllRegisterServer was not found. Make sure that "C:\ProgramFiles.........\Solver32.dll" is a valid DLL or OCX file and then try again"
Then I tried to have the access program generate the code within the excel spreadsheet and programatically add the reference within the Excel side of the VBA with the following:
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\Solver32.dll")
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 SubI talked to our IT department and got lucky, the guy was an ex-programmer. He said that it's possible that this dll is dependant on another, but he didn't know which one. I thought that maybe the regsvr was failing because I didn't have admin access, but he tried it with admin and it still didn't work.
This is driving me nuts. I know we have some programmers in here. So I'll just say this once:
HALP
