Excel automation problem
-
Greetings all. I've made a service which creates an Excel workbook automatically using the Excel COM object. I have however got a problem that when I try to enter a formula containing the sheet names to sum across all sheets that the service crashes with a "Exception from HRESULT: 0x800A03EC" error and I'm at my wits ends right now. I know the formula is correct, because I can copy it from the code and past it into the workbook afterwards and it runs fine, but when trying to do so automatically - it fails. I create a cell range for the cell I want to write this formula in, and use the "Formula" property to set the value, and here it crashes. I've made other formulas without issues, and if I do not contain sheetnames, I can write the formula easily enough (I substituted my SUM for a SUM(1+1)) in the relevant cellrange. So the issue is solely with a formula containing sheet names for me. Has anybody else experienced something similar, and possible have a solution or a heads up where to go for one?
--------------------------- 127.0.0.1 - Sweet 127.0.0.1
-
Greetings all. I've made a service which creates an Excel workbook automatically using the Excel COM object. I have however got a problem that when I try to enter a formula containing the sheet names to sum across all sheets that the service crashes with a "Exception from HRESULT: 0x800A03EC" error and I'm at my wits ends right now. I know the formula is correct, because I can copy it from the code and past it into the workbook afterwards and it runs fine, but when trying to do so automatically - it fails. I create a cell range for the cell I want to write this formula in, and use the "Formula" property to set the value, and here it crashes. I've made other formulas without issues, and if I do not contain sheetnames, I can write the formula easily enough (I substituted my SUM for a SUM(1+1)) in the relevant cellrange. So the issue is solely with a formula containing sheet names for me. Has anybody else experienced something similar, and possible have a solution or a heads up where to go for one?
--------------------------- 127.0.0.1 - Sweet 127.0.0.1
It looks like that's a nice "generic" error returned by Excel when there is a problem with just about anything using the automation model. What does the code look like that's setting this formula?
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
It looks like that's a nice "generic" error returned by Excel when there is a problem with just about anything using the automation model. What does the code look like that's setting this formula?
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007The code is something along this line (simplified to avoid writing unnecessary function call et al) myExcelObject.CreateRange("myCellRange") myExcelObject.CellRange.Formula = "=SUM('SheetName'!Cell;'SheetName'!Cell)" I can post the more exact code if needs be, but this is the gist of it. As mentioned, I write other formulas without problems, although these do not span multiple sheets - including array formulas - so there can't really be much at fault with my base code. The problem seems to lie somewhat deeper then just the normal issues, which is why I turned to this forum. I was thinking the issue might be connected to the fact that I need to "cheat" Excel to think it is a US-EN system by changing the culture when I create the excel objects needed (Workbook, Sheets etc), and when I then try to write the SheetName's in the formula that it has an localization issue or something. But if this is the case, then I'm really without much of a hope - it just really annoys me cause I can take the fomula from the code, paste it into the generated Excel document and have it work. I just can't print it out automatically :(
--------------------------- 127.0.0.1 - Sweet 127.0.0.1
-
The code is something along this line (simplified to avoid writing unnecessary function call et al) myExcelObject.CreateRange("myCellRange") myExcelObject.CellRange.Formula = "=SUM('SheetName'!Cell;'SheetName'!Cell)" I can post the more exact code if needs be, but this is the gist of it. As mentioned, I write other formulas without problems, although these do not span multiple sheets - including array formulas - so there can't really be much at fault with my base code. The problem seems to lie somewhat deeper then just the normal issues, which is why I turned to this forum. I was thinking the issue might be connected to the fact that I need to "cheat" Excel to think it is a US-EN system by changing the culture when I create the excel objects needed (Workbook, Sheets etc), and when I then try to write the SheetName's in the formula that it has an localization issue or something. But if this is the case, then I'm really without much of a hope - it just really annoys me cause I can take the fomula from the code, paste it into the generated Excel document and have it work. I just can't print it out automatically :(
--------------------------- 127.0.0.1 - Sweet 127.0.0.1
A bit more exact would help, I mean a copy'n'paste of the real thing. The error that's returned is usually caused by an automation failure. Probably due to the code doing something it's not allowed to do. What your doing by putting the formula in a range of cells is not what your code is doing. I don't do Office Interop projects, so my experience is very limited.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007 -
The code is something along this line (simplified to avoid writing unnecessary function call et al) myExcelObject.CreateRange("myCellRange") myExcelObject.CellRange.Formula = "=SUM('SheetName'!Cell;'SheetName'!Cell)" I can post the more exact code if needs be, but this is the gist of it. As mentioned, I write other formulas without problems, although these do not span multiple sheets - including array formulas - so there can't really be much at fault with my base code. The problem seems to lie somewhat deeper then just the normal issues, which is why I turned to this forum. I was thinking the issue might be connected to the fact that I need to "cheat" Excel to think it is a US-EN system by changing the culture when I create the excel objects needed (Workbook, Sheets etc), and when I then try to write the SheetName's in the formula that it has an localization issue or something. But if this is the case, then I'm really without much of a hope - it just really annoys me cause I can take the fomula from the code, paste it into the generated Excel document and have it work. I just can't print it out automatically :(
--------------------------- 127.0.0.1 - Sweet 127.0.0.1
I have no Excel automation experience, but maybe this idea could help: you could enter the formula in Excel manually, then use automation to see how it looks... :)
Luc Pattyn [Forum Guidelines] [My Articles]
this weeks tips: - make Visual display line numbers: Tools/Options/TextEditor/... - show exceptions with ToString() to see all information - before you ask a question here, search CodeProject, then Google
-
A bit more exact would help, I mean a copy'n'paste of the real thing. The error that's returned is usually caused by an automation failure. Probably due to the code doing something it's not allowed to do. What your doing by putting the formula in a range of cells is not what your code is doing. I don't do Office Interop projects, so my experience is very limited.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007With mObjExcel .CreateRange(.GetExcelColumnName(EnuColumn.UnitPrice + mIntColumnCountFactor) & mIntColRow(intCounter) & ":" & .GetExcelColumnName(EnuColumn.UnitPrice + mIntColumnCountFactor) & mIntColRow(intCounter), mStrSheetNames(intCounter)) .CellRange.NumberFormat = "#.##0" .CellRange.Formula = "=SUM(" & strFormular & ")" .CellRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, , mObjBlack.ToInteger) End WithThe "GetExcelColumnName" is a function which just returns the Excel Column name such as A1, B2 based on a column and row number. Nothing special - just one I made to help myself. The strFormula variable is the formula mentioned which looks like 'Sheetname!Cell;Sheetname!Cell' and is build dynamically based on which actual sheetnames and cells are used. The formula works when I copy it into the generated excel document so there is not a problem with the formula as such. Just the generation of it automatically Inside my mObjExcel the CreateRange looks likeFriend Sub CreateRange(ByVal range As Object, ByVal strSheetName As String) SetCultureFixStart() ''Changes the current Culture to en-US, casue otherwise excel automation crashes If Not mObjCells Is Nothing Then ReleaseComObject(mObjCells)''cleans up COM objects which might linger in memory mObjCells = Nothing ''cleans up COM objects which might linger in memory System.GC.Collect() ''cleans up COM objects which might linger in memory mObjCells = mObjSheets(strSheetName).Range(range).Cells SetCultureFixEnd() ''sets culture back End SubAnd CellRange is just a property which returns my mObjCells (which is an Excel.Range). What strikes me as most annoying is that the above code works when I use 1+1 in the formula instead of Sheetnames!cell, or when I post out more complex formulas, even .FormulaArray work. So basically so far, I've been able to do all I wanted to, except use Sheetnames in a formula :/ Thanks so far for the feedback/time.--------------------------- 127.0.0.1 - Sweet 127.0.0.1
-
I have no Excel automation experience, but maybe this idea could help: you could enter the formula in Excel manually, then use automation to see how it looks... :)
Luc Pattyn [Forum Guidelines] [My Articles]
this weeks tips: - make Visual display line numbers: Tools/Options/TextEditor/... - show exceptions with ToString() to see all information - before you ask a question here, search CodeProject, then Google
-
With mObjExcel .CreateRange(.GetExcelColumnName(EnuColumn.UnitPrice + mIntColumnCountFactor) & mIntColRow(intCounter) & ":" & .GetExcelColumnName(EnuColumn.UnitPrice + mIntColumnCountFactor) & mIntColRow(intCounter), mStrSheetNames(intCounter)) .CellRange.NumberFormat = "#.##0" .CellRange.Formula = "=SUM(" & strFormular & ")" .CellRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, , mObjBlack.ToInteger) End WithThe "GetExcelColumnName" is a function which just returns the Excel Column name such as A1, B2 based on a column and row number. Nothing special - just one I made to help myself. The strFormula variable is the formula mentioned which looks like 'Sheetname!Cell;Sheetname!Cell' and is build dynamically based on which actual sheetnames and cells are used. The formula works when I copy it into the generated excel document so there is not a problem with the formula as such. Just the generation of it automatically Inside my mObjExcel the CreateRange looks likeFriend Sub CreateRange(ByVal range As Object, ByVal strSheetName As String) SetCultureFixStart() ''Changes the current Culture to en-US, casue otherwise excel automation crashes If Not mObjCells Is Nothing Then ReleaseComObject(mObjCells)''cleans up COM objects which might linger in memory mObjCells = Nothing ''cleans up COM objects which might linger in memory System.GC.Collect() ''cleans up COM objects which might linger in memory mObjCells = mObjSheets(strSheetName).Range(range).Cells SetCultureFixEnd() ''sets culture back End SubAnd CellRange is just a property which returns my mObjCells (which is an Excel.Range). What strikes me as most annoying is that the above code works when I use 1+1 in the formula instead of Sheetnames!cell, or when I post out more complex formulas, even .FormulaArray work. So basically so far, I've been able to do all I wanted to, except use Sheetnames in a formula :/ Thanks so far for the feedback/time.--------------------------- 127.0.0.1 - Sweet 127.0.0.1
I've made a much more simple example which also fails:
mObjExcel = New Excel.Application
mObjWorkBooks = mObjExcel.Workbooks
mObjWorkBook = mObjWorkBooks.Add
mObjSheets = mObjWorkBook.Worksheets
If mObjSheets.Count > 0 Then
For i As Integer = mObjSheets.Count To 2 Step -1
mObjSheet = mObjSheets.Item(i)
mObjSheet.Delete()
Next
mObjSheet = mObjSheets.Item(1)
Else
mObjSheet = mObjSheets.Add
End If
mObjSheet.Name = "A"
mObjSheet.Range("A1").Value = "1"
mObjSheet = mObjSheets.Add()
mObjSheet.Name = "B"
mObjSheet.Range("A1").Value = "1"
mObjSheet.Range("A2").Formula = "=SUM(A1;A!A1)"This fails, but if I substitute the formula for =SUM(1+1) it writes correct. The formula is taken directly from what Excel wrote when I made the SUM in there.
-
I've made a much more simple example which also fails:
mObjExcel = New Excel.Application
mObjWorkBooks = mObjExcel.Workbooks
mObjWorkBook = mObjWorkBooks.Add
mObjSheets = mObjWorkBook.Worksheets
If mObjSheets.Count > 0 Then
For i As Integer = mObjSheets.Count To 2 Step -1
mObjSheet = mObjSheets.Item(i)
mObjSheet.Delete()
Next
mObjSheet = mObjSheets.Item(1)
Else
mObjSheet = mObjSheets.Add
End If
mObjSheet.Name = "A"
mObjSheet.Range("A1").Value = "1"
mObjSheet = mObjSheets.Add()
mObjSheet.Name = "B"
mObjSheet.Range("A1").Value = "1"
mObjSheet.Range("A2").Formula = "=SUM(A1;A!A1)"This fails, but if I substitute the formula for =SUM(1+1) it writes correct. The formula is taken directly from what Excel wrote when I made the SUM in there.
I found a workaround which only confirms my suspecision that it must be something culture specific issue. I could instead of using a SUM formula add the cells together - and I was able to do this with the sheetnames in the addition, so instead of "SUM" I use "=SheetName!Cell+SheetName!Cell ...." and that looks to work when printed out as a .Value. Wheee. :) Thanks for the help otherwise
-
I found a workaround which only confirms my suspecision that it must be something culture specific issue. I could instead of using a SUM formula add the cells together - and I was able to do this with the sheetnames in the addition, so instead of "SUM" I use "=SheetName!Cell+SheetName!Cell ...." and that looks to work when printed out as a .Value. Wheee. :) Thanks for the help otherwise
Hmmm...very weird. I wouldn't have thought that the culture would affect the sheetname.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007