Why does my code work with the latest version of excel but fails with excel 2000

Question

0.00/5 (No votes)

See more:

The following code works on my PC using Excel 10 but fails on the club laptop which has Excel 2000.

What I have tried:

The code where it fails is

VB

' Start Excel and get Application object.
     Dim folder As String
     folder = GlobalVariables.Path

     oXL = CreateObject("Excel.Application")
     'oXL.Visible = True

     ' Get a new workbook.
     oWB = oXL.Workbooks.Add
     oSheet = oWB.ActiveSheet
     'Inserting the header picture
     oSheet.Range("A1").EntireColumn.ColumnWidth = 8.43
     oSheet.Range("B1").EntireColumn.ColumnWidth = 20.14
     oSheet.Range("C1").EntireColumn.ColumnWidth = 8.43
     oSheet.Range("D1").EntireColumn.ColumnWidth = 11.86
     oSheet.Range("E1,H1").EntireColumn.ColumnWidth = 7.57
     oSheet.Rows(1).RowHeight = 69
     oSheet.Rows(2).RowHeight = 15
     oSheet.Rows(3).RowHeight = 31.5
     oSheet.Rows(4).RowHeight = 15


     oSheet.Shapes.AddPicture(folder & "JubHeader.jpg",
          MsoTriState.msoFalse,
          MsoTriState.msoCTrue, 0, 0, 445.9, 68.33)

     ' Set default font type and size.
     oRng = oSheet.Range("A1", "H1000")
     With oRng.Font
         .Name = "Calibri"
         .Bold = False
         .Size = 11
         .Strikethrough = False
         .Superscript = False
         .Subscript = False
         .OutlineFont = False
         .Shadow = False
         .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleNone
         '.ColorIndex = xlAutomatic
     End With
     oRng = oSheet.Range("A1", "H1")
     oRng.MergeCells = True
     oRng = oSheet.Range("A3", "H3")
     oRng.MergeCells = True
     oRng.Value = "Medford Competition"

The error message is

C#

************** Exception Text **************
System.Runtime.InteropServices.COMException (0x80020011): Does not support a collection. (Exception from HRESULT: 0x80020011 (DISP_E_NOTACOLLECTION))
   at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)
   at Microsoft.Office.Interop.Excel.Range.set_Value(Object RangeValueDataType, Object Param)
   at Seniors_Golf_Games.MainMenu.Populate_Medford_Print_Form() in D:\My Documents on D\Visual Studio Projects\Seniors Golf Games\Seniors Golf Games\MainMenu.vb:line 640

Line 640 is the last line in the code above "oRng.Value = "Medford Competition"". It suggests it does not like putting a value in a merged cell.

Thanks in advance for any help."

Posted 20-Feb-16 6:42am

Dave the Golfer

Updated 21-Feb-16 23:47pm

Add a Solution

2 solutions

Solution 1

Did you try to store the value in the first cell A3 instead of the merged cells ?

VB

oSheet.Range("A3").Value = "Medford Competition"

Posted 20-Feb-16 7:11am

Patrice T

Updated 20-Feb-16 7:42am

v2

Comments

Sascha Lefèvre 20-Feb-16 13:31pm

That's more of a comment than a solution if you're not sure that it will solve the issue.

Patrice T 20-Feb-16 13:44pm

Better this way ?

CHill60 20-Feb-16 13:50pm

5'd ... VBA behaviour with merged cells is not good and has definitely changed between 2000 and 2010

Patrice T 20-Feb-16 15:16pm

Thank you

Dave the Golfer 21-Feb-16 12:16pm

ppolymorphe
Tried your suggestion but got the same result.
Certainly looks like Excel 2000 is different to Excel 10.

Patrice T 21-Feb-16 12:21pm

Did you try to assign the value before merging the cells.

Dave the Golfer 21-Feb-16 13:16pm

I change the code to
oRng = oSheet.Range("A1", "H1")
oRng.MergeCells = True
oRng = oSheet.Range("A3", "H3")
'oRng.Value = "Medford Competition"
oSheet.Range("A3").Value = "Medford Competition"
oRng.MergeCells = True
It failed with same error message on the last line.

Maciej Los 21-Feb-16 15:39pm

Try this: oRange.Cells(1,1).Value = "Whatever"

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-02-21T23:47:00

Solution 2

Please, read this article: Achieving Backward Compatibility with .NET Interop: Excel as Case Study[^]

An author of above article states that:

Quote:
[System.Runtime.InteropServices.COMException] -2147352559 (80020011) Does not support a collection.
The above code produces this error when compiled on an Excel 2003 machine and run on Excel 2000

There are three ways to work around this error:

