Export thread
Calling all programmers
#1
Necronic
Necronic
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
#2
Eriol
Eriol
I'd try www.stackoverflow.com for this. I'd say you have a pretty decent issue here, but having never touched VBA, I can't help you specifically on your problem. The only additional information you need is your development environment. Visual Studio? Which version? Something specific inside a version of Excel? Which version of excel? Put all that information plus what you posted above to StackOverflow, and I'm sure somebody will be able to help you.
Good luck!
Good luck!
#3
Necronic
Necronic
I've already got a ticket out on Stack Overflow. First time I've had something tricky enough to justify it tbh. Oh yeah, and as for the IDE it's just the built in VBA IDE in Access. I've gotten a couple of bites but they just aren't sure how to fix it.
#4
strawman
strawman
No, as far as I can tell you wont be able to use solver in access. The closest people have gotten is making the excel spreadsheet, running it through excel, then reading the spreadsheet to pick out the value.
Solver isn't a terribly complex function, though, so you might take a swing at writing an implementation of it specific to your application, which should be easier than a general solver.
Solver isn't a terribly complex function, though, so you might take a swing at writing an implementation of it specific to your application, which should be easier than a general solver.
#5
Necronic
Necronic
There's something magical with me and writing help request threads. I swear after every one of these I figure out how to do it minutes after I write it. Same thing here. So the reason that injected code was failing was because I was referencing Solver32.dll. I should have been referencing Solver.xlam. Why the dll is not referenced is beyond me (and access can't reference it, only Excel so the injection is the only way to do it). This creates some security issues like having all the users disable some important security measures, but v0v not my problem.
And the code works now. And it's awesome. And I'm awesome. And you guys are awesome.
Also, I don't know how easy it would be to make my own Solver function (or how efficient it would be in Excel.) It's solving a 3 variable equation (really a 20 member matrix) with some strange constraints in it. I probably could have clubbed something together in Matlab back when I was good with that, but luckily I don't have to because the CODE WORKS NOW!!!!
This makes me feel like its friday.
And the code works now. And it's awesome. And I'm awesome. And you guys are awesome.
Also, I don't know how easy it would be to make my own Solver function (or how efficient it would be in Excel.) It's solving a 3 variable equation (really a 20 member matrix) with some strange constraints in it. I probably could have clubbed something together in Matlab back when I was good with that, but luckily I don't have to because the CODE WORKS NOW!!!!
This makes me feel like its friday.
#6
Eriol
Eriol
Link the SO question if you could. It may be interesting to see what they make of it too!