By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,793 Members | 1,269 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,793 IT Pros & Developers. It's quick & easy.

Export table in excel format

P: n/a
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
Nov 20 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Hello,

"Sam Johnson" <no**@none.none> schrieb:
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?


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

news://msnews.microsoft.com/microsof...amework.adonet

Web interface:

http://msdn.microsoft.com/newsgroups...amework.adonet

--
Herfried K. Wagner
MVP VB Classic, VB.NET
http://www.mvps.org/dotnet
Nov 20 '05 #2

P: n/a
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.


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
:-)

Nov 20 '05 #3

P: n/a
Cindy
Putting data INTO Excel using any flavor of ADO (or DAO) is
problematical, at best.
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.
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.

Cindy Meister -WordMVP- <Ci**********@swissonline.ch> wrote in message news:<VA.00008459.0065695d@speedy>... 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.


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
:-)

Nov 20 '05 #4

P: n/a
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.
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.


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


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 :-)

Nov 20 '05 #5

P: n/a
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...;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.

Cindy Meister -WordMVP- <Ci**********@swissonline.ch> wrote in message news:<VA.00008465.0053ecae@speedy>...
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.
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.


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


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 :-)

Nov 20 '05 #6

P: n/a
Hi Onedaywhen,
You can see it's the same as the OP except it has a workbook name in
the middle.

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

Nov 20 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.