Solved

Run-time 91 error, not sure why

Posted on 2016-11-10
3
23 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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 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

18 Experts available now in Live!

Get 1:1 Help Now