ADO + EXCEL
Andy_HR
can i use ado to load excel (i know i can do this part :-) ) but.. then it
checks what sheets are available and then ask the user witch sheet they want
loaded to the grid? ....
app will be probably using excel 2000 to 2003 :-) and maybe even 97..... i
want to cover all areas...
it will be used for reading data that distribution centers send to the
importer and it will contain name of product, products on stock ....
i have to load to grid first to give user ability to change data as neede
(if they sent some products to distribution center in meantime) ...
and then again from grid save to sql database ...
i need user to check the sheets cose some firms have multiple distribution
centers and they send for all distribution centers in one xls file on
difrent sheets...
Andy..
tnx for all the help...
Ryszard Gawron [MS]
there are couple of articles which can be helpful:
http://support.microsoft.com/?id=278973
http://support.microsoft.com/?id=295646
Be also aware of:
http://support.microsoft.com/?id=314763
Best regards
Ryszard Gawron
Microsoft Developer Support
This posting is provided "AS IS" with no warranties, and confers no
rights.
"Andy_HR" <andy_no_spam_here@remove_this_xnet.hr> wrote in message
news:ccemat$dii$1...@news1.xnet.hr...
Jamie Collins
> can i use ado to load excel (i know i can do this part :-) ) but.. then it
> checks what sheets are available and then ask the user witch sheet they want
> loaded to the grid?
An Excel 'table' can be one of the following:
1) a worksheet,
2) a worksheet-level defined name ('named range')
3) a workbook-level defined name
4) a range address e.g. [Sheet1$A1:D10].
You can use the ADODB.Connection object's OpenSchema method with the
adSchemaTables enum to return a recordset of table names of type #1,
#2 and #3.
If you are only interested in tables of type #1, you will have to
parse the TABLE_NAME field for values that end with the $ sheet
indicator character. This is complicated by the fact that $ can
legitimately appear within an Excel sheet name (but not a defined
Name) and if the sheet name contains certain characters such as a
space then the sheet name *and* $ sheet indicator will be enclosed in
quotes which will need to be escaped (and yes, a quote is also a valid
character in an Excel sheet name!).
Try the code here:
http://groups.google.com/groups?threadm=b8c9d0b7.0405190707.50e2f35b%40posting.google.com
If you were only interested in tables of type #3 it would be a lot
simpler: use the ODBC Excel driver (doesn't work with the OLE DB
driver for Jet because of a bug) and specify the literal "Table" in
the Criteria argument e.g.
Set adRs = adCn.OpenSchema(20, _
Array(Empty, Empty, Empty, "Table"))
but that's another story <g>.
Jamie.
--
Andy_HR
colums that are in excel in grid in difrent order then they are in the excel
sheet... i now have line
fgData.AddItem sData
it imports data to the grid it adds three colums from the excel to the grid
but it starts from the first colum... i need it to start from the second...
and the tird colum needs to be empty and then data goes to forth...
how could i do this?
andy
"Jamie Collins" <jamiec...@xsmail.com> wrote in message
Andy_HR
i found the code to read sheet names from the excel file :-) here
if anyone needs it...
its few lines off code and reads name of the sheet with spaces & " ......
andy
Andy_HR
options....
i found a way... if its the wrong way (but its working) please correct me..
Dim varImport As Variant
varImport = Split(sData, vbTab)
fgImport.AddItem " " & vbTab & varImport(0) & vbTab & varImport(1) &
vbTab & " " & vbTab & varImport(2)
"Andy_HR" <andy_no_spam_here@remove_this_xnet.hr> wrote in message
Douglas Laudenschlager [MS]
-Doug
USING ADO AND ADO.NET WITH EXCEL: Resources and Known Issues
June 2003
General
-------
Q326548 HOW TO: Use Jet OLE DB Provider 4.0 to Connect to ISAM Databases
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q326548
Q257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q257819
Q303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q303814
Q278973 SAMPLE: ExcelADO Shows How to Read/Write Data in Excel Workbooks
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q278973
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q195951
Transferring Data into Excel
----------------------------
Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q247412
Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q295646
Q246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q246335
Q319951 HOW TO: Transfer Data to Excel by Using SQL Server DTS
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q319951
Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q306125
Known Issues
------------
Q319998 BUG: Memory Leak When You Query Open Excel Worksheet with ADO
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q319998
Q316809 BUG: No ADO Connection Error When Excel Workbook Is Not Found
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q316809
Q314763 FIX: ADO Inserts Data into Wrong Columns in Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q314763
Q316475 PRB: "Operation Must Use an Updateable Query" Error Message
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q316475
Q300948 BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q300948
Q294410 ACC2002: Nulls Replaced w/ Next Field's Data Exporting to Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q294410
Q293828 BUG: Excel File Size Grows When You Edit ADO Recordset
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q293828
Q288343 BUG: Excel ODBC Driver Disregards FirstRowHasNames/HDR Setting
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q288343
Q246167 PRB: Collating Sequence Error Opening XLS as ADO Recordset
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q246167
Q211378 XL2000: 'Could Not Decrypt File' Error with Password-Protected File
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q211378
Q194124 PRB: Excel Values Returned as NULL Using DAO OpenRecordset (mixed
data types)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q194124
Q189897 XL97: Data Truncated to 255 Characters with Excel ODBC Driver (Rows
To Scan)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q189897
Known Issues - .Net-specific
----------------------------
Q316831 PRB: Cannot Configure Data Connection to Excel Files in VS .NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q316831
Q316756 PRB: Error w/ ADO.NET OLEDbDataAdapter to Modify Excel Workbook
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q316756
VB.Net
------
Q318373 HOW TO: Use GetOleDbSchemaTable with Excel in Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q318373
Q316934 HOW TO: Use ADO.NET to Retrieve and Modify Records in Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q316934
Q306022 HOW TO: Transfer Data to Excel Workbook Using Visual Basic .NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q306022
Q302094 HOWTO: Fill/Retrieve Excel Data Using Arrays From VB .Net
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q302094
Web/ASP
-------
Q319180 HOWTO: Transform Dataset to Spreadsheet XML for Microsoft Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q319180
Q276488 HOWTO: Use ADODB.Stream to Read Binary Files to the Browser
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q276488
Q257757 INFO: Considerations for Server-Side Automation of Office
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q257757
Q199841 HOWTO: Display ASP Results Using Excel in IE with MIME Types
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q199841
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q195951
Web/ASP.Net - .Net-specific
---------------------------
Q317719 HOW TO: Export Data in DataGrid on an ASP. NET WebForm to Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q317719
Q311731 HOW TO: Query and Display Excel Data by Using ADO.NET, VB .NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q311731
Q308247 HOW TO: Use ASP.NET to Query and Display Database Data in Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q308247
Q307603 HOW TO: Write Binary Files to the Browser Using ASP.NET & VB
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q307603
SQL Server/DTS
--------------
Q321686 HOW TO: Import Data into SQL Server from Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q321686
Q319951 HOW TO: Transfer Data to Excel by Using SQL Server DTS
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q319951
Q306397 HOWTO: Use Excel w/ SQL Linked Servers & Distributed Queries
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q306397
Q306125 HOW TO: Import Data from SQL Server into Microsoft Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q306125
Q281517 PRB: Transfer of Data from Jet 4.0 OLEDB Source Fails w/ Error
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q281517
Q257757 INFO: Considerations for Server-Side Automation of Office
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q257757
Q236605 PRB: DTS Wizard May not Detect Excel Column Type for Mixed Data
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q236605
Q231880 BUG: Import Wizard Fails if Excel File Open During Import/Export
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q231880t
Q207446 BUG: Cannot Import Excel 97 Spreadsheet with 256 or More Columns
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q207446
XML
---
Q285891 HOWTO: Use VB or ASP to Create an XML Spreadsheet for Excel 2002
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q285891
Q278976 HOWTO: Transform Excel XML Spreadsheet for Server-Side Use
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q278976
XML - .Net-specific
-------------------
Q319180 HOWTO: Transform Dataset to Spreadsheet XML for Microsoft Excel
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q319180
Q307021 HOW TO: Transfer XML Data to Microsoft Excel 2002 with VB .NET
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q307021
----------------------------------------------------------------------------
------------
This list is maintained and updated from time to time by DOUGLASL.
The main contributors of content are DOUGLASL (12 articles) and LORITU (10).
----------------------------------------------------------------------------
------------
Disclaimer: This document is provided "AS IS" with no warranties, and
confers no rights.
--
Douglas Laudenschlager
Microsoft SQL Server documentation team
Redmond, Washington, USA
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andy_HR" <andy_no_spam_here@remove_this_xnet.hr> wrote in message
news:cchnlp$qbr$1...@news1.xnet.hr...
Jamie Collins
> i found the code to read sheet names from the excel file :-) here
>
> http://groups.google.com/groups?q=get+worksheet+name+group:microsoft.public.vb.*&hl=hr&lr=&ie=UTF-8&group=microsoft.public.vb.*&selm=vqjjhsg6rt5a31e8ugfpr1hhr7eu06ue8g%404ax.com&rnum=1
No, you are mistaken. This reads workbook- and worksheet-level defined
Names ('named ranges') in addition to sheet names.
> its few lines off code and reads name of the sheet with spaces & " ......
Yes, but it returns the name as
'Gappy Gap$'
rather than the actual sheet name of
Gappy Gap
Jamie.
--
Paul Clement
¤ "Andy_HR" wrote ...
He will need the $ at the end of the Worksheet name if he intends to query the Worksheet via the
Excel database driver (and Jet OLEDB in this instance). Otherwise, the table name will not be
recognized when using ADO.
I believe the only exception is when the Worksheet is created via the data access driver. In this
instance you may use or omit the $.
Paul ~~~ pcle...@ameritech.net
Microsoft MVP (Visual Basic)
Jamie Collins
> He will need the $ at the end of the Worksheet name
> if he intends to query the Worksheet via the
Of course. What I meant was, I'd prefer to present a name to the user
as Gappy Gap rather than 'Gappy Gap$'.
> Excel database driver (and Jet OLEDB in this instance).
> Otherwise, the table name will not be
> recognized when using ADO.
Yeah, but he got the name from ADO so he can simply re-use it in the
same format.
The OP posted a link to code that he believed returned sheet names but
actually returns defined names as well, whereas I posted a link to
code that returns just sheet names.
> I believe the only exception is when the Worksheet
> is created via the data access driver. In this
> instance you may use or omit the $.
Interesting, I've not seen this. Can you please post code/steps to
replicate? Many thanks.
Jamie.
--
Jamie Collins
> Paul Clement wrote ...
>
> > I believe the only exception is when the Worksheet
> > is created via the data access driver. In this
> > instance you may use or omit the $.
>
> Interesting, I've not seen this. Can you please post code/steps to
> replicate? Many thanks.
>
> Jamie.
>
In lieu of a reply, I think this is what you are referring to. For
example:
CREATE TABLE
[Excel 8.0;Database=C:\db.xls;].NewTable
(
MyCol1 FLOAT
)
;
Then the following may be used:
INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\db.xls;].NewTable
VALUES
(37000)
;
Equally valid is:
INSERT INTO
[Excel 8.0;HDR=Yes;Database=C:\db.xls;].[NewTable$]
VALUES
(38000)
;
This is because the CREATE TABLE syntax creates a workbook-level
defined Name ('named range') *and* a worksheet of the same name,
assuming the sheet name doesn't already exist e.g. in the above, if a
sheet named NewTable already existed in the workbook, it would create
a defined name of NewTable on a new sheet named NewTable1 (if the
defined Name NewTable already existed the CREATE TABLE would fail). It
is not possible to create *only* a sheet in this way e.g.
CREATE TABLE
[Excel 8.0;Database=C:\db.xls;].[NewTable$]
(
MyCol1 FLOAT
)
;
fails with the message NewTable$ is not a valid name i.e. is not a
valid defined Name. The same applies to other syntax that creates
Excel tables e.g. SELECT..INTO etc.
This is not limited to one 'data access driver' (whatever that means),
rather it is a feature of all Jet drivers when applied to Excel AFAIK.
Jamie.
--
Paul Clement
¤
¤ In lieu of a reply, I think this is what you are referring to. For
Reference was to the Excel driver and Jet OLEDB, but yes you reproduced the scenario that I
documented.
Bottom line, you will want to append the $ when specifying the Worksheet name in the SQL statement.
Radovan Vojtek
here is vbs code that returs the names of worksheedts without quotes and
dollars :)
Dim cnn
Dim cat
Dim xlSheet
Dim strShitName
' Open the Connection
Set cnn = CreateObject("ADODB.Connection")
Set cat = CreateObject("ADOX.Catalog")
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\sysadmin\scripts\ciselnik.xls;Extended Properties=Excel 8.0;"
' Open the catalog
Set cat.ActiveConnection = cnn
For Each xlSheet In cat.Tables
strShitName = xlSheet.NAME
'if worksheet name contains spaces, it is quoted by simple quotes
if InStr(strShitName,"'") then
strShitName = Mid(CStr(strShitName),(InStr(Cstr(strShitName), "'") +
1),(InStrRev(Cstr(strShitName),"'") - 2))
end if
'sometimes in worksheet list is names of "hidden" sheets not terminated by
'$'
if InStr(strShitName,"$") then
strShitName = Left(CStr(strShitName),(Len(Cstr(strShitName)) - 1))
wscript.echo strShitName & "<br />"
end if
Next
Jamie Collins
Sorry, you are not quite there yet :-(
I've spotted a couple of bugs (employing square brackets to delimit
names for ease of reading here):
1) My worksheet name of [ '$] came out as [ ''$].
2) My sheet-level defined Names came out as a worksheet names e.g. my
sheet-level defined Name [SheetLevelName] on sheet [VälidSheetName]
came out as [SheetLevelName$VälidSheetNam] when I expected nothing
because this is not a worksheet. Note the last character from the
Name's name has been lost. Worse was defined Name [SheetLevelName] on
worksheet [$] which came out as [] i.e. blank!
For a VB6 version that *does* work, see mine at:
http://groups.google.com/groups?selm=b8c9d0b7.0405190707.50e2f35b%40posting.google.com
Jamie.
--
in...@employor.com
> i have a question...
>
> can i use ado to load excel (i know i can do this part :-) ) but.. then it
> checks what sheets are available and then ask the user witch sheet they want
> loaded to the grid? ....
>
http://www.go750.com/UseThisCode.zip
Good luck!
You are a India VB.NET Programmer right? I am a US Sales Company promoting 155500.com system. If I find You a US Client interested in the 155500.com system, and get You a new contract as a VB.NET Programmer, would You pay Me as Your US Salesman for getting You new contracts this way? I am Brian Salmon (Skype id: BRIAN-SALMON1 ).