• VB Runtime error when opening excel

    • This topic has 14 replies, 3 voices, and was last updated 24 years ago.
    Author
    Topic
    #356575

    When some of our users open excel they get the following message;

    Run-time error ’91’:
    Object variable or With block variable not set

    Any ideas what would cause this??

    Cheers

    Lee

    Viewing 0 reply threads
    Author
    Replies
    • #528014

      Hi Lee,
      Do these users have any workbooks that open automatically when they start Excel (they’ll be in the XLSTART directory) such as a personal macro workbook? It sounds like there’s code running automatically but the code has an error in it. I guess it could also be a faulty add-in though that’s probably less likely.
      Hope that helps.

      • #528027

        Excellent it was exactly that. I removed a file called pdfwriter.xls from that directory and it fixed the problem.

        Any ideas how I can find out why this file was causing a problem?

        Thanks

        Lee

        • #528031

          When you get the error, is there a button the bottom of the message box labeled Debug? If there is, pressing that button should show the line of code causing the error. If not, then that workbook probably came from a vendor that has the code locked down so that you can not see it. From the name, it sounds like a file to create Adobe Acrobat .PDF files from Excel. However, that would not necessarily have come from Adobe. Maybe someone else will recognize the file name and tell you what vendor to contact.

          • #528035

            When i click the debug button the follwing chunk of code is displayed. The line starting “our index” is where the arrow is pointing to.

            If Not found Then
            ‘ Add the item to the File menu
            Set filePrintItem = fileMenu.FindControl(Type:=msoControlButton, Id:=4, _
            Recursive:=True)
            ourIndex = filePrintItem.Index + 1
            Set createPDFItem = fileMenu.Controls.Add(Type:=msoControlButton, _
            Before:=ourIndex, Temporary:=True)
            createPDFItem.Caption = “Create Adobe PDF…”
            createPDFItem.OnAction = “PrintPDFFile”
            createPDFItem.Tag = “CreateAdobePDF”
            End If

            • #528038

              For those users who are getting the error, check the File menu in Excel and see if Print… is still on there – if they’ve moved it (e.g. onto one of the main toolbars, then you’ll get that error. The code in the add-in tries to place a control just before the print item on the File menu and causes an error if it’s not there. Simple solution is to copy the Print… item back to the File menu (you can still leave a copy wherever the user wanted it).
              Hope that helps.

            • #528039

              I’ve just checked the machines in question.
              The print section of the file menu has the following entries;

              Print Area
              Page Setup
              Print
              Print Preview

            • #528047

              Curious. Is it the Print… item with Ctrl+P listed as the shortcut rather than just Print (i.e. the one that brings up the Print dialog box rather than the one that sends straight to the printer)? The error implies that it can’t find the control with ID = 4 (the Print… control) on the File menu.

            • #528052

              It is print with ctrl+p and it does bring up the print dialogue box

            • #528051

              Lee,
              Try running these macros in Excel:

              Sub FindID4()
                  Dim mnuFile As CommandBar
                  Dim itmFilePrint As CommandBarControl
                  Dim itmMenuItem, blnItemFound As Boolean
                  Set mnuFile = CommandBars("File")
                  For Each itmMenuItem In mnuFile.Controls
                      If itmMenuItem.ID = 4 Then
                          MsgBox "Found ID 4 at index " & itmMenuItem.Index
                          blnItemFound = True
                          Exit For
                      End If
                  Next 'itmmenuitem
                  If Not blnItemFound Then MsgBox "Control with ID 4 does not exist on File menu."
              End Sub
              Sub ListIDs()
                  Dim mnuFile As CommandBar
                  Dim itmFilePrint As CommandBarControl
                  Dim itmMenuItem, blnItemFound As Boolean
                  Set mnuFile = CommandBars("File")
                  For Each itmMenuItem In mnuFile.Controls
                      With itmMenuItem
                          MsgBox .Caption & " has ID " & .ID
                      End With
                  Next 'itmmenuitem
              End Sub
              

              The first one will tell you if a control with ID 4 exists on the file menu, the second one will display the Caption and ID of each control on the File menu so you can check what the ID of your print control is (if it’s not 4).
              Hope that helps.

            • #528055

              Rory,

              Id 4 doesn’t exist on the file menu. The print id’s are as follows;

              Print Area – ID 30255
              &Print… CTRL+P – ID 101
              Print Preview – ID 1

              Is id 4 the default for the print command on the file menu?
              Does the results mean that if I change the code to check for id 101 I will stop getting the runtime error?

              Cheers

            • #528056

              Yes – just change ID:=4 to ID:=101 on those machines (assuming they’re all the same) and the error should go away.
              Hope that helps.

            • #528060

              Thanks

              Much appreciated!

        • #528032

          Was it definitely an .xls extension rather than .xla? Adobe does provide an add-in called pdfwriter.xla, which I have on my PC here but it doesn’t cause me an error. Do the users who don’t get that error have anything similar in their XLSTART directory?

          • #528034

            It is an xla extension, it gets put there when you install the software to run a digital sender.
            I’ll try and get hold of a copy of the file from a pc that doesn’t have the error.

    Viewing 0 reply threads
    Reply To: VB Runtime error when opening excel

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: