473,729 Members | 2,146 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Export table in excel format

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.ExecuteNonQue ry()

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

Thanks
Sam
Nov 20 '05 #1
6 18571
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.ExecuteNonQue ry()

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
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.ExecuteNonQue ry()

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
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 CopyFromRecords et 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**********@s wissonline.ch> wrote in message news:<VA.000084 59.0065695d@spe edy>... 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.ExecuteNonQue ry()

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
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
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.x ls' '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**********@s wissonline.ch> wrote in message news:<VA.000084 65.0053ecae@spe edy>...
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
5128
by: Colin Graham | last post by:
I have a little problem i was wondering if anyone could help. I have created a table in Access and it has the following format. UniqueID FieldResult 100 1,3,5,7,23,7,8,9,12,4 103 12,6,,6,4,8,12,10,19 My problem is that i need to export the table into excel and then in excel the uniqueID should become a column and each value in the FieldResult section should become a column. e.g.
2
4697
by: PerryC | last post by:
Is there a way to export an MS Access Report to Word/Excel that looks EXACTLY like it appears in Access? When I export to Excel, only certain data appears, titles, headings... all missing. The format is not aligned... When export to Word, all my lines (table lines) are gone, making it hard to read. Thanks. Perry
4
33465
by: Hans [DiaGraphIT] | last post by:
Hi! I want to export a dataset to an excel file. I found following code on the net... ( http://www.codeproject.com/csharp/Export.asp ) Excel.ApplicationClass excel = new ApplicationClass(); excel.Application.Workbooks.Add(true); DataTable table = DATASETNAME.Tables;
2
3992
by: Siu | last post by:
Hi, I use the following code to export and import a file Excel from resp. into a Web page with the following code: //EXPORT Response.Clear(); Response.Buffer = true; Response.ContentType = "application/vnd.ms-excel"; Response.Charset = ""; this.EnableViewState = false;
13
13234
by: Hemant Sipahimalani | last post by:
The following piece of code is being used to export HTML to excel. HttpContext.Current.Response.ContentType = "application/vnd.ms-excel" HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=ABC.xls") HttpContext.Current.Response.Write(strHTML) HttpContext.Current.Response.End() However when the user tries to save it the Default File Type is Web Page(*.htm; *.html)
1
2181
by: shankumar | last post by:
Hai Evrybody, I have one problem, that is i design a ASP report getting data from MS SQL Server 2000 and displays it as a desinged format and also "one option to export this same format to MS EXCEL". Before i seen it, but i dont know its code. pls anybody tell or sent the its codings pls my Mail Id :
3
3046
by: excyauseme | last post by:
Hi guys! Do you know what is the best way to export a text file, this one is a log file that is already comma delimited thru a module run by my access database, to an excel spreadsheet? I need to do this thru the module, and I don't see how I can export a txt file from access without loading it to a temp table first. I've checked out the DoCmd.TransferText methods, where you have to have a table or query or schema ini first, and the...
3
4051
by: ggupta78 | last post by:
Hi, Currently in our application we use the MHTML way of exporting to Excel i.e we render the datagrid HTML and set the MIME type to Excel. This works great but has the following limitation: 1. File size is extremely large in some cases 2. Our clients using older versions of Excel or OpenOffice 2.0 cannot open these files. So, we would want to export to excel using the BIFF format. But from
3
7158
by: =?Utf-8?B?YzY3NjIyOA==?= | last post by:
Hi all, I have a question for you. I have a .csv file which has many lines of data. Each line has many data fields which are delimited by ",". Now I need to extract part of data from this file but save it as an excel file. The data in this excel file will be imported into an Access database. The
0
8766
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9291
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9218
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8163
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6722
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6026
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4536
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4799
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2171
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.