Exporting to Excel Using 2005 Express

[Ken Tucker [MVP]]

Hi,

Stop using late binding and you will get a better error message.

Dim oExcel As New Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet

oExcel = new excel.application

Ken
-----------------------
Hi
I've found a code which should work, but it doesn't.
Please Help

Imports Microsoft.Office.Interop ' Refence in the Class

Dim oExcel As New Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.add ' The Error Occurs here
'Create an array with 3 columns and 100 rows.
Dim DataArray(99, 2) As Object
Dim r As Integer
For r = 0 To 99
DataArray(r, 0) = "ORD" & Format(r + 1, "0000")
DataArray(r, 1) = Rnd() * 1000
DataArray(r, 2) = DataArray(r, 1) * 0.07
Next
'Add headers to the worksheet on row 1.
oSheet = oBook.ActiveSheet
oSheet.Range("A1").Value = "Order ID"
oSheet.Range("B1").Value = "Amount"
oSheet.Range("C1").Value = "Tax"
'Transfer the array to the worksheet starting at cell A2.
oSheet.Range("A2").Resize(100, 3).Value = DataArray
'Save the Workbook and quit Excel.
oBook.SaveAs("Book2.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()

Thank you
Gal

 

[Gal Zilberman]

Hi
I've found a code which should work, but it doesn't.
Please Help

Imports Microsoft.Office.Interop ' Refence in the Class

Dim oExcel As New Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.add ' The Error Occurs here
'Create an array with 3 columns and 100 rows.
Dim DataArray(99, 2) As Object
Dim r As Integer
For r = 0 To 99
DataArray(r, 0) = "ORD" & Format(r + 1, "0000")
DataArray(r, 1) = Rnd() * 1000
DataArray(r, 2) = DataArray(r, 1) * 0.07
Next
'Add headers to the worksheet on row 1.
oSheet = oBook.ActiveSheet
oSheet.Range("A1").Value = "Order ID"
oSheet.Range("B1").Value = "Amount"
oSheet.Range("C1").Value = "Tax"
'Transfer the array to the worksheet starting at cell A2.
oSheet.Range("A2").Resize(100, 3).Value = DataArray
'Save the Workbook and quit Excel.
oBook.SaveAs("Book2.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()

Thank you
Gal

 

[Herfried K. Wagner [MVP]]

You're absolutely right, but it still doesn't work.
I get: "Old format or invalid type library." Error
at the 'oBook = oExcel.Workbooks.add' line

BUG: "Old Format or Invalid Type Library" Error When Automating Excel 2002
<URL:http://support.microsoft.com/?scid=kb;en-us;320369>

 

[Bernie Yaeger]

Hi Gal,

You're still going to have trouble closing out, as in .net you must call
marshal.releasecomobject(<object>).

Here's some code that works fine for me, using a datatable to create an
Excel .csv or .xls:
Public Function sqltabletocsvorxls(ByVal dt As DataTable, ByRef strpath As
String, ByVal dtype As String, ByVal includeheader As Boolean) As Integer

' signature:

' dim funcs as new imcfunctionlib.functions

' dim xint as integer

' xint = funcs.sqltabletocsvorxls(dsmanifest.tables(0),mstrpath,
"csv",false)

' where mstrpath = , say, "f:\imcapps\xlsfiles\test.xls"

sqltabletocsvorxls = 0

Dim objxl As Excel.Application

Dim objwbs As Excel.Workbooks

Dim objwb As Excel.Workbook

Dim objws As Excel.Worksheet

Dim mrow As DataRow

Dim colindex As Integer

Dim rowindex As Integer

Dim col As DataColumn

Dim fi As FileInfo = New FileInfo(strpath)

If fi.Exists = True Then

Kill(strpath)

End If

objxl = New Excel.Application

'objxl.Visible = False ' i may not need to do this

objwbs = objxl.Workbooks

objwb = objwbs.Add

objws = CType(objwb.Worksheets(1), Excel.Worksheet)

' i many want to change this to pass in a variable to determine

' if i want to have a column name row or not

If includeheader Then

For Each col In dt.Columns

colindex += 1

objws.Cells(1, colindex) = col.ColumnName

Next

rowindex = 1

Else

rowindex = 0

End If

Dim fixedstring As String

For Each mrow In dt.Rows

rowindex += 1

colindex = 0

For Each col In dt.Columns

colindex += 1

fixedstring = mrow(col.ColumnName).ToString().Replace(vbCrLf, "")

'objws.Cells(rowindex, colindex) = mrow(col.ColumnName).ToString()

If col.DataType.ToString = "System.String" Then

objws.Cells(rowindex, colindex) = "'" & fixedstring

Else

objws.Cells(rowindex, colindex) = fixedstring

End If

Next

Next

If dtype = "csv" Then

objwb.SaveAs(strpath, xlCSV)

Else

objwb.SaveAs(strpath)

End If

objxl.DisplayAlerts = False

objwb.Close()

objxl.DisplayAlerts = True

Marshal.ReleaseComObject(objws)

objxl.Quit()

Marshal.ReleaseComObject(objxl)

objws = Nothing

objwb = Nothing

objwbs = Nothing

objxl = Nothing

End Function

HTH,

Bernie Yaeger

 

[Gal Zilberman]

Hi Ken

You're absolutely right, but it still doesn't work.
I get: "Old format or invalid type library." Error
at the 'oBook = oExcel.Workbooks.add' line

Gal

 

[Gal Zilberman]

Thank you
Now I just need to find the best workaround for this bug

 

[Ken Tucker [MVP]]

Hi,

Try using the office xp pia.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/odc_oxppias.asp

Ken

 

[Gal Zilberman]

Thanks, I already have it.
It's a Regional thing, My PC has a different region the US and that's what
causing the problem, as soon as I change my region to US it works fine.
I need to get the workaround to work.

thanks anyway

Gal

Hi,

Try using the office xp pia.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnoxpta/html/odc_oxppias.asp

Ken

 

[Gal Zilberman]

Yes!!!
I finally have it working
The code in the link has an extra line that needs to be deleted

Thanks for your help

Gal