Using DAO to read data from Excel 1

[ettienne]

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

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.

 

[gmmastros]

http://support.microsoft.com/kb/194124

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ettienne]

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.

 

[SkipVought]

"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,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ettienne]

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.

 

[SkipVought]

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

Private Sub Worksheet_Change(ByVal Target As Range)[b]
'**this procedure converts NUMBERS to TEXT in the specified column[/b]
    Dim t As Range
    For Each t In Target
        With t[b]
        '''Cells(1, "A") assumes that your INVOICE is in Column A
        '''Modify accordingly[/b]
            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,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[ettienne]

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.

 

[SkipVought]

Use a procedure to FIX prior to querying.

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[dilettante]

Perhaps see

http://support.microsoft.com/kb/194124

???

 

[BobRodes]

Perhaps see 11 Apr 08 16:17 ?

 

[BobRodes]

<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.