Build your project on different machines for compatibility with each respective version of Office.
Install different versions of Office on your build machine (How-to).
Use "Late Binding" to call whatever version of Office is available at run-time

BTW: To merge cells, use Range.Merge()[^] method.

Finally, use this code to add text to merged range:

VB

oRange= oSheet.Range("A3:H3")
oRange.Cells(1,1).Value = "Whatever"

as is described here: Range.MergeCells Property (Excel)[^]
It inserts text into top-left cell of merged area.

[EDIT]
Late binding version of the part of your code:

VB.NET

oApp = CreateObject("Excel.Application") 'do not add specific version!
You have to repeat this step for every other object:
'oWbk = oApp.Workbooks.Open("path")
oWbk = oApp.Workbooks.Add
'oWsh = oWbk.Worksheets("wshName")
oWsh = oWbk.Worksheets(1) 'first sheet in the new workbook
oRange = oWsh.Range("A1")
oRange.EntireColumn.ColumnWidth = 8.43
'...
oWsh.Rows(1).RowHeight = 69
'...
oWhs.Shapes.AddPicture(folder & "JubHeader.jpg", 0, -1, 0, 0, 445.9, 68.33)
'Note: CTrue is not supported!
'...

Shapes.AddPicture method (Microsoft.Office.Interop.Excel)[^]
MsoTriState Enumeration [Object Library Reference for the 2007 Microsoft Office System][^]
XlUnderlineStyle Enumeration [Excel 2007 Developer Reference][^]

Good luck!

Posted 21-Feb-16 23:47pm

Maciej Los

Updated 22-Feb-16 4:37am

v2

Comments

Dave the Golfer 22-Feb-16 8:57am

Thanks Maciej Los.
I was going to try late binding but that has produced errors associated with adding pictures to excel sheet and underlining and alignment.
I also have just added Visual Studio to the laptop so I can work on the machine with Excel 2000. I have referenced the project to Excel 9.0 Object Library.
The Class contains the following imports
Imports Microsoft.Office.Core
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel

but I get Excel.Application etc declared as not defined. Do not know what to import for Excel 9.0 Object Library.

Maciej Los 22-Feb-16 10:27am

Remove all references to MS Excel. Now change the code to "late binding" version, which means you can not create objects this way:
oApp = Excel.Application
but this way:
oApp = CreateObject("Excel.Application") 'do not add specific version!
You have to repeat this step for every other object:

oWbk = oApp.Workbooks.Open("path")
oWsh = oWbk.Worksheets("wshName")
oWbk = oWsh.Range("A1")

Then replace all Excel specific enumeration with its constants
Excel.XlUnderlineStyle.xlUnderlineStyleNone => -4142
More at: XlUnderlineStyle Enumeration

Dave the Golfer 22-Feb-16 10:34am

Thanks for that . I have been looking for the enumeration values for the excel constants. Now I have them all I shall recode to late binding.

Maciej Los 22-Feb-16 10:39am

You're very welcome.
See updated answer. ;)
Can you accept it as an answer (green button) - formally to remove the question from unanswered list?

Dave the Golfer 22-Feb-16 11:27am

Maciej Los
I have set all the constants and all seems to be working at this stage.
But and there is always a but.
I want to save the excel sheet as a PDf file.
In early binding my code is
osheet.ExportAsFixedFormat(Excel.xlFixedFormatType.xlTypePDF, Path & "\" & Filename, Excel.XlFixedFormatQuality.xlQualityStandard, True, True, 1, 10, False)
the late binding code I used is
osheet.ExportAsFixedFormat(dbTypePDF, Path & "\" & Filename, dbQualityStandard, True, True, 1, 10, False)
dbTypePDF is a constant as is dbQualityStandard both set to 0 (zero) but it failed.
Error message states Public member 'ExportAsFixedFormat' on type 'Worksheet' not found.
Am I right in thinking 'ExportAsFixedFormat' is a function of the Excel Interop?

Maciej Los 22-Feb-16 11:45am

ExportAsFixedFormat function is not accesible in Excel up to 2007 version. So, you can't save worksheet as pdf if Excel version is less then 2010, but you can send a worksheet on pdf printer (if it's installed).
How to work around it? You have to check the version of currently installed Excel application and depending on it to call proper function.
See: Application.Version property

Dave the Golfer 22-Feb-16 12:09pm

Maciej Los
I am working with Excel 2000. so as you state ExportAsFixedFormat will not work. I have a Microsoft to PDF virtual printer loaded on the laptop so I will use that.

Thanks for your considerable help.

Maciej Los 22-Feb-16 12:20pm

Can you accept my solution as an answer (green button) - formally to remove the question from unanswered list?

Maciej Los 22-Feb-16 12:26pm

Thank you and you're very welcome ;)
Cheers,
Maciej