Solved

Error Code 91 with VB6 and Excel

Posted on 2014-04-08
24
733 Views
Last Modified: 2014-04-15
Two of the projects I am working on are in VB6. It allows the user to lookup data from an access database and output results to Excel. We recently upgraded from Office 2007 to Office 2010 ever since that we are now getting the following error when you try to output to Excel.

Error Code 91

Our error text is "Error Creating Excel Spreadsheet"

Within VB6 references we are using “Microsoft Excel 5.0 Object Library”. On my development machine the application works every time. Once I try to open it on a Windows 7 office 2010 machine I get the error. However when it was Office 2007 it worked fine and no changes have been made to the code. Any ideas on a fix?



Below are many steps I have had to take or use to get Excel to work after updating to office 2010.

I had used the below two links to resolve a macro issue I was having with VB6 and Excel.
The link below explains the issue and the resolution.
http://support.microsoft.com/kb/926430

You will need to go to the link below to download the hotfix or you can use the hotfix files in this directory ((\converter\vba) that I have already downloaded.
http://support.microsoft.com/hotfix/KBHotfix.aspx?kbnum=926430&kbln=en-us

Removing Outdated References to Office from the System Registry
http://kb.palisade.com/index.php?pg=kb.page&id=528

Click the + sign at the left of {00020813-0000-0000-C000-000000000046} to expand it. You will see one or more subkeys: 1.5 for Excel 2003, 1.6 for Excel 2007, 1.7 for Excel 2010, 1.8 for Excel 2013. Identify the one(s) that do not match the version(s) of Excel you actually have installed.

HKEY_CLASSES_ROOT\Typelib\{00020813-0000-0000-C000-000000000046}

HKEY_CLASSES_ROOT\Typelib\{00020813-0000-0000-C000-000000000046}\1.7\0\win32
Value Name: (Default)
Value data: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE

We are using Lync 2013 so in the 1.8 key we had to add this for excel and it fixed one of our issues.
HKEY_CLASSES_ROOT\Typelib\{00020813-0000-0000-C000-000000000046}\1.8\0\win32
Value Name: (Default)
Value data: C:\Program Files (x86)\Microsoft Office\Office14\EXCEL.EXE
0
Comment
Question by:iamtgo3
  • 13
  • 11
24 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39985794
The text for run-time error 91 should be:
Object variable or With block variable not set

The code could take a different path for a number of reasons, so it would help to see what the code is at the point and what leads up to it.
0
 
LVL 1

Author Comment

by:iamtgo3
ID: 39985859
  If Not TypeOf grd Is SSDBGrid Then
    Err.Raise vbObjectError + 5, "CEXcel - CopySheridanGrid", "CopySheridanGrid method only valid for Sheridan DB Grid"
  End If
  
  'Set Default values for optional parameters
  If IsMissing(iStartRow) Then iStartRow = 1
  If IsMissing(iStartCol) Then iStartCol = 1
  If IsMissing(bIncludeColHeaders) Then bIncludeColHeaders = True
  If IsMissing(sSheetName) Then sSheetName = grd.Name
  
  'Set sheet name, ignore any errors
  On Error Resume Next
  m_oExcel.ActiveSheet.Name = sSheetName
  
  On Error GoTo CopySheridanGrid_Err
  
  iExcelRow = iStartRow
  
  'Add column headers to spreadsheet
  frmStatusBox.Display "Creating Headers...", "Output to Excel", , bCancel

' ==================
'  Below is where the code block errors out
'==================

  If bIncludeColHeaders Then
    iExcelCol = iStartCol - 1
    For iCol = 0 To grd.Cols - 1
      'only copy columns that are visible in the grid
      If grd.Columns(iCol).Visible And grd.Columns(iCol).Width > 0 Then
        iExcelCol = iExcelCol + 1
        With m_oExcel.ActiveSheet
          .Cells(iExcelRow, iExcelCol).Font.Bold = True
          .Cells(iExcelRow, iExcelCol).Value = grd.Columns(iCol).Caption
          'Size the column
          .Columns(iExcelCol).AutoFit
        End With
      End If
    Next iCol
  End If

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39985967
Which line is the error on?

If you don't know because of the error handler, then comment out the On Error lines so that the code breaks on the execution line?
0
 
LVL 1

Author Comment

by:iamtgo3
ID: 39986111
I believe it on the With. The hard part to pinpoint the error is it work perfectly on the development machine. When I removed the Error Trapping I got a error: Object variable or With block variable not set.

        With m_oExcel.ActiveSheet 'I believe the error is this line
          .Cells(iExcelRow, iExcelCol).Font.Bold = True
          .Cells(iExcelRow, iExcelCol).Value = grd.Columns(iCol).Caption
          'Size the column
          .Columns(iExcelCol).AutoFit
        End With

Open in new window

0
 
LVL 1

Author Comment

by:iamtgo3
ID: 39986284
I tried a different approach replacing above With block with below code still no success. It works great on the development machine but not on the users system.

        For Each z In m_oExcel.Application.Sheets
           If z.Name = sSheetName Then
              z.Cells(iExcelRow, iExcelCol).Font.Bold = True
              z.Cells(iExcelRow, iExcelCol).Value = grd.Columns(iCol).Caption
              z.Columns(iExcelCol).AutoFit
           End If
        Next

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39986402
So the object 'm_oExcel' isn't set. Is this a local variable supposed to be set in the procedure, is it passed in, or is it a global?

It is presumably an Excel application object variable so is probably set with GetObect or CreateObject functions.
0
 
LVL 1

Author Comment

by:iamtgo3
ID: 39986460
m_oExcel is a module level variable

Option Explicit

Dim m_oExcel As Excel.Application

Open in new window


  'If Excel is not open then start it
  'frmStatusBox.Display "Opening Spreadsheet...", "Output to Excel", , bCancel
  If Not ExcelOpen Then
    Start
    AddWorkBook
  End If

Open in new window



Public Sub Start()
  '
  'Open Microsoft Excel

  On Error GoTo Start_Err:
  
  'If Excel is currently running, get current instance.
  On Error Resume Next
  Set m_oExcel = GetObject(, "Excel.Application")
 
  If m_oExcel Is Nothing Then
    'if couldn't find currently running instatnce then create a new one
    Set m_oExcel = CreateObject("Excel.Application")
  Else
    m_bPreviouslyRunning = True
  End If
       
  Exit Sub
  
Start_Err:

  Err.Raise vbObjectError + 1, "CExcel - Start", "Unable to Open Microsoft Excel"
  
End Sub

Open in new window


Public Function AddWorkBook()
  '
  'Create a new workbook
  '
  
  If ExcelOpen Then
  
    m_oExcel.Workbooks.Add
    
  End If
  
End Function

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39986690
If the code to create the object is executed successfully, then it seems that something else is destroying it.

I suggest that you create a Watch of

m_oExcel is Nothing

and set it to break when the value changes
0
 
LVL 1

Author Comment

by:iamtgo3
ID: 39986915
I did what you said but also check m_oExcel right before it executes the code causing the error and it is still set to "Microsoft Excel". So the code should work just fine. It goes through the code of activesheet below with out errors. Not sure but just seems to not like the "With m_oExcel.ActiveSheet", "With m_oExcel.ActiveWorkbook.Sheets(sSheetName)" or "For Each z In m_oExcel.Application.Sheets"

  'Set sheet name, ignore any errors
  On Error Resume Next
  m_oExcel.ActiveSheet.Name = sSheetName

Open in new window


Variable still has value
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39986973
Ah, OK. We seem to be getting there. The error is also set if there is no ActiveWorkbook.
It might be that your current version of Excel doesn't automatically create a workbook when  the application is created programatically, or that the new workbook isn't automatically active.

Personally in full projects I don't relay on which workbook or worksheet is active, but work directly on the objects themselves. You can capture the workbook when it is created and return it to the calling procedure:
Sub MyProcedure()
'...
Dim xlWbk As Excel.Workbook
Dim xlWks As Excel.Worksheet
'...
Set xlWbk = AddWorkBook(m_oExcel)
Set xlWks = xlWbk.Sheets(1)
'...
        With xlWks
          .Cells(iExcelRow, iExcelCol).Font.Bold = True
          .Cells(iExcelRow, iExcelCol).Value = grd.Columns(iCol).Caption
          'Size the column
          .Columns(iExcelCol).AutoFit
        End With
End Sub

Open in new window

Public Function AddWorkBook(xlApp as Excel.Application) as Excel.Workbook
  '
  'Create a new workbook
  '
  
  If ExcelOpen Then
  
    Set AddWorkBook = xlApp .Workbooks.Add
    
  End If
  
End Function

Open in new window

0
 
LVL 1

Author Comment

by:iamtgo3
ID: 39988710
I have tried what you posted and it works on development system but errors out on "Set xlWks = xlWbk.Sheets(1)" Error 91 Object variable or With block variable not set. the sheets or worksheets seem to keep causing the issue.

  Dim iCol As Integer
  Dim iRow As Integer
  Dim bkmrk As Variant
  Dim iExcelCol As Integer
  Dim iExcelRow As Integer
  Dim sValue As String
  Dim i As Integer
  Dim bCancel As Boolean
  
    Dim myResponse
  
  'On Error GoTo CopySheridanGrid_Err
  
  'If Excel is not open then start it
  'frmStatusBox.Display "Opening Spreadsheet...", "Output to Excel", , bCancel
  If Not ExcelOpen Then
    Start
    'AddWorkBook
  End If
    
'Testing what is gets through before error    
myResponse = MsgBox("Excel Started", vbInformation, "Error")

Dim xlWbk As Excel.Workbook
Dim xlWks As Excel.Worksheet

'Testing what is gets through before error    
myResponse = MsgBox("Excel worksheet and workbook Started", vbInformation, "Error")

Set xlWbk = AddWorkBook(m_oExcel)

'Testing what is gets through before error    
myResponse = MsgBox("Excel workbook set", vbInformation, "Error")

Set xlWks = xlWbk.Sheets(1) ' It is erroring out on this now. I have tried Set xlWks = xlWbk.Worksheets(1) as well
    
'Testing what is gets through before error    
myResponse = MsgBox("Excel worksheet set", vbInformation, "Error")
   
    
  If Not TypeOf grd Is SSDBGrid Then
    Err.Raise vbObjectError + 5, "CEXcel - CopySheridanGrid", "CopySheridanGrid method only valid for Sheridan DB Grid"
  End If
  
  'Set Default values for optional parameters
  If IsMissing(iStartRow) Then iStartRow = 1
  If IsMissing(iStartCol) Then iStartCol = 1
  If IsMissing(bIncludeColHeaders) Then bIncludeColHeaders = True
  If IsMissing(sSheetName) Then sSheetName = grd.Name
  
  'Set sheet name, ignore any errors
  On Error Resume Next
  'm_oExcel.ActiveSheet.Name = sSheetName
  xlWks.Name = sSheetName
  
'Testing what is gets through before error    
myResponse = MsgBox("Excel sheet set Started", vbInformation, "Error")
  
    'On Error GoTo CopySheridanGrid_Err
  
  iExcelRow = iStartRow
  
  'Add column headers to spreadsheet
  frmStatusBox.Display "Creating Headers...", "Output to Excel", , bCancel

'Testing what is gets through before error    
myResponse = MsgBox("Entering 1st loop", vbInformation, "Error")

  If bIncludeColHeaders Then
    iExcelCol = iStartCol - 1
    For iCol = 0 To grd.Cols - 1
      'only copy columns that are visible in the grid
      If grd.Columns(iCol).Visible And grd.Columns(iCol).Width > 0 Then
        iExcelCol = iExcelCol + 1
        With xlWks
          .Cells(iExcelRow, iExcelCol).Font.Bold = True
          .Cells(iExcelRow, iExcelCol).Value = grd.Columns(iCol).Caption
          'Size the column
          .Columns(iExcelCol).AutoFit
        End With
      End If
    Next iCol
  End If
    

'Testing what is gets through before error    
myResponse = MsgBox("Loop2", vbInformation, "Error")

    
  'Move to the top of the grid
  grd.MoveFirst
  'Loop through each row
  For iRow = 0 To grd.Rows - 1
    iExcelRow = iExcelRow + 1
    'Set the current row
    bkmrk = grd.GetBookmark(iRow)
    'loop though each column
    iExcelCol = iStartCol - 1
    For iCol = 0 To grd.Cols - 1
      'only copy columns that are visible in the grid
      If grd.Columns(iCol).Visible And grd.Columns(iCol).Width > 0 Then
        iExcelCol = iExcelCol + 1
        With xlWks 'm_oExcel.ActiveSheet
          sValue = grd.Columns(iCol).CellText(bkmrk)
          'all numeric strings greater than 10 characters should be treated as strings
          'by placing a single apostrophy in front of the string, will tell excel will treat it as a string.
          If IsNumeric(sValue) And Len(sValue) > 10 Then
            sValue = "'" & sValue
          End If
          .Cells(iExcelRow, iExcelCol).HorizontalAlignment = xlLeft
          .Cells(iExcelRow, iExcelCol).Value = sValue
        End With
      End If
    Next iCol
    frmStatusBox.Display "Exporting...", "Output to Excel", , bCancel, , , iRow, grd.Rows
    If bCancel Then Exit For
  Next iRow
 
 'Size the columns again to the width of the data
  For i = 1 To iExcelCol
    'm_oExcel.ActiveSheet.Columns(i).AutoFit
    xlWks.Columns(i).AutoFit
  Next i
 
 'Freeze the headers
  If bIncludeColHeaders Then
    'm_oExcel.ActiveSheet.Cells(iStartRow + 1, iStartCol).Activate
    xlWks.Cells(iStartRow + 1, iStartCol).Activate
    m_oExcel.ActiveWindow.FreezePanes = True
    
  End If
 
  Unload frmStatusBox
  m_oExcel.Visible = m_bVisible
  If m_oExcel.Application.WindowState = xlMinimized Then
    m_oExcel.Application.WindowState = xlNormal
  End If

  Exit Sub
  
'CopySheridanGrid_Err:
  
  'Err.Raise Err, "CExcel - CopySheridanGrid", "Error creating excel spreadsheet."

Open in new window


Public Function AddWorkBook(xlApp As Excel.Application) As Excel.Workbook

  '
  'Create a new workbook
  '
  
  If ExcelOpen Then
  
     Set AddWorkBook = xlApp.Workbooks.Add
    
  End If
  
End Function

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39988794
Perhaps the workbook is being created without any sheets. See if this helps.
If xlWbk.Sheets.Count > 0 Then
    Set xlWks = xlWbk.Sheets(1) ' It is erroring out on this now. I have tried Set xlWks = xlWbk.Worksheets(1) as well
Else
    Set xlWks = xlWbk.Sheets.Add
End If

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 1

Author Comment

by:iamtgo3
ID: 39988833
Could it be that the user systems are missing a reference or DLL? What is the difference between Dev system and user system that it does not work. All the code you have posted are good ideas. If I get rid of all the Sheet information it works on user system. However that makes what the original developer was doing pointless.

When I am looking at the task manager the Excel.exe process starts and then unloads. Same issue I had explained above on other VB6 apps I am fixing. However I have added all the updates that fixed that I believe.
0
 
LVL 1

Author Comment

by:iamtgo3
ID: 39989241
If I change it up and try to open a Excel file it works on the development machine but not on user system. Excel opens then immediately closes. I get a Error Code: 430 "Class does not support Automation or does not support expected interface" At this point I feel like I can rule out the code. I think it is something different with user system compared to dev system. VB6 reference missing something any ideas?

myResponse = MsgBox("Excel Starting", vbInformation, "Error")

    Set xlApp = Excel.Application


myResponse = MsgBox("Excel worksheet and workbook Started", vbInformation, "Error")

Set xlWbk = xlApp.Workbooks.Open(App.Path & "\Book1.xls")
'Set xlWbk = AddWorkBook(m_oExcel)

myResponse = MsgBox("Excel workbook set", vbInformation, "Error")

Set xlWks = xlWbk.Worksheets("Product List")

Open in new window

0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39989268
I am trying to think how the code could get as far as it does but still have missing references. References are needed to support early binding. If a reference is completely absent, I would expect a compile-time error (User-defined type not defined) on the declaration line such as

Dim xlApp as Excel.Application

If a reference in a project is to an earlier version, VB normally picks up the later library and uses that. As a general rule later versions are designed to be compatible with earlier versions.

Though we all have our own coding standards, the code seems to be well formed. As indicated earlier, I personally try to avoid activating the word document and by the same token try not to use the Selection object (which isn't in any of the code that you have posted, anyway).

What I try even harder to avoid is 'On Error Resume Next'. That could postpone the discovery of a problem and make the original cause difficult to determine. There is one in the code, and it isn't clear why.

All that being said, we are trying to eliminate any cause of the immediate symptom that you report, which  now seems to be that a workbook has no sheets. I don't have 2010 to test it, but I wouldn't have expected that behaviour to be different. Hopefully the code in the last suggestion will either be a workaround or get us a bit closer to the actual problem.
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39989276
My last comment may be out-of-date. I typed it several hours ago, but neglected to submit it
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39989295
That can be caused by a mismatch between the Referenced library and the DLL it calls.

See if late binding, which doesn't use a library, helps.
Dim xlApp As Object
Dim xlWbk As Object
Dim xlWks As Object
myResponse = MsgBox("Excel Starting", vbInformation, "Error")

    Set xlApp = Excel.Application


myResponse = MsgBox("Excel worksheet and workbook Started", vbInformation, "Error")

Set xlWbk = xlApp.Workbooks.Open(App.Path & "\Book1.xls")
'Set xlWbk = AddWorkBook(m_oExcel)

myResponse = MsgBox("Excel workbook set", vbInformation, "Error")

Set xlWks = xlWbk.Worksheets("Product List")
                          

Open in new window

0
 
LVL 1

Author Comment

by:iamtgo3
ID: 39989320
GrahamSkan I am seeing the same characteristics I seen in another application where it opens excel and then immediately closes it. So until it tries to add data to the actual instance it just accept all is good.
  If Not ExcelOpen Then
    Start
    AddWorkBook
  End If

  m_oExcel.ActiveSheet.Name = sSheetName

Open in new window

It may actually error out earlier if there was not a “On Error Resume Next”. I agree with you on that. I did not originally write this code but unfortunately stuck fixing it now. We all love working on bad code we did not create don’t we.
0
 
LVL 1

Author Comment

by:iamtgo3
ID: 39989373
I have tried your late bind. Excel opens then immediately closes. I get a Error Code: 430 "Class does not support Automation or does not support expected interface" I just can not put my finger on it we have tried 4 different approaches and still have an issue. This works fine on the development system though.
0
 
LVL 76

Assisted Solution

by:GrahamSkan
GrahamSkan earned 250 total points
ID: 39989624
It might be that Excel 5.0 object library is too big a jump and it isn't clear where you have ended up following the links that you posted in the question.

Which version are you currently using?
0
 
LVL 1

Accepted Solution

by:
iamtgo3 earned 0 total points
ID: 39992270
GrahamSkam

Ok after two days of nothing I stop and thought about automation error. You were on to something with your last post on too big of a jump. However we have a lot of old Excel files here and the Excel 5.0 object library is the reference I needed for those. This app is not opening any existing Excel documents rather creating from Office 2010 or v14.0. So I started looking at that. It turns out with VB6 I had to browse for the two references I needed for Excel 2010. First I had to delete "Excel 5.0 object library" reference then add the two below.

"Microsoft Excel 14.0 Object Library" in C:\Program Files (x86)\Microsoft Office\Office14\Excel.exe
"Microsoft Office14.0 Object Library" in C:\Program Files (x86)\Common Files\microsoft shared\OFFICE14\MSO.DLL
0
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 39992349
I'm sorry that I didn't pick that up earlier. I read it as information of the history of the problem.

Until recently, the Excel 5.0 library was usually available as well as the latest, such as 12.0 (in my case), but I always ignored it. It was there to support Excel 5.0, released in 1993 and the first version to support VBA; and hence the first such library.

Since then the internal and external numbering systems diverged. My 2007 version is matched by the version 12.0 .

The main point is that the version needs to match the current application, rather than the original format of the documents (workbooks).

I take it that things are now working.
0
 
LVL 1

Author Comment

by:iamtgo3
ID: 39993250
Yes things are now working. Well I kind of did a good job of overwhelming you with information and did not structure so well. I think support Excel 5.0 object library or Excel 5.0 ended when Office 2010 came out and broke a lot of our applications. So I have all but one now fixed.

Thanks for all the great code and ideas. Every single one of them worked on my dev system since I have everything from VB6 to VS2013 installed with 2 office versions.
0
 
LVL 1

Author Closing Comment

by:iamtgo3
ID: 40001131
I selected my own solution because this is what ultimately fixed my issue. Thanks to GrahamSkan working with me I was able to find the right solution for my problem.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 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

13 Experts available now in Live!

Get 1:1 Help Now