Greetings,

I am trying to access an excel spreadsheet using ado and one of the
fields have a zipcode in it. There is a number of tabs or worksheets in
this workbook and I am seeing some strange results that I need some help on.


In some instances the column in question for the zipcode has 10
characters or 5 characters (i.e. xxxxx-xxxx or xxxxx). On one worksheet
when I look at the datasource it shows up as null for a zipcode entered
in as xxxxx-xxxx, and then another worksheet it will show up perfectly fine.

This is the query I am using

"SELECT * FROM [" & aStateNames(iStateLoop) & "$]

where aStateNames refers to the name of the worksheets.




And this is the way I am calling the query

Function DbQuery(strSql As String, strFile As String, Optional
bolConnect As Boolean = False) As Variant

'Handle any errors found
On Error GoTo ErrorHandler
'Local variables
Dim objAdoRs As ADODB.Recordset
Dim strConn As String
'Create the reference to the ado recordset
Set objAdoRs = New ADODB.Recordset
'Ado recordset object
With objAdoRs
'SEt the object properties
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
'Set the connection string
If Not bolConnect Then
strConn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ="
& strFile
Else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"""
End If
'Open the recordset
.Open strSql, strConn
'.Save "c:\testjrd.xml", adPersistXML

'Check to see if the recordset brought back a list of records
If Not .EOF And Not .BOF Then
'Return the recordset as a 2D array
DbQuery = .GetRows
Else
'Nothing was found, so return nothing
DbQuery = ""
End If
End With
'Free the object
Set objAdoRs = Nothing
ExitFunc:
'Exit the function
Exit Function
ErrorHandler:
'Print out the error
Debug.Print strSql + " " + Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
Resume ExitFunc
End Function


Does any one have any idea on what is causing this weird behaviour and
what the solution could be to resolve it.

Irishdude,

This may or MAY NOT be your problem: ADO samples the first 7 to 10 rows in
order to "type" them; then treats all remaining records as that type -
anything not of that type is ignored;

So, for example, if your first 7 to 10 entries look like numbers: 12345,
77981, 26290, etc. then ADO will type the Zip column as a number and ignore
entries that don't look like numbers (i.e. 12345-4790 is not a number, so
this entry is ignored and a null replaces it).

You may be better off formatting all of the Zip's the same, but fill the
extended zip with zeros if it is not known, that way ADO will treat all
entries as text (e.g. 12345-0000). Not sure if this work around is best for
you...

HTH

"irishdudeinusa" wrote:

Greetings,

I am trying to access an excel spreadsheet using ado and one of the
fields have a zipcode in it. There is a number of tabs or worksheets in
this workbook and I am seeing some strange results that I need some help on.


In some instances the column in question for the zipcode has 10
characters or 5 characters (i.e. xxxxx-xxxx or xxxxx). On one worksheet
when I look at the datasource it shows up as null for a zipcode entered
in as xxxxx-xxxx, and then another worksheet it will show up perfectly fine.

This is the query I am using

"SELECT * FROM [" & aStateNames(iStateLoop) & "$]

where aStateNames refers to the name of the worksheets.




And this is the way I am calling the query

Function DbQuery(strSql As String, strFile As String, Optional
bolConnect As Boolean = False) As Variant

'Handle any errors found
On Error GoTo ErrorHandler
'Local variables
Dim objAdoRs As ADODB.Recordset
Dim strConn As String
'Create the reference to the ado recordset
Set objAdoRs = New ADODB.Recordset
'Ado recordset object
With objAdoRs
'SEt the object properties
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
'Set the connection string
If Not bolConnect Then
strConn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ="
& strFile
Else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"""
End If
'Open the recordset
.Open strSql, strConn
'.Save "c:\testjrd.xml", adPersistXML

'Check to see if the recordset brought back a list of records
If Not .EOF And Not .BOF Then
'Return the recordset as a 2D array
DbQuery = .GetRows
Else
'Nothing was found, so return nothing
DbQuery = ""
End If
End With
'Free the object
Set objAdoRs = Nothing
ExitFunc:
'Exit the function
Exit Function
ErrorHandler:
'Print out the error
Debug.Print strSql + " " + Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
Resume ExitFunc
End Function


Does any one have any idea on what is causing this weird behaviour and
what the solution could be to resolve it.

Greetings,

The first time the zipcode appears it is set up as xxxxx-xxxx and then
xxxxx and then xxxxx-xxxx. So this would imply that it is not the ADO
problem or feature that you called out down below.

Is there any way to force ADO to make everything be a string no matter
what datatype it sees it as is.

I did come up with one solution that may work, but that requires going
through each of the rows in the spreadsheet and forcing this column
value to be string by adding a single quote and then saving it.

Not the most ideal solution when you have a lot of worksheets and the
fact that the spreadsheet is generated from a different source.



quartz wrote:
Irishdude,

This may or MAY NOT be your problem: ADO samples the first 7 to 10 rows in
order to "type" them; then treats all remaining records as that type -
anything not of that type is ignored;

So, for example, if your first 7 to 10 entries look like numbers: 12345,
77981, 26290, etc. then ADO will type the Zip column as a number and ignore
entries that don't look like numbers (i.e. 12345-4790 is not a number, so
this entry is ignored and a null replaces it).

You may be better off formatting all of the Zip's the same, but fill the
extended zip with zeros if it is not known, that way ADO will treat all
entries as text (e.g. 12345-0000). Not sure if this work around is best for
you...

HTH

"irishdudeinusa" wrote:

Greetings,

I am trying to access an excel spreadsheet using ado and one of the
fields have a zipcode in it. There is a number of tabs or worksheets in
this workbook and I am seeing some strange results that I need some help on.


In some instances the column in question for the zipcode has 10
characters or 5 characters (i.e. xxxxx-xxxx or xxxxx). On one worksheet
when I look at the datasource it shows up as null for a zipcode entered
in as xxxxx-xxxx, and then another worksheet it will show up perfectly fine.

This is the query I am using

"SELECT * FROM [" & aStateNames(iStateLoop) & "$]

where aStateNames refers to the name of the worksheets.




And this is the way I am calling the query

Function DbQuery(strSql As String, strFile As String, Optional
bolConnect As Boolean = False) As Variant

'Handle any errors found
On Error GoTo ErrorHandler
'Local variables
Dim objAdoRs As ADODB.Recordset
Dim strConn As String
'Create the reference to the ado recordset
Set objAdoRs = New ADODB.Recordset
'Ado recordset object
With objAdoRs
'SEt the object properties
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
'Set the connection string
If Not bolConnect Then
strConn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ="
& strFile
Else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"""
End If
'Open the recordset
.Open strSql, strConn
'.Save "c:\testjrd.xml", adPersistXML

'Check to see if the recordset brought back a list of records
If Not .EOF And Not .BOF Then
'Return the recordset as a 2D array
DbQuery = .GetRows
Else
'Nothing was found, so return nothing
DbQuery = ""
End If
End With
'Free the object
Set objAdoRs = Nothing
ExitFunc:
'Exit the function
Exit Function
ErrorHandler:
'Print out the error
Debug.Print strSql + " " + Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
Resume ExitFunc
End Function


Does any one have any idea on what is causing this weird behaviour and
what the solution could be to resolve it.

You would have to adjust your strSql but using the Cstr function around the
zipcode should work.

"irishdudeinusa" wrote:

Greetings,

The first time the zipcode appears it is set up as xxxxx-xxxx and then
xxxxx and then xxxxx-xxxx. So this would imply that it is not the ADO
problem or feature that you called out down below.

Is there any way to force ADO to make everything be a string no matter
what datatype it sees it as is.

I did come up with one solution that may work, but that requires going
through each of the rows in the spreadsheet and forcing this column
value to be string by adding a single quote and then saving it.

Not the most ideal solution when you have a lot of worksheets and the
fact that the spreadsheet is generated from a different source.



quartz wrote:
Irishdude,

This may or MAY NOT be your problem: ADO samples the first 7 to 10 rows in
order to "type" them; then treats all remaining records as that type -
anything not of that type is ignored;

So, for example, if your first 7 to 10 entries look like numbers: 12345,
77981, 26290, etc. then ADO will type the Zip column as a number and ignore
entries that don't look like numbers (i.e. 12345-4790 is not a number, so
this entry is ignored and a null replaces it).

You may be better off formatting all of the Zip's the same, but fill the
extended zip with zeros if it is not known, that way ADO will treat all
entries as text (e.g. 12345-0000). Not sure if this work around is best for
you...

HTH

"irishdudeinusa" wrote:

Greetings,

I am trying to access an excel spreadsheet using ado and one of the
fields have a zipcode in it. There is a number of tabs or worksheets in
this workbook and I am seeing some strange results that I need some help on.


In some instances the column in question for the zipcode has 10
characters or 5 characters (i.e. xxxxx-xxxx or xxxxx). On one worksheet
when I look at the datasource it shows up as null for a zipcode entered
in as xxxxx-xxxx, and then another worksheet it will show up perfectly fine.

This is the query I am using

"SELECT * FROM [" & aStateNames(iStateLoop) & "$]

where aStateNames refers to the name of the worksheets.




And this is the way I am calling the query

Function DbQuery(strSql As String, strFile As String, Optional
bolConnect As Boolean = False) As Variant

'Handle any errors found
On Error GoTo ErrorHandler
'Local variables
Dim objAdoRs As ADODB.Recordset
Dim strConn As String
'Create the reference to the ado recordset
Set objAdoRs = New ADODB.Recordset
'Ado recordset object
With objAdoRs
'SEt the object properties
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
'Set the connection string
If Not bolConnect Then
strConn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ="
& strFile
Else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"""
End If
'Open the recordset
.Open strSql, strConn
'.Save "c:\testjrd.xml", adPersistXML

