473,320 Members | 2,041 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 8803
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Chris | last post by:
Hi, I hope this is correct forum for this, this might possibly an academic type question. I am trying to copy a dataset to an excel spreadsheet for my asp.net application. (written VB). I...
10
by: Robert Hicks | last post by:
I need to pull data out of Oracle and stuff it into an Excel spreadsheet. What modules have you used to interface with Excel and would you recommend it? Robert
17
by: Ange T | last post by:
Hi there, I'm having pain with the VB behind an Access form. The form is used to create reports in Excel based on the details entered in the form. This has always worked without error on my...
3
by: deko | last post by:
This code runs okay, but I cannot quit Excel. I've read that this may be due to the way I reference the object variables, but I don't know how else I would reference them. How do I get Excel to...
4
by: ZR | last post by:
Hi, The following code used to work perfectly, this copies a recordset on to excel (it saves you having to loop through all the recs.) code : "WST.Range("A5").CopyFromRecordset(RsStyles)" ...
4
by: John Z. | last post by:
I have read numerous articles and postings on various approaches to exporting a DataSet to excel while avoiding the use of automation. I found the most performant approach to be assigning a...
19
by: wreckingcru | last post by:
I'm trying to output a SQL query that is constructed thru my VB.net GUI into an excel file. Here is the code I'm using: 'Sqlstmt is the SQL query statement 'Conn is the SQL Connection object...
0
by: keithsimpson3973 | last post by:
I have the following code to populate an excel spreadsheet from vb6 and access database. It works great except that when it reads from the recordset and populates the spreadsheet, I would like it to...
1
by: il0postino | last post by:
Apologies in advance for this newbie question! I have an Access form with an unbound embedded Excel chart on it(Called, OLEUnbound39) (Done on Access form by Insert > Object > Microsoft excel...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.