excel leading zero problem

Skip to first unread message


Sep 18, 2001, 2:41:50 AM9/18/01
hi all:

something need your help

who is doing the vb com that read recordset from excel
that using excel driver before?

i face a problem when
i read one record from excel cell

my data in cell excel file is "01234"
and format cell is text

but when i can recordset data out
it display null

how to solve?

for leading zero in front for excel cell
and read recordset can has it original
value "01234"

for other case

come out "1234"

thank for help


Sep 18, 2001, 5:53:44 AM9/18/01
Could you provide the code you use that returns null for "01234"? Below is
the sample code that works correctly on my side. You may check it to see
whether it is helpful to make out where the problem comes from:

******sample code************
Dim rst As ADODB.Recordset

Private Sub Command1_Click()
Text1.Text = rst("f1")
End Sub

Private Sub Form_Load()
Dim strProvider As String, strQuery As String
strProvider = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ=F:\My Documents\TEMP\Book1.xls;"
strQuery = "SELECT * FROM `Sheet1$` "
Set rst = New ADODB.Recordset
rst.Open strQuery, strProvider
End Sub
*******sample code************

NOTE: I'm a Microsoft Employee, and I've been "encouraged" to include the
following information from our lawyers: this posting is provided "As Is"
with no warranties, and confers no rights. That means there is no warranty
of merchantability or fitness for a particular purpose, and no warranty of
non-infringement. See full details on terms of use.

yeong hong keong

Sep 19, 2001, 3:36:07 AM9/19/01
my code is like below

in vb com one
see the ** part for coding of it

--take note as the line
EPFno = rs.Fields("EPF_NO").Value

