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

    export value only if ... (2000 sr 1)

    Is possible to be sure export only cells filled and not with a blank value, from column L M AB ?

    Sub INPS_TOTALE()
    Dim num As Integer

    Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, n As Long
    Set cn = New ADODB.Connection
    '****
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & gPROVADatabasePath2
    '****
    Set rs = New ADODB.Recordset
    rs.Open "INPS_02", cn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rs.INDEX = "PROVA29"
    r = 3 ' START LINE
    Do While Len(Range("A" & r).Formula) > 0
    With rs
    If Not rs.BOF Then
    rs.MoveFirst
    End If
    rs.Seek Array(Range("AC" & r)), adSeekFirstEQ
    If rs.EOF = True Then
    .AddNew ' NUOVO RECORD
    .Update ' SALVA RECORDS
    Else
    .Fields("PROVA12") = Range("L" & r).Value
    .Fields("PROVA13") = Range("M" & r).Value
    .Fields("PROVA28") = Range("AB" & r).Value
    .Update
    End If
    End With
    r = r + 1 ' PROSSIMA LINEA

    Loop

    Debug.Print n & " records added."

    'rs.Close
    'rs.Open "SELECT Count(PROVA29) As Cnt FROM INPS_02", cn, adOpenKeyset, adLockOptimistic, adCmdText
    'Range("H1") = rs!cnt

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

    ActiveSheet.Unprotect PASSWORD:="SAL21"
    Range("C1") = "OK"
    ActiveSheet.Protect PASSWORD:="SAL21"

    End Sub

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

    Re: export value only if ... (2000 sr 1)

    Work but peraphs you have lost a parenthesys...

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

    in other case solved...

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

    Re: export value only if ... (2000 sr 1)

    You're correct, I forgot a parenthesis. Sorry about that.

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

    Re: export value only if ... (2000 sr 1)

    Edited by HansV to correct error (missing parenthesis)

    Try changing

    Do While Len(Range("A" & r).Formula) > 0
    With rs
    ...
    End With
    r = r +1
    Loop

    to

    Do While Len(Range("A" & r).Formula) > 0
    If Not Trim(Range("L" & r) & Range("M" & r) & Range("AB" & r)) = "" Then
    With rs
    ...
    End With
    End If
    r = r +1
    Loop

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

    Re: export value only if ... (2000 sr 1)

    NO SORRY, NEVER! (From you)
    tKS.

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

    Re: export value only if ... (2000 sr 1)

    is possible to make the same condition on this macro or not is important to be sure import only a filled fileds?

    Sub IMPORTA_AGENZIE()

    Dim NomeDB As String
    Dim CONT As String
    Dim UTENTE As String
    Set ELENCO = Worksheets("A.T.CAMPANIA")
    NomeDB = gPROVADatabasePath

    ELENCO.Range("L3:M5000").ClearContents
    ELENCO.Range("AB3:AB5000").ClearContents
    ELENCO.Range("AD3:AD5000").ClearContents

    'CONT = 2 'heets("A.T.CAMPANIA").Cells(65536, 1).End(xlUp).Row

    Dim StringaDiConnessione
    StringaDiConnessione = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & NomeDB & ";'"
    Dim OggettoConnessione As Object, OggettoRecordset As Object
    Set OggettoConnessione = CreateObject("ADODB.Connection")
    OggettoConnessione.Open StringaDiConnessione
    Set OggettoRecordset = CreateObject("ADODB.Recordset")
    Set OggettoRecordset = OggettoConnessione.Execute("SELECT * from INPS_02")

    Do While Not OggettoRecordset.EOF

    ID = OggettoRecordset("PROVA29")
    Set Found_ID = Sheets("A.T.CAMPANIA").Columns("AC:AC").Find(ID, lookat:=xlWhole)
    If Found_ID Is Nothing Then

    Else

    ELENCO.Range("L" & Trim(Str(Found_ID.Row))).Value = OggettoRecordset("PROVA12")
    ELENCO.Range("M" & Trim(Str(Found_ID.Row))).Value = OggettoRecordset("PROVA13")
    ELENCO.Range("AB" & Trim(Str(Found_ID.Row))).Value = OggettoRecordset("PROVA28")

    If OggettoRecordset("PROVA28") <> "" Then
    UTENTE = OggettoRecordset("PROVA28")
    Range("AD" & Trim(Str(Found_ID.Row))).Value = Application.WorksheetFunction.VLookup(UTENTE, Worksheets("TABELLA").Range(Worksheets("TABELLA"). Range("G2"), Worksheets("TABELLA").Range("H1000").End(xlUp)), 2, False)
    End If

    End If

    OggettoRecordset.MoveNext
    Loop

    OggettoRecordset.Close
    OggettoRecordset.Open "SELECT Count(PROVA29) As Cnt FROM INPS_02", StringaDiConnessione, adOpenKeyset, adLockOptimistic, adCmdText
    Range("I1") = OggettoRecordset!cnt

    OggettoRecordset.Close
    Set OggettoRecordset = Nothing
    OggettoConnessione.Close
    Set OggettoConnessione = Nothing

    ActiveWorkbook.Save

    MsgBox (Range("AD1").Value & " SEGNALZIONI SU UN TOTALE DI " & Range("H1").Value & " RATE")

    End Sub

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

    Re: export value only if ... (2000 sr 1)

    You will have to decide whether it is important or not, I can't decide that for you. If so, the code would be similar.

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

    Re: export value only if ... (2000 sr 1)

    is this correct? :

    ............
    ID = OggettoRecordset("PROVA29")
    Set Found_ID = Sheets("RATE").Columns("AC:AC").Find(ID, lookat:=xlWhole)
    If Found_ID Is Nothing Then

    Else
    'my modify''''''''''''
    If Not Trim(OggettoRecordset("PROVA12") & OggettoRecordset("PROVA13") & OggettoRecordset("PROVA28")) = "" Then
    'my modify'''''''''''

    ELENCO.Range("L" & Trim(Str(Found_ID.Row))).Value = OggettoRecordset("PROVA12")
    ELENCO.Range("M" & Trim(Str(Found_ID.Row))).Value = OggettoRecordset("PROVA13")
    ELENCO.Range("AB" & Trim(Str(Found_ID.Row))).Value = OggettoRecordset("PROVA28")

    If OggettoRecordset("PROVA28") <> "" Then
    UTENTE = OggettoRecordset("PROVA28")
    Range("AD" & Trim(Str(Found_ID.Row))).Value = Application.WorksheetFunction.VLookup(UTENTE, Worksheets("TABELLA").Range(Worksheets("TABELLA"). Range("G2"), Worksheets("TABELLA").Range("H1000").End(xlUp)), 2, False)
    End If
    'my modify'''''''''''
    End If
    'my modify'''''''''''
    End If
    OggettoRecordset.MoveNext
    Loop

    ............

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

    Re: export value only if ... (2000 sr 1)

    Looks OK.

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

    Re: export value only if ... (2000 sr 1)

    Tks....

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

    Re: export value only if ... (2000 sr 1)

    hi, Hans...
    I have this sheet TABELLA in column A are the code 4500. 4501...ecc...

    Is possible during the importing (see the code in this post Sub IMPORTA_AGENZIE() ):

    1) summ the number of lines from field PROVA1 (Here are present many lines indetified with code 4500, 4501... ecc...) and put the result for each code into column G of tabella sheet.
    2) count the value filled from each line identified from code into field PROVA12 and insert the totale vlue counted into column H of TABELLA sheet
    example:
    PROVA1 PROVA12
    4500 blank
    4500 filled
    4500 filled
    4501 blank
    4501 filled
    ....
    insert into cell G2 "3"
    insert into cell G3 "2"
    ....
    ecc...

    insert into H2 "2"
    insert into H3 "1"
    .....

    i hope i am clear

    tks.

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

    Re: export value only if ... (2000 sr 1)

    Below the line

    ELENCO.Range("AB" & Trim(Str(Found_ID.Row))).Value = OggettoRecordset("PROVA28")

    insert these new lines:

    Dim oCell As Range
    Dim varIndex
    ' Value of PROVA1
    varIndex = OggettoRecordset("PROVA1")
    ' Find value in column A of TABELLA
    Set oCell = Worksheets("TABELLA").Range("A:A").Find _
    (What:=varIndex, LookAt:=xlWhole)
    If Not oCell Is Nothing Then
    ' If found, increase count in column G (6 to the right of A)
    oCell.Offset(0, 6) = oCell.Offset(0, 6) + 1
    If Not IsNull(OggettoRecordset("PROVA12")) Then
    ' If not null, increase count in column H (7 to the right of A)
    oCell.Offset(0, 7) = oCell.Offset(0, 7) + 1
    End If
    End If

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

    Re: export value only if ... (2000 sr 1)

    Sorry Hans... but i have post a wrong question.

    i dont wount to use the mdb for this prob but use a sheet, and i would want to use a separate macro...

    New question...
    1) summ the number of lines from COLUMN A OF SHEET RATE (Here are present many lines indetified with code 4500, 4501... ecc...) and put the result for each code into column G of tabella sheet.
    2) count the value filled from each line identified from code into field COLUM AB OF SHEET RATE and insert the total value counted into column H of TABELLA sheet

    example:
    COL A COL AB
    4500 blank
    4500 filled
    4500 filled
    4501 blank
    4501 filled
    ....
    insert into cell TABELLA G2 "3"
    insert into cell TABELLA G3 "2"
    ....
    ecc...

    insert into TABELLA H2 "2"
    insert into TABELLA H3 "1"
    .....

    i hope i am clear

    tks.


    in effect inested to count into recorset of mdb count into column of sheet

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

    Re: export value only if ... (2000 sr 1)

    Enter the following formula in G2:

    =COUNTIF(RATE!A:A,A2)

    Enter the following formula in H2:

    =SUMPRODUCT((RATE!$A$1:$A$10000=A2)*(RATE!$AB$1:$A B$10000<>""))

    and fill down as far as needed. COUNTIF is CONTA.SE and SUMPRODUCT is MATR.SOMMA.PRODOTTO in Italian.

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

    Re: export value only if ... (2000 sr 1)

    EXCELLENT!...

Posting Permissions

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