Solved

Run-time 91 error, not sure why

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

777 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