Cells.Formula = QUOT; = CONCATENATE（...）"异常0x800A03EC [英] Cells.Formula="=CONCATENATE(...)" Exception 0x800A03EC

问题描述

我正在编写一个生成Excel文件的VB.NET应用程序。

我的意图是写一个使用 CONCATENATE 在单元格中。

现在，以下代码行触发了以下异常：

  0）tSheet.Cells（tIncRow + ItemIndex * PixelIndex + PixelIndex，2）.Formula = 
= CONCATENATE（&像素（PixelIndex）& ;批次！J3）
  

以下行不会引发异常。 （这只是上面没有 = 的行，事实上它不引发异常意味着索引被正确使用;我会摆脱他们在以下段落中以缓解符号）。此外，如果我手动将Excel中的一个 = 在同一个公式前面，那么公式会得到正确的结果（它正确地抓住批处理！ J3 ）

  1）tSheet.Cells（tIncRow + ItemIndex * PixelIndex + PixelIndex，2）。公式= 
CONCATENATE（&像素（PixelIndex）&;批次！J3）
  

以下行也运行没有问题：

  2）tSheet.Cells（indexes）.Formula == CONCATENATE （&像素（PixelIndex）&）
  

此行也同样适用： p>

  3）tSheet.Cells（indexes）.Formula == CONCATENATE（Batches！J3）
  

似乎只有2）和3）的组合引发了异常。

我正在使用Visual Studio 2012，Excel 2007，我包括Microsoft Excel 12.0对象库

感谢任何帮助！

解决方案

哟你没有提到你的地区，所以只要Excel预期参数分隔符是逗号（如在英国语言环境中）而不是分号（如de-DE语言环境），那么尝试：

  tSheet.Cells（tIncRow + ItemIndex * PixelIndex + PixelIndex，2）.Formula = 
= CONCATENATE（&像素（PixelIndex）& ，批次！J3）
  

（编辑）如果这样工作，那么你可以使用以下使其区域设置独立：

  Dim ci As CultureInfo = System.Globalization.CultureInfo.CurrentCulture 
 Dim listSep As String = ci.TextInfo.ListSeparator 
 tSheet.Cells（tIncRow + ItemIndex * PixelIndex + PixelIndex，2）.Formula = 
= CONCATENATE（&像素（PixelIndex）& listSep&批量！J3）
  

只需从Windows文化中获取数字列表分隔符。它确实假定Excel以 System.Globalization.CultureInfo.CurrentCulture 运行，有关此更多详细信息和您可能遇到的特殊情况，请参阅以下两个MSDN参考： p>

• 创建Excel解决方案在多个国家/地区使用Visual Studio Tools for Office （特别是编写代码以检查区域设置部分）


• 创建办公室在多个国家/地区使用的解决方案

（结束编辑）

如果这不行，那么我们可能需要更多的信息 - 请参阅我对原始问题的意见。

I'm writing a VB.NET application that generates an Excel file.

My intention here is to write a particular formula that uses CONCATENATE in a cell.

Now, the following line of code fires the above exception:

0) tSheet.Cells(tIncRow + ItemIndex * PixelIndex + PixelIndex, 2).Formula = 
   "=CONCATENATE(" & Pixels(PixelIndex) & ";Batches!J3)"

The following row does NOT raise the exception. (It's simply the row above without the = at the beginning. The fact that it doesn't raise the exception means that the indexes are used properly; I'll get rid of them in the following passages to ease the notation). Also, if I manually put in Excel an = in front of the very same formula, then the formula gives a correct result (it correctly grabs Batches!J3)

1) tSheet.Cells(tIncRow + ItemIndex * PixelIndex + PixelIndex, 2).Formula = 
   "CONCATENATE(" & Pixels(PixelIndex) & ";Batches!J3)"

The following line also runs without problem:

2) tSheet.Cells(indexes).Formula = "=CONCATENATE(" & Pixels(PixelIndex) & ")"

This line works as well:

3) tSheet.Cells(indexes).Formula = "=CONCATENATE(Batches!J3)"

It seems that only the combination of 2) and 3) raises the exception.

I'm using Visual Studio 2012, Excel 2007, and I'm including Microsoft Excel 12.0 Object Library

Thanks for any help!

解决方案

You haven't mentioned your locale, so just in case Excel is expecting argument separators to be comma (as in the en-UK locale) instead of semicolon (as in de-DE locale) then try:

tSheet.Cells(tIncRow + ItemIndex * PixelIndex + PixelIndex, 2).Formula = 
    "=CONCATENATE(" & Pixels(PixelIndex) & ",Batches!J3)"

(edit) If that does work, then you can use something like the following to make it locale independent:

Dim ci As CultureInfo = System.Globalization.CultureInfo.CurrentCulture
Dim listSep As String = ci.TextInfo.ListSeparator
tSheet.Cells(tIncRow + ItemIndex * PixelIndex + PixelIndex, 2).Formula =
    "=CONCATENATE(" & Pixels(PixelIndex) & listSep & "Batches!J3)"

That just grabs the numeric list separator from the windows culture. It does assume that Excel runs with System.Globalization.CultureInfo.CurrentCulture, see the following two MSDN references for more detailed information on this and the special cases you may encounter:

• Creating Excel Solutions for Use in Multiple Countries/Regions Using Visual Studio Tools for Office (espcially the "Writing Code to Check Regional Settings" section)
• Creating Office Solutions for Use in Multiple Countries/Regions

(end edit)

If this doesn't work, then we might need more info - see my comments on your original question.

这篇关于Cells.Formula = QUOT; = CONCATENATE（...）"异常0x800A03EC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！