Solved

Vb.Net - Excel 2013 Invalid Index

Posted on 2014-09-12
7
602 Views
Last Modified: 2014-10-23
Good Day Experts!

I am in a bit of a quandry here.  I have a VB.Net application with a TabControl that has multiple "Export to Excel" buttons on the Tabs.  It has worked fine for 2years on XP machines and recently Windows7 machines.  

However, we just upgraded a User to Office 2013 and now I am having trouble when trying to "Export to Excel".  Message received --> Invalid Index. (Exception from HRESULT:0x8002000B (DISP_E_BADINDEX)).  When I go to my machine which is Windows7 and Office 2010 I have no troubles.

Perhaps I need to create the objects a bit different for 2013 but I am not sure?

Do you have any ideas?

Thanks,
jimbo99999
0
Comment
Question by:Jimbo99999
  • 4
  • 3
7 Comments
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40319777
We would need more information such as a stack trace to see the line of code that is causing the exception. and the source code at that point.
0
 

Author Comment

by:Jimbo99999
ID: 40323513
Good Day Everyone!

I put some message boxes in the code by User so as to not disturb the other Users when I published.  I have narrowed it down to the line causing the error with the Office 2013 W7 machine and have bolded it below.  

Note, that this code works fine on my Office 2010 W7 machine.  

 oWB = oXL.Workbooks.Add
 oSheet = oWB.ActiveSheet
 If rbtnCashReceipts.Checked = True Then
          oSheet.Name = "Funding Report"
 Else
          oSheet.Name = "Pymt Release Report"
 End If
oSheet2 = oWB.Worksheets(2)
oSheet2.Name = "Check Details"
oSheet3 = oWB.Worksheets(3)
oSheet3.Name = "Invoice Details"

Is there a way to reference the worksheet that will work for both versions?

Thanks,
jimbo99999
0
 
LVL 15

Expert Comment

by:ChloesDad
ID: 40323683
The error is caused by there not being 3 worksheets in the spreadsheet. You could check using the Count property and then add the extra sheets if necessary.

NumberOfSheets = oWB.Count

IF NumberOfSheets >= 3 then
  oSheet2 = oWB.Worksheets(2)
ELSE
 oSheet2 = CType(oWB.Add(), Excel.Worksheet)
 NumberOfSheets =NumberOfSheets + 1
END IF

oSheet2.Name = "Check Details"

IF NumberOfSheets >= 4 then
  oSheet3 = oWB.Worksheets(3)
ELSE
 oSheet3 = CType(oWB.Add(), Excel.Worksheet)
 NumberOfSheets =NumberOfSheets + 1
END IF

oSheet3.Name = "Invoice Details"
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:Jimbo99999
ID: 40325315
Good Day!

I apologize as I did not include my declaration block...here it is:

        Dim oXL As Excel.Application
        Dim oWB As Excel.Workbook
        Dim oSheet As Excel.Worksheet
        Dim oSheet2 As Excel.Worksheet
        Dim oSheet3 As Excel.Worksheet
0
 
LVL 15

Accepted Solution

by:
ChloesDad earned 500 total points
ID: 40326440
Thats fine, but if there are not enough worksheets in the active workbook then you will get the error that you are seeing.

Just because you have sheets declared as objects doesn't mean that they are actually present in the workbook, they just exist as objects in the subroutine.

In excel you can set the number of sheets in the workbook that are created by default. This is probably what is different on the machine that has a problem.
0
 

Author Comment

by:Jimbo99999
ID: 40327966
Hello ChloesDad:

Ahhh...I gotcha.  Ok, I will check that out today.

Thanks,
jimbo99999
0
 

Author Closing Comment

by:Jimbo99999
ID: 40400486
Thanks you...it is working now!!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

856 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