Run-time 91 error, not sure why

Posted on 2016-11-10
Last Modified: 2016-11-10
Hi everyone.  I'm trying to change the formatting of a MS Excel spreadsheet via MS Access using VBA, but I'm running into an error every other time the code runs.  When I run the code the first time it works fine, but if I try a second time I get "Run-time error '91': Object variable or With block variable not set"

The code I'm using is below.  All the variables here appear to be named and defined properly so I'm not sure why this is happening:

Sub FormatSheets()

Dim xl As Object
Dim xlWkbk As Object
Dim xlsheet As Object

Set xl = Excel.Application
Set xlWkbk = xl.ActiveWorkbook
Set xlsheet = xlWkbk.Sheets("Sheet1")

With xlsheet
    xlsheet.Name = "rename"
End With

Set xl = Nothing
Set xlWkbk = Nothing
Set xlsheet = Nothing

End Sub

When I try debugging, it tells me the error lies on line "Set xlsheet = xlWkbk.Sheets("Sheet1")"  Does anyone have any guess as to what the problem is here?

Thanks in advance for your help!
Question by:D.J. Johnston
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 47

Accepted Solution

Dale Fye (Access MVP) earned 500 total points
ID: 41882751
Excel is very particular about the way things get opened and closed. You need to close the objects in the reverse order of the way you opened them.  When you set the XL object, you should use the GetObject method to set a reference to an open Excel instance.  If Excel is not open, then you will get an error #429, which will take you to the error handler where you open Excel and then open the workbook with the name you passed into the procedure.  The error handler then returns control to the subsequent line which sets xlWkbk to the Active workbook.  Somewhere in there, you need to set the visible properly of the XL object to True, if you want to see the workbook you are working in.

Sub FormatSheets(FileName as string)

Dim xl As Object
Dim xlWkbk As Object
Dim xlsheet As Object

    On Error Goto ProcError

    Set xl = GetObject(, "Excel.Application")
    Set xlWkbk = xl.ActiveWorkbook
    Set xlsheet = xlWkbk.Sheets("Sheet1")

    With xlsheet
        xlsheet.Name = "rename"
    End With

    On Error resume next
    Set xlsheet = Nothing
    Set xlWkbk = Nothing
    Set xl = Nothing
    Exit Sub

    If err.number = 429 then
        Set xl = CreateObject("Excel.Application")
        xl.visible = true
        Set xlWkbk = xl.openWorkbook(FileName)
        Resume Next
        msgbox err.number & vbcrlf & err.description
        debug.print err.number, err.description
        resume ProcExit
    end if

End Sub

Open in new window


Author Closing Comment

by:D.J. Johnston
ID: 41882773
Thanks!  Changed the line for setting xl to xl = GetObject(, "excel.application") and changed the order in which the variables were closed.  Both of these items combined solved the problem.  

As for the ProcError coding that you suggested, it may not be necessary in my case.  Before I run this code, I have an initial piece of code in a separate module that opens excel, makes it visible, and transfers in my data.  That initial code then calls this code to run the formatting in my spreadsheet; therefore, excel will always already be open when this code is running.  

Either way, the process error coding is good information to have for the future.  Thanks again for your help!
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41882781
glad I could help

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question