• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

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

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

  • 4
  • 3
  • 3
2 Solutions
Chuck WoodCommented:
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

johnnyg123Author Commented:
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
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.
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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?
Chuck WoodCommented:
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.
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.
johnnyg123Author Commented:
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

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

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.
johnnyg123Author Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now