'Check to see if the recordset brought back a list of records
If Not .EOF And Not .BOF Then
'Return the recordset as a 2D array
DbQuery = .GetRows
Else
'Nothing was found, so return nothing
DbQuery = ""
End If
End With
'Free the object
Set objAdoRs = Nothing
ExitFunc:
'Exit the function
Exit Function
ErrorHandler:
'Print out the error
Debug.Print strSql + " " + Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
Resume ExitFunc
End Function


Does any one have any idea on what is causing this weird behaviour and
what the solution could be to resolve it.

Actually, this could still be your problem, since ADO applies the datatype to
the most prevalent type among the rows sampled. So if the majority look like
numbers, then it will still type that column as a number.

The only way to do this neatly as far as I know (others may post a better
solution) is to trick ADO into thinking that all your data are strings by
using the very method you site - i.e. to enter the single quote in front of
everything needed (or to change the appearance of your data as I suggested
originally). A pain true, but it does work - and if it solves your problem
then it proves that the datatype is the issue at hand.

It is not hard to run a simple macro in front of your ADO code that converts
the data in the zip column: i.e. by adding a single quote in front of
everything...

Untested and assuming the zip column is column D:

Dim rCell as Range
For Each rCell in ActiveSheet.UsedRange.Columns(4).Rows
If Left(rCell.Formular1c1, 1) < "'" then
rCell.Formular1c1 = "'" & rCell.Formular1c1
End If
Next rCell

"irishdudeinusa" wrote:

Greetings,

The first time the zipcode appears it is set up as xxxxx-xxxx and then
xxxxx and then xxxxx-xxxx. So this would imply that it is not the ADO
problem or feature that you called out down below.

Is there any way to force ADO to make everything be a string no matter
what datatype it sees it as is.

I did come up with one solution that may work, but that requires going
through each of the rows in the spreadsheet and forcing this column
value to be string by adding a single quote and then saving it.

Not the most ideal solution when you have a lot of worksheets and the
fact that the spreadsheet is generated from a different source.



quartz wrote:
Irishdude,

This may or MAY NOT be your problem: ADO samples the first 7 to 10 rows in
order to "type" them; then treats all remaining records as that type -
anything not of that type is ignored;

So, for example, if your first 7 to 10 entries look like numbers: 12345,
77981, 26290, etc. then ADO will type the Zip column as a number and ignore
entries that don't look like numbers (i.e. 12345-4790 is not a number, so
this entry is ignored and a null replaces it).

You may be better off formatting all of the Zip's the same, but fill the
extended zip with zeros if it is not known, that way ADO will treat all
entries as text (e.g. 12345-0000). Not sure if this work around is best for
you...

HTH

"irishdudeinusa" wrote:

Greetings,

I am trying to access an excel spreadsheet using ado and one of the
fields have a zipcode in it. There is a number of tabs or worksheets in
this workbook and I am seeing some strange results that I need some help on.


In some instances the column in question for the zipcode has 10
characters or 5 characters (i.e. xxxxx-xxxx or xxxxx). On one worksheet
when I look at the datasource it shows up as null for a zipcode entered
in as xxxxx-xxxx, and then another worksheet it will show up perfectly fine.

This is the query I am using

