Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    hummmmm gosth in macro... (2000 sr 1)

    Edited by HansV - the Script font is NOT meant for code - it makes code unreadable. Use the <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags instead.

    ... why the macro import a blank record (4500-SK-5002.....) is blank in column AC ...
    <pre>Sub INQUIRY()

    Sheets("INQUIRY").Select

    Dim CN As ADODB.Connection, rs As ADODB.Recordset, r As Long, N As Long
    Set CN = New ADODB.Connection
    '**** Substitute the correct path ************
    CN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & gPROVADatabasePath2
    '**********************************************
    Set rs = New ADODB.Recordset
    rs.Open "INQUIRY", CN, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rs.INDEX = "PROVA29"
    r = 5 ' the start row in the worksheet
    Do While Len(Range("A" & r).Formula) > 0

    'If Not Trim(Range("L" & r) & Range("M" & r) & Range("AB" & r)) = "" Then

    With rs
    If Not rs.BOF Then
    rs.MoveFirst
    End If
    rs.Seek Array(Range("AC" & r)), adSeekFirstEQ
    If rs.EOF = True Then

    .AddNew ' create a new record
    'add values to each field in the record
    .Fields("PROVA1") = Range("A" & r).Value
    .Fields("PROVA2") = Range("B" & r).Value
    .Fields("PROVA3") = Range("C" & r).Value
    .Fields("PROVA4") = Range("D" & r).Value
    .Fields("PROVA5") = Range("E" & r).Value
    .Fields("PROVA6") = Range("F" & r).Value
    .Fields("PROVA7") = Range("G" & r).Value
    .Fields("PROVA8") = Range("H" & r).Value
    .Fields("PROVA9") = Range("I" & r).Value
    .Fields("PROVA10") = Range("J" & r).Value
    '.Fields("PROVA11") = Range("K" & r).Value
    '.Fields("PROVA12") = Range("L" & r).Value
    '.Fields("PROVA13") = Range("M" & r).Value
    '.Fields("PROVA14") = Range("N" & r).Value
    '.Fields("PROVA15") = Range("O" & r).Value
    '.Fields("PROVA16") = Range("P" & r).Value
    '.Fields("PROVA17") = Range("Q" & r).Value
    '.Fields("PROVA18") = Range("R" & r).Value
    '.Fields("PROVA19") = Range("S" & r).Value
    '.Fields("PROVA20") = Range("T" & r).Value
    '.Fields("PROVA21") = Range("U" & r).Value
    '.Fields("PROVA22") = Range("V" & r).Value
    '.Fields("PROVA23") = Range("W" & r).Value
    '.Fields("PROVA24") = Range("X" & r).Value
    '.Fields("PROVA25") = Range("Y" & r).Value
    '.Fields("PROVA26") = Range("Z" & r).Value
    '.Fields("PROVA27") = Range("AA" & r).Value
    '.Fields("PROVA28") = Range("AB" & r).Value
    .Fields("PROVA29") = Range("AC" & r).Value
    '.Fields("PROVA30") = Range("AD" & r).Value

    .Update ' stores the new record

    End If
    End With
    'End If

    r = r + 1 ' next row
    Loop

    Debug.Print N & " records added."

    rs.Close
    rs.Open "SELECT Count(PROVA29) As Cnt FROM INQUIRY", CN, _
    adOpenKeyset, adLockOptimistic, adCmdText
    Sheets("INQUIRY").Range("J3") = rs!cnt

    rs.Close
    Set rs = Nothing
    CN.Close
    Set CN = Nothing

    End Sub</pre>


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: hummmmm gosth in macro... (2000 sr 1)

    What exactly is your question?

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hummmmm gosth in macro... (2000 sr 1)

    In effect i would want to export only the line in sheet INQUIRY that contain a value in column AC...
    In this case the line 7 in sheet not have a index in AC but that line is imported from macro, why?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: hummmmm gosth in macro... (2000 sr 1)

    If you don't want to import rows with a blank value in column AC, you should test for that:

    If Not Trim(Range("AC" & r)) = "" Then
    With rs
    ...
    End With
    End If

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: hummmmm gosth in macro... (2000 sr 1)

    TKS!...
    but sorry for your time, i have see with attention my code and already have a similar condition:

    ....
    'If Not Trim(Range("L" & r) & Range("M" & r) & Range("AB" & r)) = "" Then
    ....


    .. i am a stupid!
    Sorry Hans.

Posting Permissions

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