469,909 Members | 1,722 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,909 developers. It's quick & easy.

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

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
Nov 20 '05 #1
2 8196
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

"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

Nov 20 '05 #2
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

Nov 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Chris | last post: by
10 posts views Thread by Robert Hicks | last post: by
4 posts views Thread by ZR | last post: by
4 posts views Thread by John Z. | last post: by
19 posts views Thread by wreckingcru | last post: by
1 post views Thread by Waqarahmed | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.