"SELECT * FROM [" & aStateNames(iStateLoop) & "$]

where aStateNames refers to the name of the worksheets.




And this is the way I am calling the query

Function DbQuery(strSql As String, strFile As String, Optional
bolConnect As Boolean = False) As Variant

'Handle any errors found
On Error GoTo ErrorHandler
'Local variables
Dim objAdoRs As ADODB.Recordset
Dim strConn As String
'Create the reference to the ado recordset
Set objAdoRs = New ADODB.Recordset
'Ado recordset object
With objAdoRs
'SEt the object properties
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
'Set the connection string
If Not bolConnect Then
strConn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ="
& strFile
Else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"""
End If
'Open the recordset
.Open strSql, strConn
'.Save "c:\testjrd.xml", adPersistXML

'Check to see if the recordset brought back a list of records
If Not .EOF And Not .BOF Then
'Return the recordset as a 2D array
DbQuery = .GetRows
Else
'Nothing was found, so return nothing
DbQuery = ""
End If
End With
'Free the object
Set objAdoRs = Nothing
ExitFunc:
'Exit the function
Exit Function
ErrorHandler:
'Print out the error
Debug.Print strSql + " " + Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
Resume ExitFunc
End Function


Does any one have any idea on what is causing this weird behaviour and
what the solution could be to resolve it.

Yes, I always forget about the conversion functions, Ralph's suggestion
should work!

Thanks.

"Ralph" wrote:

You would have to adjust your strSql but using the Cstr function around the
zipcode should work.

"irishdudeinusa" wrote:

Greetings,

The first time the zipcode appears it is set up as xxxxx-xxxx and then
xxxxx and then xxxxx-xxxx. So this would imply that it is not the ADO
problem or feature that you called out down below.

Is there any way to force ADO to make everything be a string no matter
what datatype it sees it as is.

I did come up with one solution that may work, but that requires going
through each of the rows in the spreadsheet and forcing this column
value to be string by adding a single quote and then saving it.

Not the most ideal solution when you have a lot of worksheets and the
fact that the spreadsheet is generated from a different source.



quartz wrote:
Irishdude,

This may or MAY NOT be your problem: ADO samples the first 7 to 10 rows in
order to "type" them; then treats all remaining records as that type -
anything not of that type is ignored;

So, for example, if your first 7 to 10 entries look like numbers: 12345,
77981, 26290, etc. then ADO will type the Zip column as a number and ignore
entries that don't look like numbers (i.e. 12345-4790 is not a number, so
this entry is ignored and a null replaces it).

You may be better off formatting all of the Zip's the same, but fill the
extended zip with zeros if it is not known, that way ADO will treat all
entries as text (e.g. 12345-0000). Not sure if this work around is best for
you...

HTH

"irishdudeinusa" wrote:

Greetings,

I am trying to access an excel spreadsheet using ado and one of the
fields have a zipcode in it. There is a number of tabs or worksheets in
this workbook and I am seeing some strange results that I need some help on.


In some instances the column in question for the zipcode has 10
characters or 5 characters (i.e. xxxxx-xxxx or xxxxx). On one worksheet
when I look at the datasource it shows up as null for a zipcode entered
in as xxxxx-xxxx, and then another worksheet it will show up perfectly fine.

This is the query I am using

"SELECT * FROM [" & aStateNames(iStateLoop) & "$]

where aStateNames refers to the name of the worksheets.




And this is the way I am calling the query

Function DbQuery(strSql As String, strFile As String, Optional
bolConnect As Boolean = False) As Variant

'Handle any errors found
On Error GoTo ErrorHandler
'Local variables
Dim objAdoRs As ADODB.Recordset
Dim strConn As String
'Create the reference to the ado recordset
Set objAdoRs = New ADODB.Recordset
'Ado recordset object
With objAdoRs
'SEt the object properties
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
'Set the connection string
If Not bolConnect Then
strConn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ="
& strFile
Else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"""
End If
'Open the recordset
.Open strSql, strConn
'.Save "c:\testjrd.xml", adPersistXML

'Check to see if the recordset brought back a list of records
If Not .EOF And Not .BOF Then
'Return the recordset as a 2D array
DbQuery = .GetRows
Else
'Nothing was found, so return nothing
DbQuery = ""
End If
End With
'Free the object
Set objAdoRs = Nothing
ExitFunc:
'Exit the function
Exit Function
ErrorHandler:
'Print out the error
Debug.Print strSql + " " + Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
Resume ExitFunc
End Function


