Hi Gal,
You're still going to have trouble closing out, as in .net you must call
marshal.release comobject(<obje ct>).
Here's some code that works fine for me, using a datatable to create an
Excel .csv or .xls:
Public Function sqltabletocsvor xls(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.sqltablet ocsvorxls(dsman ifest.tables(0) ,mstrpath,
"csv",false )
' where mstrpath = , say, "f:\imcapps\xls files\test.xls"
sqltabletocsvor xls = 0
Dim objxl As Excel.Applicati on
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(strpat h)
If fi.Exists = True Then
Kill(strpath)
End If
objxl = New Excel.Applicati on
'objxl.Visible = False ' i may not need to do this
objwbs = objxl.Workbooks
objwb = objwbs.Add
objws = CType(objwb.Wor ksheets(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.Column Name).ToString( ).Replace(vbCrL f, "")
'objws.Cells(ro windex, colindex) = mrow(col.Column Name).ToString( )
If col.DataType.To String = "System.Str ing" Then
objws.Cells(row index, colindex) = "'" & fixedstring
Else
objws.Cells(row index, colindex) = fixedstring
End If
Next
Next
If dtype = "csv" Then
objwb.SaveAs(st rpath, xlCSV)
Else
objwb.SaveAs(st rpath)
End If
objxl.DisplayAl erts = False
objwb.Close()
objxl.DisplayAl erts = True
Marshal.Release ComObject(objws )
objxl.Quit()
Marshal.Release ComObject(objxl )
objws = Nothing
objwb = Nothing
objwbs = Nothing
objxl = Nothing
End Function
HTH,
Bernie Yaeger
"Gal Zilberman" <ga***********@ tns-teleseker.com> wrote in message
news:eo******** ******@TK2MSFTN GP12.phx.gbl...
Hi
I've found a code which should work, but it doesn't.
Please Help
Imports Microsoft.Offic e.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("E xcel.Applicatio n")
oBook = oExcel.Workbook s.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.ActiveShe et
oSheet.Range("A 1").Value = "Order ID"
oSheet.Range("B 1").Value = "Amount"
oSheet.Range("C 1").Value = "Tax"
'Transfer the array to the worksheet starting at cell A2.
oSheet.Range("A 2").Resize(1 00, 3).Value = DataArray
'Save the Workbook and quit Excel.
oBook.SaveAs("B ook2.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
Thank you
Gal
