Click here to Skip to main content
14,732,615 members
Please Sign up or sign in to vote.
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

' 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

************** 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
Updated 22-Feb-16 0:47am

Did you try to store the value in the first cell A3 instead of the merged cells ?
oSheet.Range("A3").Value = "Medford Competition"
   
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"
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:
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:
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!
   
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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900