Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ADO Help (VBA/Excel/97)

    I'm using the following code to pull records that are specific to an account number from a csv file and associating the pulled values with the same account number in an Excel spreadsheet. The program works fine for account numbers such as 123456 up to 1234567891-In other words six to ten digits. However, I have to work with some account numbers that are of the form 200010012345 and the program does not pull values for these numbers. Any suggestions?

    Sub BAOADO()

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim cellValueAmount As Currency
    Dim cellValueBAI As Double
    Dim cellValueBankAccount As Double

    cnn.Open "Provider=MSDASQL; driver={Microsoft Text Driver " & _
    "(*.txt; *.csv)};dbq=c:..."

    rst.Open "Select * from BOA.csv", cnn, adOpenStatic

    Do While Not rst.EOF
    On Error Resume Next

    cellValueAmount = rst.Fields("Amount").Value
    cellValueBAI = rst.Fields("BAI Code").Value
    cellValueBankAccount = rst.Fields("Account").Value

    If cellValueBAI = "15" Then

    With Range("Account")
    Set c = .Find(cellValueBankAccount, LookIn:=xlValues)
    If Not c Is Nothing Then
    firstaddress = c.Address
    Do
    If cellValueAmount = "0" Then
    c.Offset(rowoffset:=0, columnoffset:=4).Value = "0"
    Else
    c.Offset(rowoffset:=0, columnoffset:=4).Value = _
    cellValueAmount
    End If

    Set c = .FindNextŠ
    Loop While Not c Is Nothing And c.Address <> firstaddress

    End If

    End With

    End If
    rst.MoveNext
    Loop

    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing

    End Sub

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Help (VBA/Excel/97)

    Are these records completely unavailable from the ADO recordset?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ADO Help (VBA/Excel/97)

    I'm always nervous about the Double data type. You might be running into some kind of subtle rounding error. Can you do a string-to-string comparison instead?

  4. #4
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Help (VBA/Excel/97)

    No, they are available.

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Help (VBA/Excel/97)

    I know that if I change the data type classification, I sometimes get nonsensical results. When I use the double data type, I pull 17 out of twenty account numbers; it is only the large account numbers (13 digits longs) in which no information is pulled. What are you suggesting?

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Help (VBA/Excel/97)

    Have you tried a manual search for the number that is used in the Find method?

    You must make sure you explicitly set all options in the Find method, otherwise VBA will use the setting you have used last through the UI. Thus it may be that it is set to find entire cells, whereas you may want to look in part of the cell (or vice versa of course).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    2 Star Lounger
    Join Date
    Mar 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Help (VBA/Excel/97)

    I was trying to set this up to search and apply the account $ amount automatically. There are a lot of account numbers in several different files, so I wanted to automate the process as much as possible.

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ADO Help (VBA/Excel/97)

    > What are you suggesting?

    Unless the account number is a decimal value, I suggest that you not use a decimal data type (like Single or Double). Instead you could try a string or long.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Help (VBA/Excel/97)

    <<There are a lot of account numbers in several different files, so I wanted to automate the process as much as possible. >>

    Of course. My remark doesn't prevent that, I just meant to say that in your code, you need to set a value for every argument in the Find method.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ADO Help (VBA/Excel/97)

    A Long Integer data type would overflow with more than 10 digits (and for most 10 digit account numbers!)
    (long integer) range: -2,147,483,648 to 2,147,483,647
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: ADO Help (VBA/Excel/97)

    Since you don't do calculations with account numbers (addition, subtraction, etc.), they don't need to be handled as numbers. The only realistic way to handle this range of account numbers is by converting the account number to a string, particularly since account numbers are typically identifiers, rather than true numbers. Have you checked to be sure the long account numbers are actually present and in the expected location in the text file?
    Charlotte

  12. #12
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: ADO Help (VBA/Excel/97)

    Maybe we ought to rename it "longish" to indicate that really, it isn't all that long. <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •