7 Replies Latest reply: May 15, 2014 9:53 AM by Nick Osdale-Popa RSS

    VBA access issue

    Christina_db _

      Hello,

       

      I am trying to utilize Monarch to export spreadsheets through VBA. I get an ActiveX error. I looked through the existing topics and found one that was similar (VBA app bombs) but I did not see resolution on it. So I need some help please. This is the code I have, below. I also removed the ActiveX references in VBA.

       

      Option Compare Database

      Option Explicit

      '0123456789001234567890012345678900123456789001234567890012345678900123456789001234567890

       

      'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

      'EXPORT SPOOLER REPORT TO SPREADSHEET VIA MONARCH

      'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

       

      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 FilesMonarchProgrammonarch.tlb")

          'Set MonarchObj = GetObject("", "Monarch32")

          Error.Clear

              If MonarchObj Is Nothing Then

              Set MonarchObj = CreateObject("Monarch32")

          End If

       

          t = MonarchObj.SetLogFile("C:Documents and Settings" & ClipNull(GetUser()) & "My DocumentsMPrg_G5.log", False)

          openfile = MonarchObj.SetReportFile("azservb01RIMS_DOWNLDsp" & _

              Forms![frmLogin]![txtSP] & ".txt", False)

          If openfile = True Then

              openmod = MonarchObj.SetModelFile("azservb01DPT_ENRCOMEDIRIMS EDI JOBS" & _

                  "REETFilesMacrosComparisonReport_new.mod")

              If openmod = True Then

                  MonarchObj.JetExportTable ("C:Documents and Settings" & ClipNull(GetUser()) & "My Documentssp" & 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.

      Christina

        • VBA access issue
          Christina_db _

          Forgot to add that I and other users get this error but there are 2 users that do not get this error. The program works fine for them. They have the same version of Monarch and same Windows CP and Microsoft 2003. Are there specific components to look for that they might have and others including myself do not have?

           

          Thank you.

          Christina

          • VBA access issue
            mclark _

            Hi Christina,

            What is Monarch.tlb in the GetObject statement? Is that something that the two users have that the other users don't? What does the Active X error say? Michele

            • VBA access issue
              Christina_db _

              Hi Michele,

               

              The ActiveX error is "Run-Time Error '429': ActiveX component can't create object." And the Monarch.tlb is the Monarch object I referenced based off of the forum string "Visual Basic App Bombs" but it still didn't work. All the users have Monarch on their computers.

               

              Thank you.

              • VBA access issue
                Christina_db _

                I found out additional info to hopefully have someone help me with this. The users that do not get the run time error have admin rights on their computer. Is there something in how this was installed to require functions to be done with admin rights? Would this require re-installing Monarch differently? Any help is appreciated!

                 

                Thank you.

                • VBA access issue
                  RalphB _

                  Christina,

                   

                  Monarch has had 2 versions of Monarch, Standard and Pro since Monarch 6.  Which version is everyone running? The JetExportTable command will only work with the Pro versions, V6 Pro, V7 Pro and V8 Pro.

                   

                  If you have the Standard versions, you will need to use the ExportTable command instead.

                   

                  Ralph

                  • VBA access issue
                    Christina_db _

                    All users have Monarch Pro 7.00.

                     

                    Thanks.

                    • VBA access issue
                      Nick Osdale-Popa

                      Hi Christina,

                      Your SET command should be the one you have commented out, with a slight adjustment:

                       

                      [font="courier"]Set MonarchObj = GetObject("Monarch32")[/font][/quote]Monarch.tlb is not the Monarch object, but a reference file to the Monarch object, in other words, you would use it for early binding.  In Tools|References, you would set a reference to the monarch.tlb file and thus you could set an object directly to Monarch with early binding as such:

                      [font="courier"] Dim MonarchObj as Monarch[/font][/quote]So to sum up,

                      For late binding (which I commonly use):

                      [font="courier"]Set MonarchObj = GetObject("Monarch32")[/font][/quote]For early binding (direct reference):

                      Set a reference to monarch.tlb, in code use:

                      [font="courier"] Dim MonarchObj as Monarch[/font][/quote]Hope that clarifies things.