Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Jul 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VB Runtime error when opening excel

    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

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VB Runtime error when opening excel

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Jul 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Runtime error when opening excel

    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

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Runtime error when opening excel

    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.
    Legare Coleman

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VB Runtime error when opening excel

    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?
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Lounger
    Join Date
    Jul 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Runtime error when opening excel

    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.

  7. #7
    Lounger
    Join Date
    Jul 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Runtime error when opening excel

    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

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VB Runtime error when opening excel

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Lounger
    Join Date
    Jul 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Runtime error when opening excel

    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

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VB Runtime error when opening excel

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VB Runtime error when opening excel

    Lee,
    Try running these macros in Excel:
    <pre>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
    </pre>

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Lounger
    Join Date
    Jul 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Runtime error when opening excel

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

  13. #13
    Lounger
    Join Date
    Jul 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Runtime error when opening excel

    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

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: VB Runtime error when opening excel

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Lounger
    Join Date
    Jul 2002
    Location
    Melbourne, Victoria, Australia
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VB Runtime error when opening excel

    Thanks

    Much appreciated!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •