Serial Number With Letters And Numbers From Excel
I'm using this code to get data from a column in Excel.
[code]Database2 = "C:\Database\Naomy-DB.xls"
'Define the object names.
dim objConn
dim strConn
dim rs
'Define the database connection parameter constants.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
'Connect to the Excel file.
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Database2 & ";Extended Properties=""Excel 8.0;HDR=YES;"""
'Create a record set of one record which is the current record being printed.
Set rs = CreateObject("ADODB.Recordset")
rs.open "SELECT [Modelnr:] FROM [Modelark$] ORDER BY [Modelnr:] ", objConn', adOpenStatic, adLockOptimistic, adCmdText
'fill options
options=""
Do Until rs.EOF
If Len(options)>0 Then options = options + vbCR
options = options + rs("Modelnr:").Value
rs.MoveNext
Loop
'Close connection
objConn.Close
Set objConn = Nothing
'set options
Value=options[/code]
It works fine if all data is like this:
2365-15
2366-15
And if all data is numbers only:
2365
2366
I can make it with:
[code]options = options + Cstr(rs("Modelnr:").Value)[/code]
But my problem is, if the data is mixed like this:
2365-15
2366
2367-16
It will not work.
I tried with this If statement, but with no luck.
[code]If IsNumeric(rs("Modelnr:").Value) Then
options = options + Cstr(rs("Modelnr:").Value)
Else
options = options + rs("Modelnr:").Value
End If[/code]
What am I doing wrong?
[code]Database2 = "C:\Database\Naomy-DB.xls"
'Define the object names.
dim objConn
dim strConn
dim rs
'Define the database connection parameter constants.
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001
'Connect to the Excel file.
Set objConn = CreateObject("ADODB.Connection")
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Database2 & ";Extended Properties=""Excel 8.0;HDR=YES;"""
'Create a record set of one record which is the current record being printed.
Set rs = CreateObject("ADODB.Recordset")
rs.open "SELECT [Modelnr:] FROM [Modelark$] ORDER BY [Modelnr:] ", objConn', adOpenStatic, adLockOptimistic, adCmdText
'fill options
options=""
Do Until rs.EOF
If Len(options)>0 Then options = options + vbCR
options = options + rs("Modelnr:").Value
rs.MoveNext
Loop
'Close connection
objConn.Close
Set objConn = Nothing
'set options
Value=options[/code]
It works fine if all data is like this:
2365-15
2366-15
And if all data is numbers only:
2365
2366
I can make it with:
[code]options = options + Cstr(rs("Modelnr:").Value)[/code]
But my problem is, if the data is mixed like this:
2365-15
2366
2367-16
It will not work.
I tried with this If statement, but with no luck.
[code]If IsNumeric(rs("Modelnr:").Value) Then
options = options + Cstr(rs("Modelnr:").Value)
Else
options = options + rs("Modelnr:").Value
End If[/code]
What am I doing wrong?
0
-
It's the way Excel interprets and passes types of data. It's messed up, and has been like this for a long time. It almost always happens to me when, like you're describing, we're mixing data types.
The best solution is to save your .xls out to a Tab Delimited .txt. There may be other workarounds, but that is the one I know works.
From: http://www.seagullscientific.com/aspx/FAQ.aspx
[i][b]When using Microsoft Excel as a database source for my BarTender label format, why do I often get missing data in some fields?[/b]
A : This is a limitation of Excel, which is not a true database application. The data is dropped by the ODBC or OLEDB connection when the actual data in the field does not agree with the data type given to that field by the connection.
The solution is either to export your data to a Microsoft Access database (.mdb file) using a delimited text file, or to "force" the data type used by the field by prefixing your data in Excel with a ' character. This forces the Excel ODBC/OLEDB driver to treat the data as string data type.[/i]0 -
It was not just the information I wanted to hear 
I will find a way to force this thru.
Thanks for your response nRyder0 -
Shotaro Ito
★ BarTender Hero ★
This mixed type issue is long headache for Excel ODBC/OLEDB users.
Generally use CSV database works well as nRyder suggested.
If you need to stick with Excel,
One solution is to unify column data type by apply "string" cell data type to excel sheet.
However, Just applying string format to whole sheet doesn't change previously input values - you need to retype all data
an easy way to retype all, is copy whole sheet, paste into notepad, apply string format to whole sheet, paste back from notepad.
Another is specifying import mode (IMEX=1) allows OLEDB to read all mixed type column as text.
ex..
[code]objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Database2 & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1"""[/code]
Database connection created by BarTender 9.4 automatically add ";IMEX=1", to reduce the same thing to happen.
Note that this scans only first 8 lines to determine mixed type column or not.0 -
Gene Henson
★ BarTender Hero ★
I've attached a PDF here that helps explain the situation and workarounds. 0
Please sign in to leave a comment.
Comments
4 comments