VBA 429 error

[cvaccess]

Hello,

I am trying to export spreadsheets through VBA. I get an ActiveX error. This is the code I have, below. There are no ActiveX references in VBA and Monarch.tlb is referenced. I commented out the previous way of getting and creating the Monarch object to directly find the object with the path but that does not work. What is strange is that 2 users can use the app fine but others cannot. They have the same versions and same desktop. Any help will be appreciated!

Option Compare Database
Option Explicit

Sub MonarchExp()
'On Error GoTo Error_Handler

Dim MonarchObj As Object
'original...Dim openfile, openmod, t As Boolean
Dim openfile As Variant
Dim openmod As Variant
Dim t As Boolean
'If Monarch is currently active GetObject will use Monarch. If it is not use the
'CreateObject() to open another copy of Monarch.

On Error Resume Next
Set MonarchObj = GetObject("C:\Program Files\Monarch\Program\monarch.tlb")
'Set MonarchObj = GetObject("", "Monarch32")
Error.Clear
If MonarchObj Is Nothing Then Set MonarchObj = CreateObject("C:\Program Files\Monarch\Program\monarch.tlb")
'Set MonarchObj = CreateObject("Monarch32")
End If

t = MonarchObj.SetLogFile("C:\Documents and Settings\" & ClipNull(GetUser()) & "\My Documents\MPrg_G5.log", False)
openfile = MonarchObj.SetReportFile("\\azservb01\RIMS_DOWNLD\sp" & _
Forms![frmLogin]![txtSP] & ".txt", False)
If openfile = True Then
openmod = MonarchObj.SetModelFile("\\azservb01\DPT_ENRCOM\EDI\RIMS EDI JOBS\" & _
"REET\Files\Macros\ComparisonReport_new.mod")
If openmod = True Then
MonarchObj.JetExportTable ("C:\Documents and Settings\" & ClipNull(GetUser()) & "\My Documents\sp" & Forms![frmLogin]![txtSP] & ".xls")
Else
MsgBox "Spreadsheet not created"
Exit Sub
End If
End If
MonarchObj.CloseAllDocuments
MonarchObj.Exit
'Error_Handler:
' ErrorHandler
End Sub


Thank you!

 

[stevexff]

I get an ActiveX error.

Have you considered stepping through the code in the debugger to see which line is causing the problem?

Not only will this help you, but us as well...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[cvaccess]

Thank you Steve for the quick response. It is in this line:

Set MonarchObj = GetObject("C:\Program Files\Monarch\Program\monarch.tlb")

which I have debugged.

Thanks.

 

[PHV]

Provided your tlb/exe/dll is properly registered why not keep this syntax ?
Set MonarchObj = CreateObject("Monarch32")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886