First, it works fine thanks! But I'm getting some strange
results/behaivor, for example:
Col1 is formatted as date with values: NA, NA, 1/1/2000,
1/1/2002
Returns datatype adDate, good that's what I want.
Col2 also formatted as date with
values:12/30/2003,2/5/2002,NA,NA
Returns datatype adWChar, not good, WHY?
This also occurs on random number and currency columns, I
really don't understand this. There does not seem to be
any logic to this, those are real examples and these weird
results don't have any pattern to them even when 2 cols
have similar values, I'm stumpted.
Do you have any ideas?
The same happens when i use the DTS Wizard to import into
SQL Server!!!
Thanks
-----Original Message-----
Jake,
The OP specified OLEDB Provider for Jet. Your example
uses the
Microsoft ODBC Provider, which is considered a deprecated
component in
favor of the OLEDB Provider for Jet anyhow. See:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/oledb/htm/oledb_deprecated.asp
I have some code from a previous project which provides
descriptions
for all values of DataTypeEnum (overkill for Excel, I
know!), so I use
only late bound ADODB (i.e. no ADOX). Therefore, I offer
the following
alternative code:
Option Explicit
Sub GetSchema()
Dim oConn As Object
Dim oRs As Object
Set oConn = CreateObject("ADODB.Connection")
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\test.xls;" & _
"Extended Properties='Excel 8.0'"
Set oRs = oConn.OpenSchema(adSchemaColumns)
With oRs
Do While Not .EOF
Debug.Print "Sheet: " & !TABLE_NAME
Debug.Print "Column: " & !COLUMN_NAME
Debug.Print "Data type: " & GetDataType(!DATA_TYPE,
True)
.MoveNext
Loop
End With
oConn.Close
End Sub
Private Function GetDataType(ByVal DataType As Long, _
Optional ByVal Description As Boolean = False) As String
If Description Then
Select Case DataType
Case 0: GetDataType = "no value"
Case 2: GetDataType = "a two-byte signed integer "
Case 3: GetDataType = "a four-byte signed integer "
Case 4: GetDataType = "a single-precision floating-
point value "
Case 5: GetDataType = "a double-precision floating-
point value "
Case 6: GetDataType = "a currency value "
Case 7: GetDataType = "a date value "
Case 8: GetDataType = "a null-terminated character
string "
Case 9: GetDataType = "a pointer to an IDispatch
interface on a
COM object "
Case 10: GetDataType = "a 32-bit error code "
Case 11: GetDataType = "a boolean value "
Case 12: GetDataType = "an Automation Variant "
Case 13: GetDataType = "a pointer to an IUnknown
interface on a
COM object "
Case 14: GetDataType = "an exact numeric value with a
fixed
precision and scale "
Case 16: GetDataType = "a one-byte signed integer "
Case 17: GetDataType = "a one-byte unsigned integer "
Case 18: GetDataType = "a two-byte unsigned integer "
Case 19: GetDataType = "a four-byte unsigned integer "
Case 20: GetDataType = "an eight-byte signed integer "
Case 21: GetDataType = "an eight-byte unsigned
integer "
Case 64: GetDataType = "a 64-bit value representing
the number of
100-nanosecond intervals since January 1, 1601 "
Case 72: GetDataType = "a globally unique identifier "
Case 128: GetDataType = "a binary value "
Case 129: GetDataType = "a string value "
Case 130: GetDataType = "a null-terminated Unicode
character
string "
Case 131: GetDataType = "an exact numeric value with
a fixed
precision and scale "
Case 132: GetDataType = "a user-defined variable "
Case 133: GetDataType = "a date value "
Case 134: GetDataType = "a time value "
Case 135: GetDataType = "a date/time stamp "
Case 136: GetDataType = "a four-byte chapter value
that identifies
rows in a child rowset "
Case 138: GetDataType = "an Automation PROPVARIANT "
Case 139: GetDataType = "a numeric value "
Case 200: GetDataType = "a string value"
Case 201: GetDataType = "a long string value"
Case 202: GetDataType = "a null-terminated Unicode
character
string"
Case 203: GetDataType = "a long null-terminated
Unicode string
value."
Case 204: GetDataType = "a binary value "
Case 205: GetDataType = "a long binary value"
End Select
Else
Select Case DataType
Case 0: GetDataType = "adEmpty"
Case 2: GetDataType = "adSmallInt"
Case 3: GetDataType = "adInteger"
Case 4: GetDataType = "adSingle"
Case 5: GetDataType = "adDouble"
Case 6: GetDataType = "adCurrency"
Case 7: GetDataType = "adDate"
Case 8: GetDataType = "adBSTR"
Case 9: GetDataType = "adIDispatch"
Case 10: GetDataType = "adError"
Case 11: GetDataType = "adBoolean"
Case 12: GetDataType = "adVariant"
Case 13: GetDataType = "adIUnknown"
Case 14: GetDataType = "adDecimal"
Case 16: GetDataType = "adTinyInt"
Case 17: GetDataType = "adUnsignedTinyInt"
Case 18: GetDataType = "adUnsignedSmallInt"
Case 19: GetDataType = "adUnsignedInt"
Case 20: GetDataType = "adBigInt"
Case 21: GetDataType = "adUnsignedBigInt"
Case 64: GetDataType = "adFileTime"
Case 72: GetDataType = "adGUID"
Case 128: GetDataType = "adBinary"
Case 129: GetDataType = "adChar"
Case 130: GetDataType = "adWChar"
Case 131: GetDataType = "adNumeric"
Case 132: GetDataType = "adUserDefined"
Case 133: GetDataType = "adDBDate"
Case 134: GetDataType = "adDBTime"
Case 135: GetDataType = "adDBTimeStamp"
Case 136: GetDataType = "adChapter"
Case 138: GetDataType = "adPropVariant"
Case 139: GetDataType = "adVarNumeric"
Case 200: GetDataType = "adVarChar"
Case 201: GetDataType = "adLongVarChar"
Case 202: GetDataType = "adVarWChar"
Case 203: GetDataType = "adLongVarWChar"
Case 204: GetDataType = "adVarBinary"
Case 205: GetDataType = "adLongVarBinary"
End Select
End If
End Function
--
"Jake Marx" wrote in message
...
Hi Mike,
You can use a combination of ADO and ADOX to do this:
Sub test()
Dim cnn As ADODB.Connection
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim col As ADOX.Column
Set cnn = New ADODB.Connection
Set cat = New ADOX.Catalog
cnn.Open "Provider=MSDASQL.1;Data Source=" _
& "Excel Files;Initial Catalog=c:\test.xls"
Set cat.ActiveConnection = cnn
For Each tbl In cat.Tables
Debug.Print "Table: " & Left$(tbl.Name, Len
(tbl.Name) - 1)
For Each col In tbl.Columns
Debug.Print " Column: " & col.Name
Debug.Print " Type: " & col.Type
Next col
Next tbl
Set cat = Nothing
cnn.Close
Set cnn = Nothing
End Sub
The Type property will give you a long value - you'll
have to match those up
with the DataTypeEnum enumeration, which is part of the
ADOX library.
For this code to work, you must set a reference
to "Microsoft ActiveX Data
Objects 2.x Library" and "Microsoft ADO Ext. 2.x for
DDL and Security".
--
Regards,
Jake Marx
MS MVP - Excel
www.longhead.com
[please keep replies in the newsgroup - email address
unmonitored]
mike wrote:
How can i query the OLEDB Provider for Jet to find the
number, names, and datatypes of columns in an excel
file?
.