Good question?
-----Original Message-----
From: Unicorn.PC.Support [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 26, 2004 11:06 AM
To: [EMAIL PROTECTED]
Subject: Re: [vbhelp] Vb.Net - Export Dataset to Excel
Now this is not a criticism of Ben's solution, but could someone offer a
suggestion how this is better or faster than the pure SQL solution that
works only for Jet databases? example below
Note.... SQL server info following code
Dim cnn As New ADODB.Connection
Dim strSQL As String
cnn.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\DB1.mdb;" & _
"Jet OLEDB:Engine Type=4;"
'''Export statement
strSQL = "SELECT * INTO [Text;DATABASE=e:\My Documents\TextFiles].[Plot.txt]
FROM [tblPlot]"
'''
'''Import statement
strSQL = "SELECT * INTO [tblPlot] FROM [Text;DATABASE=e:\My
Documents\TextFiles].[Plot.txt]"
'''
cnn.Execute strSQL
cnn.Close
BTW if you are using MSDE/SQL server you might want to look at the BCP
utility
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/coprompt/cp
_bcp_61et.asp
The some purpose of this little bugger is bulk copy of data into and out of
SQL server.
Then you could look at the Com services of the DTS package
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/dts_overview.asp
Notes on Vb development using DTS
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtsprog/dts
papps_2e5f.asp
The code here
http://www.sqldts.com/default.aspx?237 appears to actually do what you are
seeking to do without any coding.
Matt
----- Original Message -----
From: "Ben Rosato" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Tuesday, October 26, 2004 11:00 PM
Subject: RE: [vbhelp] Vb.Net - Export Dataset to Excel
>
> The key to the speed is not looping through the recordset itself, but
using
> the getrows and an array. Just modify this for your needs.
>
> MYARRAY = MYRST.GetRows
> MYRST.Close
>
> ExportString = ""
> For X = 0 To UBound(MYARRAY , 2)
> For I = 0 To UBound(MYARRAY , 1)
> ExportString = ExportString & MYARRAY (I, X) & ","
> Next
> Print #2, ExportString
> frmExport.Caption = "Exporting record number " & X + 1
> ExportString = ""
> DoEvents
> Next
>
> -----Original Message-----
> From: Daniel Magliola [mailto:[EMAIL PROTECTED]
> Sent: Monday, October 25, 2004 4:14 PM
> To: [EMAIL PROTECTED]
> Subject: RE: [vbhelp] Vb.Net - Export Dataset to Excel
>
>
>
> Yes, I would
>
> Thanks!
>
> > -----Mensaje original-----
> > De: Ben Rosato [mailto:[EMAIL PROTECTED]
> > Enviado el: Monday, October 25, 2004 17:13
> > Para: [EMAIL PROTECTED]
> > Asunto: RE: [vbhelp] Vb.Net - Export Dataset to Excel
> >
> >
> >
> > I have written a function that will export a recordset to .csv
> > It is pretty
> > quick. I can dump out 2000 records with 254 columns in about 2 seconds.
> > Let me know if you would like it.
> >
> > -----Original Message-----
> > From: Daniel Magliola [mailto:[EMAIL PROTECTED]
> > Sent: Monday, October 25, 2004 3:51 PM
> > To: [EMAIL PROTECTED]
> > Subject: [vbhelp] Vb.Net - Export Dataset to Excel
> >
> >
> >
> > Dear Group,
> >
> > I currently have a Dataset that is filled with the results of a SELECT
> > Stored Procedure.
> > (I created a Command for the SP, a DataAdapter for that command, and
used
> > that DataAdapter to fill the Dataset)
> >
> > Now i need to export that Dataset to Excel.
> > I want to do this as automatically as possible. I'd like to avoid the
> > solution of recursing through all the rows in the Dataset, and manually
> > writing the cells in Excel. (And this is the only solution I could come
up
> > with so far).
> >
> > If I were using Excel 2002, I could export the Dataset to an XML
> > file (I've
> > already done this), and I could import it into Excel, but I'm using
Excel
> > 2000.
> > I've also read about using an XSLT to transform the XMl data into
> > a CSV, or
> > a tab-delimited file, but the problem is that the format of the XML will
> > vary a little depending on what SP I call, and I want this to be
generic.
> >
> > If any of you can think of something for this, I'd really really
> > appreciate
> > it.
> >
> > Thanks a lot.
> >
> > Daniel Magliola
> > Cimatic SRL
> > +5411 4787 5311
> >
> >
> >
> >
> >
> > '// =======================================================
> > Rules : http://ReliableAnswers.com/List/Rules.asp
> > Home : http://groups.yahoo.com/group/vbHelp/
> > =======================================================
> > Post : [EMAIL PROTECTED]
> > Join : [EMAIL PROTECTED]
> > Leave : [EMAIL PROTECTED]
> > '// =======================================================
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> > ---
> > Incoming mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.779 / Virus Database: 526 - Release Date: 10/19/2004
> >
> > ---
> > Outgoing mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.779 / Virus Database: 526 - Release Date: 10/19/2004
> >
> >
> >
> >
> >
> >
> > '// =======================================================
> > Rules : http://ReliableAnswers.com/List/Rules.asp
> > Home : http://groups.yahoo.com/group/vbHelp/
> > =======================================================
> > Post : [EMAIL PROTECTED]
> > Join : [EMAIL PROTECTED]
> > Leave : [EMAIL PROTECTED]
> > '// =======================================================
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> >
> >
> >
> >
>
>
>
>
>
> '// =======================================================
> Rules : http://ReliableAnswers.com/List/Rules.asp
> Home : http://groups.yahoo.com/group/vbHelp/
> =======================================================
> Post : [EMAIL PROTECTED]
> Join : [EMAIL PROTECTED]
> Leave : [EMAIL PROTECTED]
> '// =======================================================
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.779 / Virus Database: 526 - Release Date: 10/19/2004
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.779 / Virus Database: 526 - Release Date: 10/19/2004
>
>
>
>
>
>
> '// =======================================================
> Rules : http://ReliableAnswers.com/List/Rules.asp
> Home : http://groups.yahoo.com/group/vbHelp/
> =======================================================
> Post : [EMAIL PROTECTED]
> Join : [EMAIL PROTECTED]
> Leave : [EMAIL PROTECTED]
> '// =======================================================
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
'// =======================================================
Rules : http://ReliableAnswers.com/List/Rules.asp
Home : http://groups.yahoo.com/group/vbHelp/
=======================================================
Post : [EMAIL PROTECTED]
Join : [EMAIL PROTECTED]
Leave : [EMAIL PROTECTED]
'// =======================================================
Yahoo! Groups Links
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.779 / Virus Database: 526 - Release Date: 10/19/2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.779 / Virus Database: 526 - Release Date: 10/19/2004
------------------------ Yahoo! Groups Sponsor --------------------~-->
$9.95 domain names from Yahoo!. Register anything.
http://us.click.yahoo.com/J8kdrA/y20IAA/yQLSAA/k7folB/TM
--------------------------------------------------------------------~->
'// =======================================================
Rules : http://ReliableAnswers.com/List/Rules.asp
Home : http://groups.yahoo.com/group/vbHelp/
=======================================================
Post : [EMAIL PROTECTED]
Join : [EMAIL PROTECTED]
Leave : [EMAIL PROTECTED]
'// =======================================================
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/vbhelp/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
