Export table in excel format

  • Thread starter Thread starter Sam Johnson
  • Start date Start date
S

Sam Johnson

HI

I tried to send the following SQL string to an open
databse, to export a table into excel format:

g.Connection = conn 'valid OleDBConnection and Command
objects
g.CommandText = "SELECT * INTO XLSTest IN 'C:\' 'Excel
8.0;' FROM Table1"
g.ExecuteNonQuery()

This does not seem to work. The app stops, and doesn't
respond any longer. Any ideas why?

Thanks
Sam
 
Hello,

Sam Johnson said:
I tried to send the following SQL string to an open
databse, to export a table into excel format:

g.Connection = conn 'valid OleDBConnection and Command
objects
g.CommandText = "SELECT * INTO XLSTest IN 'C:\' 'Excel
8.0;' FROM Table1"
g.ExecuteNonQuery()

This does not seem to work. The app stops, and doesn't
respond any longer. Any ideas why?
Click to expand...

This is a VB.NET language group. Please turn to the ADO.NET newsgroup:

news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet

Web interface:

http://msdn.microsoft.com/newsgroup...roup=microsoft.public.dotnet.framework.adonet
 
Hi Sam,

Putting data INTO Excel using any flavor of ADO (or DAO) is
problematical, at best. Search the data.ado newsgroups
through Google for contributions by Doug Laudenschlager
(from MSFT) on the topic.

Note, however, that you certainly should NOT have those
apostrophes here: C:\' 'Excel. Nor should you be using the
Excel class name, this should be a full file path to an
existing file. Excel is not a database program, it's a
spreadsheet. Certain things you simply cannot do with it,
among then creating a "fileless" table.
I tried to send the following SQL string to an open
databse, to export a table into excel format:

g.Connection = conn 'valid OleDBConnection and Command
objects
g.CommandText = "SELECT * INTO XLSTest IN 'C:\' 'Excel
8.0;' FROM Table1"
g.ExecuteNonQuery()

This does not seem to work. The app stops, and doesn't
respond any longer.
Click to expand...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail
:-)
 
Cindy
Putting data INTO Excel using any flavor of ADO (or DAO) is
problematical, at best.
Click to expand...

I disagree. At its best putting data INTO Excel using ADO is problem
free and very fast, with workbooks and worksheets are created by the
Jet provider on the fly, and all in the fraction of the time it would
take to just *open* a workbook using automation.

Sure, Excel has limitations with ADO (notably the 65536 row limit) but
I consider ADO the fastest and easiest way to get data into Excel
format. Using automation and Excel's CopyFromRecordset method (as you
recommended in another thread) also has limitations and is many times
slower.
Note, however, that you certainly should NOT have those
apostrophes here: C:\' 'Excel. Nor should you be using the
Excel class name, this should be a full file path to an
existing file.
Click to expand...

You are wrong. They are not apostrophes, they are single quotes. If
the data source is MS Jet then the OP syntax is correct, it is simply
missing the filename i.e. this would work:

SELECT * INTO XLSTest IN 'C:\MyWorkbook.xls' 'Excel 8.0;' FROM
Table1

If the workbook and/or worksheet did not exist Jet would create them.
 
Hi Onedaywhen,

I disagree. Take a close look at what I copied from the original
question: C:\''Excel

Between \ and Excel there should be no apostrophes (or single quotes,
or whatever you want to call them - that's really beside the point).
Nor do you have them in your suggested string :-)

Further to the discussion of using ADO to put data into Excel. If
you're starting with an empty workbook/worksheet, fine. But I've done
quite a bit of testing with Insert INTO for Excel, and if you're trying
to work with an existing worksheet, already containing data, all kinds
of things can go wrong. ADO -> Excel can work fine, under specific
circumstances; under others, where it works with no problems for a
database, it fails for Excel.
You are wrong. They are not apostrophes, they are single quotes. If
the data source is MS Jet then the OP syntax is correct, it is simply
missing the filename i.e. this would work:

SELECT * INTO XLSTest IN 'C:\MyWorkbook.xls' 'Excel 8.0;' FROM
Table1
Click to expand...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister
http://www.mvps.org/word
http://go.compuserve.com/MSOfficeForum

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail :-)
 
Cindy,

OK, let's have a *close* look. Here's what you copied from the
original question in your first post:

C:\' 'Excel

Here's what you said in your second post you copied:

C:\''Excel

Do you think these two strings are the same?

Here's the equivalent string taken from my first post

C:\MyWorkbook.xls' 'Excel

You can see it's the same as the OP except it has a workbook name in
the middle. So I really don't get what you mean when you say "Nor do
you have them in your suggested string"!

The single quotes (apostrophes, whatever) *are* required. Here's an
extract from KB article 295646 HOWTO: Transfer Data from ADO Data
Source to Excel with ADO:

The following example uses the alternate syntax for the IN clause:

strSQL = "SELECT * INTO [Sheet1] IN '" & App.Path & _
"\book1.xls' 'Excel 8.0;' FROM Customers"

(http://support.microsoft.com/default.aspx?scid=kb;en-us;Q295646)

As you can see, there are two quotes between the App.Path and the word
Excel, just as there are in my query as posted.

Have you tested my query? (I have and it works for me) Have you tested
my query without the quotes/apostrophes and found it works?

Using ADO and INSERT INTO, we'll just have to agree that disagree on
that one. I use it all the time without major difficulty.
 
Hi Onedaywhen,
You can see it's the same as the OP except it has a workbook name in
the middle.
Click to expand...
I see it, now. I agree single quotes are required, but I was under the
mistaken impression that Sam was trying to use them somehow within the
"file name". But of course it simply wasn't there, and my eyes/brain
tried to interpret the class name as an attempt at the file name.

Cindy Meister
 
Back
Top