Solved

Run-time 91 error, not sure why

Posted on 2016-11-10
3
32 Views
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!
0
Comment
Question by:D.J. Johnston
  • 2
3 Comments
 
LVL 47

Accepted Solution

by:
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

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

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

End Sub

Open in new window

1
 

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!
0
 
LVL 47

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
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…

863 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now