INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using DAO to read data from Excel

Using DAO to read data from Excel

(OP)
I am using the following code to open an Excel spreadsheet using DAO and reading records and posting transactions into an accounting application.

The spreadsheet layout is simple:
Vendor Invoice  Date         Amount
ABC100 IN100    04/01/2008   100.00
DEF100 IN101    04/01/2008   200.00

CODE

Dim dbExcel As DAO.Database
Dim rsExcel As DAO.Recordset

Set dbExcel = OpenDatabase("C:\File.xls", False, True, "Excel 8.0; HDR=YES;")
Set rsExcel = dbExcel.OpenRecordset("Sheet1$")

Do While Not rsExcel.EOF
    'Read records and process data
    rsExcel.MoveNext
Loop

The problem is that you can have mixed data types in Excel, for example in the Invoice column users can enter string and numeric data. If everything is string then there is no problem, but with mixed data types the field returns Null for invalid data types.
If numbers are entered with a leading apostrophe (converting number to string) then all is fine, but the end users keep forgetting to do this.
The question is: Is there a way to force a data type to string for certain fields in the DAO recordset?
I have found references to using a text file for defining data types for text files opened using DAO, but there is nothing similar for Excel.

BTW ADO does the same.

RE: Using DAO to read data from Excel

(OP)
Thanks George, I found that one as well.

It seems that there is no real solution to the Excel ISAM driver problem.

What I did to get around the problem is I wrote a routine to open the spreadsheet in Excel and then change all numeric values to text and save the file.

RE: Using DAO to read data from Excel






"Invoice column users can enter string and numeric data."

In reality, Invoice Numbers are not numbers at all.  You will NEVER do arithmetic on an Invoice Number.
The value is an IDENTIFIER, albeit all numeric CHARACTERS.  Your user interface ought to convert the numbers to string values.

Skip,

glassesHave you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!tongue

RE: Using DAO to read data from Excel

(OP)
Um, yes, but what is happening is the end user is typing this information into an Excel spreadsheet - that's what they want.
I take the information from the spreadsheet and pull it into an accounting system. I am not in control of the data input by the end user, and that is the source of the problem.

RE: Using DAO to read data from Excel


You could install Worksheet_Change event code to assure that numeric data is converted...

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
'**this procedure converts NUMBERS to TEXT in the specified column

    Dim t As Range
    For Each t In Target
        With t
        '''Cells(1, "A") assumes that your INVOICE is in Column A
        '''Modify accordingly

            If Not Intersect(t, Cells(1, "A").EntireColumn) Is Nothing Then
                If IsNumeric(.Value) Then .Value = "'" & .Value
            End If
        End With
    Next
End Sub
installed in the Sheet Object code window.

Skip,

glassesHave you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!tongue

RE: Using DAO to read data from Excel

(OP)
Thanks Skip, that is basically what I did in VB. Remember I am running a VB app, not a macro in Excel.
I tried running a macro in the spreadsheet, but the users are not the brightest, they start a new spreadsheet instead of using the spreadsheet I had setup with the macro.
I had to come up with something that is idiot proof.

RE: Using DAO to read data from Excel





Use a procedure to FIX prior to querying.

Skip,

glassesHave you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!tongue

RE: Using DAO to read data from Excel

Perhaps see 11 Apr 08 16:17 ? lol

RE: Using DAO to read data from Excel

<I wrote a routine to open the spreadsheet in Excel and then change all numeric values to text

That's one of the workarounds that the link that George and dilettante provided mentions.  It also mentions that it uses the first 8 rows to determine the datatype, so really you only need to do the first 8 rows if that helps any.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close