( my excel format is text
and when i key in "01234"
in cell
in excel i can see 01234

but when i read recordset from excel using
Microsoft Excel Driver (*.xls)

it come out null for the line with

how to solve?

it want the value of "01234"
come out in recordset?


Private Function GetFile() As Integer
Dim FileObj As FileSystemObject
Dim latefile As String
Dim oldDate As Date
Dim File As File

On Error GoTo ErrorHandler
Set FileObj = CreateObject("Scripting.FileSystemObject")

oldDate = CDate("1970/01/01")
latefile = ""

For Each File In FileObj.GetFolder(mvarXLDir).Files
If File.DateLastAccessed > oldDate And UCase(Right(File.Name, 4)) = ".XLS" Then
' If File.DateLastAccessed > oldDate And UCase(Right(File.Name, 4)) = ".CSV" Then

latefile = File.Name
oldDate = File.DateLastAccessed
Filename = File.Name
End If

mvarXLDir = mvarXLDir & "\" & latefile

mvarExcelPath = "DBQ=" & mvarXLDir & ";Driver={Microsoft Excel Driver (*.xls)};ReadOnly=1"
' mvarExcelPath = "DBQ=" & mvarXLDir & ";Driver={Microsoft text Driver (*.txt;*.csv)};ReadOnly=1"

If latefile = "" Then
' Response.Write ("<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font face='verdana,arial,geneva' size=2>Can not found the Excel file in such directory!</font>")
' Response.Write ("<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font face='verdana,arial,geneva' size=2>Transfering aborted!</font>")
GetFile = 0
' Response.Write ("<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font face='verdana,arial,geneva' size=2>Excel file found, start transfering .....</font>")
GetFile = 1
End If
Set FileObj = Nothing
Exit Function
Set FileObj = Nothing
Response.Write "Module:GetFile,Error number:" & Err.Number & ",Description:" & Err.Description

End Function

Private Sub ExcelToDB()
Dim conn As ADODB.Connection
Dim XLconnstring As String
Dim XLConn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim rs_temp As ADODB.Recordset
Dim rs_error As ADODB.Recordset
Dim remark As String

Dim rs_chk As ADODB.Recordset
Dim rs_chk1 As ADODB.Recordset

Dim rs_chkpackage As ADODB.Recordset
Dim rs_chkpackage1 As ADODB.Recordset

Dim rs_chksup As ADODB.Recordset
Dim validdata As Boolean

validdata = True

Dim RecUpl As Integer
Dim Membername, NRIC, OtherID, HAddr1, HAddr2, HPostcode, HCity, HState, Homephoneno, Officephoneno, Handphoneno, Packagecode, suporgname
Dim DAddr1, DAddr2, DAddr3, DPostcode, DCity, DState
Dim DOB, RACE, GENDER, preferredphone
Dim RecCounter As Integer
Dim sSQL As String
Dim EPFno As String

RecCounter = 0

Set XLConn = CreateObject("ADODB.Connection")
XLConn.ConnectionString = mvarExcelPath

If XLConn.State = adStateClosed Then
GoTo XLConnOpenError
End If
If XLConn.State = adStateOpen Then
'Response.Write ("<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font face='verdana,arial,geneva' size=2>Connection to Excel file established!</font>")
End If

Set conn = CreateObject("ADODB.Connection")

conn.ConnectionString = mvarDBConn

' conn.Open mvarDBConn, "sa", ""

' If conn.State = adStateClosed Then
' GoTo ConnOpenErr
'End If

'If conn.State = adStateOpen Then
' Response.Write ("<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font face='verdana,arial,geneva' size=2>Connection to Database established!</font>")
'End If

' Response.Write ("<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font face='verdana,arial,geneva' size=2>-----------------------------------------------------</font>")

Set rs = CreateObject("ADODB.Recordset")
rs.CursorLocation = adUseClient
Filename = "PRBATCH.XLS"
rs.Open "[" & Trim(Replace(UCase(Filename), UCase(".xls"), "")) & "$]", XLConn, , , adCmdTable

'rs.Open "[" & Trim(Replace(UCase(Filename), UCase(".xls"), "")) & "$]", XLConn

On Error GoTo ErrHandle

Set rs_chk = CreateObject("ADODB.Recordset")
rs_chk.CursorType = adOpenKeyset
rs_chk.CursorLocation = adUseClient
rs_chk.LockType = adLockOptimistic

Set rs_chk1 = CreateObject("ADODB.Recordset")
rs_chk1.CursorType = adOpenKeyset
rs_chk1.CursorLocation = adUseClient
rs_chk1.LockType = adLockOptimistic

Set rs_chkpackage = CreateObject("ADODB.Recordset")
rs_chkpackage.CursorType = adOpenKeyset
rs_chkpackage.CursorLocation = adUseClient
rs_chkpackage.LockType = adLockOptimistic

Set rs_chkpackage1 = CreateObject("ADODB.Recordset")
rs_chkpackage1.CursorType = adOpenKeyset
rs_chkpackage1.CursorLocation = adUseClient
rs_chkpackage1.LockType = adLockOptimistic

Set rs_chksup = CreateObject("ADODB.Recordset")
rs_chksup.CursorType = adOpenKeyset
rs_chksup.CursorLocation = adUseClient
rs_chksup.LockType = adLockOptimistic

Set rs_temp = CreateObject("ADODB.Recordset")
rs_temp.CursorType = adOpenKeyset
rs_temp.CursorLocation = adUseClient
rs_temp.LockType = adLockOptimistic
'rs_temp.Open "BATCHUPLOAD", conn, , , adCmdTable

Set rs_error = CreateObject("ADODB.Recordset")
rs_error.CursorType = adOpenKeyset
rs_error.CursorLocation = adUseClient
rs_error.LockType = adLockOptimistic
'rs_error.Open "BATCHerror", conn, , , adCmdTable

Do While Not rs.EOF

validdata = True
remark = ""
RecCounter = RecCounter + 1

'If IsNull(rs.Fields("EPF_NO").Value) = True Then


'End If

EPFno = rs.Fields("EPF_NO").Value

end sub

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Douglas Laudenschlager

Sep 20, 2001, 1:52:01 PM9/20/01

You will have serious problems if you have TEXT and NUMBERS in the same
column. The Excel driver returns ONLY the values of the majority data
type, and returns NULL for the others. You need to make all values in the
column NUMBERS, or all values TEXT. You can use a single quote character
to mark a cell value as text.




Using ADO with Excel: Resources and Known Issues
August 29, 2001

Microsoft Knowledge Base articles can be found at:
Choose to search by "specific article ID number"
Enter the article number including the "Q"

Q303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA
Q306397 INFO: Use Excel with SQL Server Linked Servers and Distributed

Q257819 HOWTO: Use ADO with Excel Data from Visual Basic or VBA
Q303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA
Q278973 SAMPLE: ExcelADO Shows How to Read/Write Data in Excel Workbooks
Q195951 HOWTO: Query and Update Excel Data Using ADO From ASP
Q306397 INFO: Use Excel with SQL Server Linked Servers and Distributed

Related How-To Subjects
Q244761 XL2000: How to Use ADO to Return Data to a ListBox or ComboBox
Q285891 HOWTO: Use VB or ASP to Create an XML Spreadsheet for Excel 2002
Q276488 HOWTO: Use ADODB.Stream to Read Binary Files to the Browser
Q199841 HOWTO: Display ASP Results Using Excel in IE with MIME Types
Q278976 HOWTO: Transform Excel XML Spreadsheet for Server-Side Use

Transferring Data into Excel
Q247412 INFO: Methods for Transferring Data to Excel from Visual Basic
Q295646 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
Q246335 HOWTO: Transfer Data from ADO Recordset to Excel with Automation

Known Issues
Q246167 PRB: Collating Sequence Error Opening XLS as ADO Recordset
Q288343 BUG: Excel ODBC Driver Disregards FirstRowHasNames/HDR Setting
Q293828 BUG: Excel File Size Grows When You Edit ADO Recordset
Q300948 BUG: Incorrect TABLE_TYPE Is Returned for Excel Worksheets

Known Issues working within Excel
Q263498 BUG: Run-Time Error 5 Using Add Method of QueryTables Collection
Q215154 XL2000: Excel does not support OLE DB Data Links
Q228633 OFF2000: "Catastrophic Failure" Error Running Samples.xls Macro
Q291199 XL2002: "Invalid Use of New Keyword" Error Using ADODB Library
Q225059 XL2000: "Invalid Use of New Keyword" Error Using ADODB Library

This posting is provided “AS IS” with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights

Reply all
Reply to author
0 new messages