Hi Grawsha,
I use the following function to copy a dataset/datatable to either .csv or
..xls using vb .net and ado .net:
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),mstr path,
"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
For Each mrow In dt.Rows
rowindex += 1
colindex = 0
For Each col In dt.Columns
colindex += 1
objws.Cells(rowindex, colindex) = mrow(col.ColumnName).ToString()
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
"al" <gr*********@yahoo.com> wrote in message
news:66*************************@posting.google.co m...
Greetings,
I'm wondering if Excel object CopyFromRecordset is still supported in
VB.NET?? If not, what is the alternative, looping through dataset????
MTIA,
Grawsha
