strange error in excel

strange error in excel

• wcottee

  
  'Trying to automate excel in VB.Net
  'opening a workbook
  'i was first block with a easy-to-find-out problem
  
  app.Workbooks.Add() 'This bug
  
  'As microsoft says :Ref: http://support.microsoft.com/default.aspx scid=kb;en-us;320369
  'BUG: "Old format or invalid type library" error when automating Excel
  ' SYMPTOMS
  'If you automate Microsoft Excel with Microsoft Visual Basic .NET, Microsoft Visual C# .NET, or Microsoft Visual C++, you may receive the following error when calling certain methods:
  'Error: 0x80028018 (-2147647512)
  'Description: Old Format or Invalid Type Library
  'Back to the top
  ' CAUSE
  'You receive this error calling an Excel method when the following conditions are true:
  '? The method requires an LCID (locale identifier).
  '? You run an English version of Excel. However, the regional settings for the computer are configured for a non-English language.
  
  'I changed to :
  Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
  System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
  app.Workbooks.Add() 'Now Ok
  System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
  
  'Ok, this work now
  
  'Later, trying to get selected cell to update :
  
  Public Sub DisplayData(ByVal dData As Double)
  Try
  Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet = app.Workbooks(1).ActiveSheet
  
  Dim rg1 As Microsoft.Office.Interop.Excel.Range = xlSheet.Range("A1")
  Dim rg2 As Microsoft.Office.Interop.Excel.Range = app.ActiveCell
  rg1.Value = dData 'this line bug
  rg2.Value = dData 'this line bug
  
  Dim rg3 As Microsoft.Office.Interop.Excel.Range = app.ActiveCell.CurrentRegion.Select() 'this line bug
  Dim rg4 As Microsoft.Office.Interop.Excel.Range = xlSheet.Range(1, 1) 'this line bug
  Catch ex As Exception
  Stop
  End Try
  End Sub
  
  'I Changed to :
  Public Sub DisplayData(ByVal dData As Double)
  Try
  Dim oldCI As System.Globalization.CultureInfo = System.Threading.Thread.CurrentThread.CurrentCulture
  System.Threading.Thread.CurrentThread.CurrentCulture = New System.Globalization.CultureInfo("en-US")
  
  Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet = app.Workbooks(1).ActiveSheet
  Dim rg1 As Microsoft.Office.Interop.Excel.Range = xlSheet.Range("A1")
  Dim rg2 As Microsoft.Office.Interop.Excel.Range = app.ActiveCell
  
  rg1.Value = dData 'Now OK
  rg2.Value = dData 'Now OK
  
  Dim rg3 As Microsoft.Office.Interop.Excel.Range = app.ActiveCell.CurrentRegion.Select() 'Still bug
  Dim rg4 As Microsoft.Office.Interop.Excel.Range = xlSheet.Range(1, 1) 'Still bug
  
  System.Threading.Thread.CurrentThread.CurrentCulture = oldCI
  Catch ex As Exception
  Stop
  End Try
  End Sub
  
  ' I'm on Office PIA 2003, Office 2003, using dotnet VS2005
  'After too much spent time, i'll stay on this solution, but i don't like those problems..
  
  
  

• Anders ScSa

  Another attempt to get this right, this time by consulting the documentation.

  http://msdn.microsoft.com/library/default.asp url=/library/en-us/dv_wrcore/html/wrtskhowtorefertoworksheetrangesincode.asp

  Use the Offset property of a range to retrieve a range relative to the original range; the following example adds content to the area under the cell at row 1, column 1:

  ' Visual Basic
  rng = DirectCast(ws.Cells(1, 1), Excel.Range)
  
  Dim i As Integer
  For i = 1 To 5
    rng.Offset(i, 0).Value = i.ToString
  Next
  

  
  
  

• pravee

  Try

  oSheet.Cells(nRowNum + 1, 1).EntireRow.Value2 = "";

  or

  oSheet.Cells.Rows(1).Value2 = ""

  
  
  

• Venu123321

  I had these same two completely unacceptable and useless errors. Neither of the fixes posted here solved my problem. Converting everything that I was setting the cell values to, to a string, fixed my problem. Seems pretty ridiculous that setting the cell value to an integer as I was would cause such *** errors. Oh well, its what I've come to expect using the office interops.
  
  
  

• gagan5961

  I have finally found the real reason of my problem. It was the language settings for my operating system. I have polish operating system and english office - this difference makes the problem.

  There is a workaround for this kind of problems at:

  http://msdn.microsoft.com/library/default.asp url=/library/en-us/odc_vsto2003_ta/html/odc_VSTMultCR.asp

  Thanks anyway for helping, Misha

  Zbigniew Kawalec

  
  

• mowali75

  Misha,

  Thanks for your reply, but I am getting the same error.

  Regards

  
  
  

• tmn

  I believe this one is pretty easy to solve

  use this
  excelObj.Cells(1, 2).Value2 = "something"

  
  
  

• Dmitriy_Nikonov_MSFT

  Unfortinatelly, after applying your changes I get this exception:

  System.Runtime.InteropServices.COMException (0x800A03EC): Exception from HRESULT: 0x800A03EC.
     at Microsoft.VisualBasic.CompilerServices.LateBinding.InternalLateSet(Object o, Type& objType, String name, Object[] args, String[] paramnames, Boolean OptimisticSet, CallType UseCallType)
     at Microsoft.VisualBasic.CompilerServices.LateBinding.LateSetComplex(Object o, Type objType, String name, Object[] args, String[] paramnames, Boolean OptimisticSet, Boolean RValueBase)

   

  Zbigniew Kawalec

  
  

• davidakos

  Hi all,

  I have one line of code which tries to set a value in a cell of excell sheet.

  oSheet.get_Range(nRowNum + 1, 1).EntireRow.Value2 = "";

  This line gives error even if I set the Culture of my thread to en-US. It throws a COM Exception as above.

  Please help.

  Thanks in advance.

  Regards,

  Anil

  
  
  
• strange error in excel