excel leading zero problem
hky
something need your help
who is doing the vb com that read recordset from excel
file
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
like
"1234"
come out "1234"
thank for help
chris
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
:
in vb com one
see the ** part for coding of it
--take note as the line
with
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
rs.Fields("EPF_NO").Value
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
Next
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> <font face='verdana,arial,geneva' size=2>Can not found the Excel file in such directory!</font>")
' Response.Write ("<br> <font face='verdana,arial,geneva' size=2>Transfering aborted!</font>")
GetFile = 0
Else
' Response.Write ("<br> <font face='verdana,arial,geneva' size=2>Excel file found, start transfering .....</font>")
GetFile = 1
End If
Set FileObj = Nothing
Exit Function
ErrorHandler:
Set FileObj = Nothing
GetObjectContext.SetAbort
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
XLConn.Open
If XLConn.State = adStateClosed Then
GoTo XLConnOpenError
End If
If XLConn.State = adStateOpen Then
'Response.Write ("<br> <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
' conn.Open mvarDBConn, "sa", ""
' If conn.State = adStateClosed Then
' GoTo ConnOpenErr
'End If
'If conn.State = adStateOpen Then
' Response.Write ("<br> <font face='verdana,arial,geneva' size=2>Connection to Database established!</font>")
'End If
' Response.Write ("<br> <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 = ""
'rs.MoveNext
RecCounter = RecCounter + 1
'If IsNull(rs.Fields("EPF_NO").Value) = True Then
' GoTo FINISH
'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
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.
'01234
or
="01234"
Using ADO with Excel: Resources and Known Issues
August 29, 2001
Microsoft Knowledge Base articles can be found at:
http://search.support.microsoft.com/kb/c.asp
Choose to search by "specific article ID number"
Enter the article number including the "Q"
New
---
Q303814 HOWTO: Use ADOX with Excel Data from Visual Basic or VBA
Q306397 INFO: Use Excel with SQL Server Linked Servers and Distributed
Queries
General
-------
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
Queries
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
reserved.