为什么我的代码使用最新版本的excel但是使用excel 2000失败了 [英] Why does my code work with the latest version of excel but fails with excel 2000

问题描述

下面的代码在我的电脑上使用Excel 10，但在带有Excel 2000的俱乐部笔记本电脑上失败。



我尝试了什么：



失败的代码是

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是上面代码中的最后一行oRng.Value =Medford Competition。它表明它不喜欢在合并的单元格中放置一个值。



提前感谢任何帮助。

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."

推荐答案

您是否尝试将值存储在第一个单元格 A3 而不是合并的单元格中？

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

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

请阅读这篇文章：实现与.NET Interop的向后兼容性：Excel作为案例研究 [ ^ ]



上述文章的作者声明：

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）做不支持集合。

上面的代码在Excel 2003机器上编译并在Excel 2000上运行时会产生此错误

[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

有解决此错误的三种方法：

• 在不同的计算机上构建项目，以便与各个版本的Office兼容。
• 在构建计算机上安装不同版本的Office（操作方法）。
• 使用Late Binding调用运行时可用的任何Office版本





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



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



如下所述：Range.MergeCells Property（Excel） [ ^ ]

它将文本插入合并区域的左上角单元格。





代码部分的后期绑定版本：




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


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方法（Microsoft.Office.Interop.Excel） [ ^ ]

MsoTriState Enumeration  [对象库R. 2007 Microsoft Office System的参考资料] [ ^ ]

XlUnderlineStyle枚举  [Excel 2007开发人员参考] [ ^ ]



祝你好运！



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!




这篇关于为什么我的代码使用最新版本的excel但是使用excel 2000失败了的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！