Does any one have any idea on what is causing this weird behaviour and
what the solution could be to resolve it.

Try adding Imex option to connection string for mixed values that is the only
way I could get it to work..

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No;Imex=1"""



"quartz" wrote:

Actually, this could still be your problem, since ADO applies the datatype to
the most prevalent type among the rows sampled. So if the majority look like
numbers, then it will still type that column as a number.

The only way to do this neatly as far as I know (others may post a better
solution) is to trick ADO into thinking that all your data are strings by
using the very method you site - i.e. to enter the single quote in front of
everything needed (or to change the appearance of your data as I suggested
originally). A pain true, but it does work - and if it solves your problem
then it proves that the datatype is the issue at hand.

It is not hard to run a simple macro in front of your ADO code that converts
the data in the zip column: i.e. by adding a single quote in front of
everything...

Untested and assuming the zip column is column D:

Dim rCell as Range
For Each rCell in ActiveSheet.UsedRange.Columns(4).Rows
If Left(rCell.Formular1c1, 1) < "'" then
rCell.Formular1c1 = "'" & rCell.Formular1c1
End If
Next rCell

"irishdudeinusa" wrote:

Greetings,

The first time the zipcode appears it is set up as xxxxx-xxxx and then
xxxxx and then xxxxx-xxxx. So this would imply that it is not the ADO
problem or feature that you called out down below.

Is there any way to force ADO to make everything be a string no matter
what datatype it sees it as is.

I did come up with one solution that may work, but that requires going
through each of the rows in the spreadsheet and forcing this column
value to be string by adding a single quote and then saving it.

Not the most ideal solution when you have a lot of worksheets and the
fact that the spreadsheet is generated from a different source.



quartz wrote:
Irishdude,

This may or MAY NOT be your problem: ADO samples the first 7 to 10 rows in
order to "type" them; then treats all remaining records as that type -
anything not of that type is ignored;

So, for example, if your first 7 to 10 entries look like numbers: 12345,
77981, 26290, etc. then ADO will type the Zip column as a number and ignore
entries that don't look like numbers (i.e. 12345-4790 is not a number, so
this entry is ignored and a null replaces it).

You may be better off formatting all of the Zip's the same, but fill the
extended zip with zeros if it is not known, that way ADO will treat all
entries as text (e.g. 12345-0000). Not sure if this work around is best for
you...

HTH

"irishdudeinusa" wrote:

Greetings,

I am trying to access an excel spreadsheet using ado and one of the
fields have a zipcode in it. There is a number of tabs or worksheets in
this workbook and I am seeing some strange results that I need some help on.


In some instances the column in question for the zipcode has 10
characters or 5 characters (i.e. xxxxx-xxxx or xxxxx). On one worksheet
when I look at the datasource it shows up as null for a zipcode entered
in as xxxxx-xxxx, and then another worksheet it will show up perfectly fine.

This is the query I am using

"SELECT * FROM [" & aStateNames(iStateLoop) & "$]

where aStateNames refers to the name of the worksheets.




And this is the way I am calling the query

Function DbQuery(strSql As String, strFile As String, Optional
bolConnect As Boolean = False) As Variant

'Handle any errors found
On Error GoTo ErrorHandler
'Local variables
Dim objAdoRs As ADODB.Recordset
Dim strConn As String
'Create the reference to the ado recordset
Set objAdoRs = New ADODB.Recordset
'Ado recordset object
With objAdoRs
'SEt the object properties
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
'Set the connection string
If Not bolConnect Then
strConn = "DRIVER=Microsoft Excel Driver (*.xls);" & "DBQ="
& strFile
Else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"""
End If
'Open the recordset
.Open strSql, strConn
'.Save "c:\testjrd.xml", adPersistXML

'Check to see if the recordset brought back a list of records
If Not .EOF And Not .BOF Then
'Return the recordset as a 2D array
DbQuery = .GetRows
Else
'Nothing was found, so return nothing
DbQuery = ""
End If
End With
'Free the object
Set objAdoRs = Nothing
ExitFunc:
'Exit the function
Exit Function
ErrorHandler:
'Print out the error
Debug.Print strSql + " " + Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
Resume ExitFunc
End Function


Does any one have any idea on what is causing this weird behaviour and
what the solution could be to resolve it.