Date/ Numeric Values incorrectly returned as null when reading excel spreadsheet via access vba

Posted on 2009-05-01
Last Modified: 2013-11-27
I have a vba routine in access that reads an excel spreadsheet to either insert or update data in  an access table.  I tested the application with a spreadsheet in which I manually entered data and all worked fine.   My user has a spreadsheet that calculates data using formulas and then she does a copy/ paste special  values.   (also tried paste special values and number formats but doesn't seem to mak a difference)

The problem is that all the date fields in the recordset are being returne as null and the numeric values are being returned as zero even when there are valid values for these fields.

When I look at the type for the fields the value is adVarWchar instead of either addouble or addate.   I suspect there is some kind of conversion going on behin the scenes.

If I look at the column in the spreadsheet and do a format, it says it is a date but....

I've posted the access vba code below.

Any ideas?
Private Sub cmdProcessADPFile_Click()

'Application.DoCmd.SetWarnings (False)

Dim strExcel_File As String

strExcel_File = GetExcel(Me)

If strExcel_File = vbNullString Then GoTo GetstrExcel_File_exit

Dim intAnswer As Integer


    intAnswer = MsgBox(vbCrLf & "Clicking Yes will add/update Employee Data" & vbCrLf & vbCrLf & "Are you sure you wish to continue ?", vbQuestion + vbYesNo, "Employee Data Will Be Added/Updated")

    If intAnswer = vbYes Then


        Dim rs As New ADODB.Recordset

        Dim cnn2 As New ADODB.Connection

        Dim cmd2 As New ADODB.Command

        Dim strsql As String

        Dim intRecsAdded As Integer

        Dim intRecsUpdated As Integer

        Dim lngTotalRecs As Long


        Dim Hourly_Rate As Double

        Dim Hourly_beneficial_rate As Double

        Dim Weekly_salary As Double

        Dim Weekly_beneficial_rate As Double




        intRecsAdded = 0

        intRecsUpdated = 0

        With cnn2

            .Provider = "Microsoft.Jet.OLEDB.4.0"

            .ConnectionString = "Data Source=" & strExcel_File & ";" & "Extended Properties=Excel 8.0;"


        End With

        Set cmd2.ActiveConnection = cnn2

        cmd2.CommandType = adCmdText

        cmd2.CommandText = "SELECT * FROM [Sheet1$]  "

        rs.CursorLocation = adUseClient

        rs.CursorType = adOpenKeyset

        rs.LockType = adLockOptimistic

        rs.Open cmd2




        While Not rs.EOF


        lngTotalRecs = DCount("Employee_ID", "Emp info - Cosmic", "Employee_ID=" & rs!Employee_ID)




        If Len(Nz(rs!Hourly_Rate, "")) = 0 Then

            Hourly_Rate = 0


            Hourly_Rate = rs!Hourly_Rate

        End If


        If Len(Nz(rs!Hourly_beneficial_rate, "")) = 0 Then

            Hourly_beneficial_rate = 0


            Hourly_beneficial_rate = rs!Hourly_beneficial_rate

        End If


        If Len(Nz(rs!Weekly_salary, "")) = 0 Then

            Weekly_salary = 0


            Weekly_salary = rs!Weekly_salary

        End If


        If Len(Nz(rs!Weekly_beneficial_rate, "")) = 0 Then

            Weekly_beneficial_rate = 0


            Weekly_beneficial_rate = rs!Weekly_beneficial_rate

        End If



        If lngTotalRecs <> 0 Then


           ' MsgBox "Employee ID " & rs!Employee_ID & " not added" & vbCrLf & vbCrLf & "Skipping to Next Record", , "Employee ID already exists"

            strsql = "Update [Emp info - cosmic]"


            strsql = strsql & " Set [Name]" & " = " & "'" & rs!Name & "'" & ", "

            strsql = strsql & "[SSN]" & " = " & "0" & ", "

            strsql = strsql & "[ShopNo]" & " = " & rs!ShopNo & ", "

            strsql = strsql & "[Hire_Date]" & " = " & "'" & rs!hire_date & "'" & ", "

            strsql = strsql & "[Term Date]" & " = " & "'" & rs![Term Date] & "'" & ", "

            strsql = strsql & "[CurTitle]" & " = " & "'" & rs![Cur Title] & "'" & ", "

            strsql = strsql & "[Hourly_Rate]" & " = " & Hourly_Rate & ", "

            strsql = strsql & "[Hourly_beneficial_rate]" & " = " & Hourly_beneficial_rate & ", "

            strsql = strsql & "[Weekly_salary]" & " = " & Weekly_salary & ", "

            strsql = strsql & "[Weekly_beneficial_rate]" & " = " & Weekly_beneficial_rate & ", "

            strsql = strsql & "[Date_Last_Updated]" & " = " & "'" & Now() & "'"

            strsql = strsql & " Where [Employee_ID]" & " = " & rs!Employee_ID



            CurrentDb.Execute strsql


            intRecsUpdated = intRecsUpdated + 1


            MsgBox "Employee ID " & rs!Employee_ID & " updated", , "Employee ID Updated"




            strsql = "INSERT INTO [Emp info - cosmic]([Employee_ID], [Name], [SSN], [ShopNo], [Hire_Date],[Term Date],[CurTitle],[CommBase1],[Commbase2],[Hourly_Rate],[Hourly_beneficial_rate],[Weekly_salary],[Weekly_beneficial_rate]) "

            strsql = strsql & "VALUES(" & rs!Employee_ID & ", " & "'" & rs!Name & "'" & ", " & "0" & ", " & rs!ShopNo & ", " & "'" & rs!hire_date & "'" & ", " & "Null" & ", " & "'" & rs![Cur Title] & "'" & ", " & "0" & ", " & "0" & ", " & Hourly_Rate & ", " & Hourly_beneficial_rate & ", " & Weekly_salary & ", " & Weekly_beneficial_rate & ")"


            CurrentDb.Execute strsql


            intRecsAdded = intRecsAdded + 1


            MsgBox "Employee ID " & rs!Employee_ID & " added", , "Employee ID Added"


        End If



        'Application.DoCmd.TransferText acImportDelim, "Dialer_App Link Specification", "dbo_dialer_app", strExcel_File, False


        MsgBox strExcel_File & " has been processed.", vbInformation, intRecsAdded & " Employee(s) Added " & "And " & intRecsUpdated & " Employee(s) Updated "



        MsgBox "Employee(s) not added", , "Add Request Cancelled"


    End If


 Exit Sub


MsgBox "Employee(s) not added", , "Add Request Cancelled"

Exit Sub


MsgBox Err.Description & vbCrLf & vbCrLf & "Please check Excel file format", vbCritical, "Import Error"

End Sub

Open in new window

Question by:johnnyg123
    LVL 16

    Accepted Solution

    I recommend you use the TransferSpreadsheet method of DoCmd to import your spreadsheet data. The final Boolean (True/False) value indicates whether your spreadsheet has field name (headers). Please let me know if you have any questions.

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TableName", "FileName", True

    Open in new window


    Author Comment

    Well..the DoCmd.TransferSpreadsheet was my first choice but the user thinks there might be a time when the update might depend on a certain value in the spreadheet
    LVL 4

    Expert Comment

    You're right there is a conversion happening but not behind the scenes.
    You say when you manually enter data it works - when you do a copy - paste values it doesn't?
    What is happening is that you are copying text & pasting it into Excel, you are pasting text.
    You're also making a common mistake about the Excel Format dialog - just because it says the format is Date does not mean the DATA is of that TYPE!
    It means Excel will try to format data for display in that manner.
    If you paste text that looks like a date it will look fine.
    But with a normal date when you change the format to General, it displays a floating point number.
    I'm sure if you do that with yr data, you'll still see the "Date", because it's text & Excel can only display it.

    The same is happening with the numbers - they look like numbers, but as far as Excel is concerned they are text.
    LVL 4

    Expert Comment

    You can fix this either in yr spreadsheet, but I think a simpler option would be to look at yr VBA import routine first
    You said the datatype was adVarWchar - did you check what the actual data was?
    If the actual data was "23/04/2009" then maybe you can just use functions like CDate & CDbl to coerce the data to the type you want?
    Like when you are trying to save rs!hire_date into the Access table use CDate(rs!hire_date).

    Maybe I'm mistaken, but don't dates have to be surrounded with hashes #?
    Maybe take a closer at yr update query?
    LVL 16

    Expert Comment

    by:Chuck Wood
    If you use CDate, you don't need to surround the date with hashes. You can also use "#" & rs!hire_date & "#" but I think CDate is a better choice.
    LVL 4

    Expert Comment

    Another thing to be aware about Excel in case yr user's have problems where sometime they see data in the spreadsheet but it doesn't get imported:
    Becasue Excel can have various types of data in a single colum, the Excel ODBC driver, which you are using here, determines the datatype of a column by scanning the first 8 rows of the spreadsheet.
    Whichever datatype is in the majority, sets the datatype for that column as far as the ODBC driver is concerned. If there is other types of data in the same column, they will be discarded by the ODBC driver.
    So, if a user does a copy paste & it's all text & then makes a manual correction of just 1 or 2 rows, because you have set the datatype in Excel to General or Date, they are in the minority now against all the text. They will be discarded by the ODBC driver.
    Hence if you are doing this import, it's safe to format yr entire spreadsheet as text so that manual entry will be treated as text.
    But just be aware of that "feature" of the Excel ODBC driver cos I guarantee you, yr users will come to you soon enough saying the data isin the spreadsheet but yr program is faulty.

    Author Comment

    Thanks for all the posts r0bertdenir0!

    I knew about the 8 rows deal so I had the user make sure that a default date of 1/1/1900 was in the date cells.  that seems to have solved that problem but I can't figure out what is going on with one of the numeric cells.  I had the user deault 0 and yet the va code still thinks it is character and is ignoring any value and using a value of zero

    I have attached a sample file (minus employee name)
    Looking at the first enty in the file you can see that employee id 23816
    has a weekly salary of 510 but the debug screen shows what is in the attached word file

    LVL 16

    Expert Comment

    by:Chuck Wood
    According to Excel 2007, some of the cells are formatted as Text. I recommend you use 0.00 for zero amounts.

    LVL 4

    Assisted Solution

    The problem is you never know what the user is pasting in there...
    You can set the format to Number but if they are pasting text (formatted on the clipboard as text) Excel will treat it as text until you edit the cell & press Enter.
    It doesn't attempt that conversion for paste operations.
    The problem you have is that they can paste all kindsa stuff there from who knows where & as far as they r concerned a number is a number.

    But if yr data is mixed here it's gonna goto Access as 0 or blank.
    There myt be other ways, but off the topp of my head ryt now I think the simplest way to ensure the integrity of the data is to run a macro to ensure that every column is of the datatype you want.
    Either get the user to run the macro herself when she's done (better - give her responsibility), or run automatically when she closes the workbook.
    Either format all data as text & convert in Access (simplest I think) or convert to the "correct" type in the spreadsheet.

    Author Comment

    I really appreciate all the responses!

    The solution I ended up using was a combo of ideas from r0bertdenir0 and cwood-wm-com so I split the points between them.

    I added a table with field definitions I needed and then added the following to wipe out the table and copy the spreadsheet values.  (For whatever reason this got the data in the format I needed)

     CurrentDb.Execute "Delete from ADPExtract"
            DoCmd.TransferSpreadsheet acImport, , "ADPExtract", strExcel_File, True, "Sheet1!"

            Set rs = CurrentDb.OpenRecordset("Select * from ADPExtract")

    I then processed the record set from the table instead of trying to use odbc to read the spreadsheet and create a record set.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now