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 48

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 48

Expert Comment

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

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

691 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