Is Excel Object.CopyFromRecordset still supported in VB.NET??

  • Thread starter Thread starter al
  • Start date Start date
A

al

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
 
Grawsha,
Of course it is still supported from VB.NET. ;-)

To use Excel's CopyFromRecordset you would need an ADODB.Recordset in your
VB.NET program that you pass as the parameter to CopyFromRecordset, just
like from VB6 or VBA.

Now are you asking does Excel have a CopyFromDataset method, that you can
use with a .NET dataset? Not really, however depending on how current a
version of Excel you have, I would simply save the dataset as an XML file,
then use Excels ability to read/link to an XML file to retrieve the data.

Hope this helps
Jay
 
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),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

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
 
Back
Top