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

    import from .dbf (2000 sr 1)

    this file contain a wbook and a macro.
    My problem is to modify the connection string and other parameter, because the vale to import not are into and MDB file but into and .DBF file with the recorset name is the same of the MDB.
    The Dbf file is the in format DbaseIII.

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

    Re: import from .dbf (2000 sr 1)

    Excel can open DBase files directly, so there should be no need to use ADO to read them.

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

    Re: import from .dbf (2000 sr 1)

    ok.... but is who is the connection string?
    ... and is possible to maintain the same architecture of old code?

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

    Re: import from .dbf (2000 sr 1)

    Try

    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Folder;Extended Properties=dBASE III;User ID=Admin;Password="

    where C:Folder is the folder containing the DBase file. When opening the recordset, provide the name of the DBase file.

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

    Re: import from .dbf (2000 sr 1)

    NATURALLY IT WORK PERFECT!
    only one...
    But in the old macro i have this condition to test the dupes hwo to change?

    'ID = OggettoRecordset("SERVIZIO")
    'Set Found_ID = Sheets("L0785_PAGATI").Columns("S:S").Find(ID, lookat:=xlWhole)
    'If Found_ID Is Nothing Then

    and when the mcaro go on this line i have error:

    ELENCO.Range("S" & Trim(Str(CONT))).Value = OggettoRecordset("SERVIZIO")

    i am sure there is!


    Sub IMPORTA_PAGATI()
    ThisWorkbook.Activate
    Set ELENCO = Worksheets("L0785_PAGATI")
    CONT = FirstFree("L0785_PAGATI", "A", 6)

    Dim StringaDiConnessione
    StringaDiConnessione = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:MACRO;Extended Properties=dBASE III;User ID=Admin;Password="
    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 PAGATI")

    Do While Not OggettoRecordset.EOF

    'ID = OggettoRecordset("SERVIZIO")
    'Set Found_ID = Sheets("L0785_PAGATI").Columns("S:S").Find(ID, lookat:=xlWhole)
    'If Found_ID Is Nothing Then

    ELENCO.Range("A" & Trim(Str(CONT))).Value = OggettoRecordset("DATA_CONT")
    ELENCO.Range("B" & Trim(Str(CONT))).Value = OggettoRecordset("DIP")
    ELENCO.Range("C" & Trim(Str(CONT))).Value = OggettoRecordset("COD_BATCH")
    ELENCO.Range("D" & Trim(Str(CONT))).Value = OggettoRecordset("C_C")
    ELENCO.Range("E" & Trim(Str(CONT))).Value = OggettoRecordset("NOMINATIVO")
    ELENCO.Range("F" & Trim(Str(CONT))).Value = OggettoRecordset("CAUS")
    ELENCO.Range("G" & Trim(Str(CONT))).Value = OggettoRecordset("DARE")
    ELENCO.Range("H" & Trim(Str(CONT))).Value = OggettoRecordset("AVERE")
    ELENCO.Range("I" & Trim(Str(CONT))).Value = OggettoRecordset("VAL")
    ELENCO.Range("J" & Trim(Str(CONT))).Value = OggettoRecordset("SPORT_MIT")
    ELENCO.Range("K" & Trim(Str(CONT))).Value = OggettoRecordset("ANOM")
    ELENCO.Range("L" & Trim(Str(CONT))).Value = OggettoRecordset("DESCR")
    ELENCO.Range("M" & Trim(Str(CONT))).Value = OggettoRecordset("CRO")
    ELENCO.Range("N" & Trim(Str(CONT))).Value = OggettoRecordset("ABI")
    ELENCO.Range("O" & Trim(Str(CONT))).Value = OggettoRecordset("CAB")
    ELENCO.Range("P" & Trim(Str(CONT))).Value = OggettoRecordset("PAG_IMP")
    ELENCO.Range("Q" & Trim(Str(CONT))).Value = OggettoRecordset("NR_ASS")
    ELENCO.Range("R" & Trim(Str(CONT))).Value = OggettoRecordset("MT")
    ELENCO.Range("S" & Trim(Str(CONT))).Value = OggettoRecordset("SERVIZIO")
    CONT = CONT + 1
    'End If
    OggettoRecordset.MoveNext

    Loop

    Range("A7").Select

    OggettoRecordset.Close
    Set OggettoRecordset = Nothing
    OggettoConnessione.Close
    Set OggettoConnessione = Nothing
    End Sub
    Public Function FirstFree(Tabella, Colonna, RigaIniziale)
    CONT = RigaIniziale
    lTest = False
    Set Check = Worksheets(Tabella).Range(Colonna + CStr(CONT))
    Do While IsEmpty(Check) <> True
    Set Check = Worksheets(Tabella).Range(Colonna + CStr(CONT))
    If IsEmpty(Check) <> True Then
    CONT = CONT + 25
    Else
    x = CONT
    Do Until lTest = True
    Set oTest = Worksheets(Tabella).Range(Colonna + CStr(x))
    If IsEmpty(oTest) = True Then
    x = x - 1
    Else
    FirstFree = x + 1
    lTest = True
    End If
    Loop
    End If
    Loop
    End Function

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

    Re: import from .dbf (2000 sr 1)

    ops...
    the little .dbf file

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

    Re: import from .dbf (2000 sr 1)

    Are you sure you used this exact DBF file? The code works for me without an error with the DBF file you attached (with the correct path on my PC)

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

    Re: import from .dbf (2000 sr 1)

    OPS...
    I have i this days the head in the sky... Many project to menage in the same time;-)
    You have reason naturally.
    Naturally you................. ecc....
    Tks as usual Hans.

    Ah... Dubt, but i i dont know the version dbase file if is III or IV ecc... (the dbf file have always the same estention .dbf) the string connection is good for all type file?

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

    Re: import from .dbf (2000 sr 1)

    If you don't know the version, I would use DBase IV in the connection string; that seems to be the format most commonly used by other applications writing DBase files.

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

    Re: import from .dbf (2000 sr 1)

    Godd morning Hans...
    All work fine
    but, if in the dir C:Folder are present many file with .dbf exstention, hoiw i set a single file?
    for example in die Folder are:
    test1.dbf
    test2.dbf
    test3.bdf
    ....

    and if i would want to select test2.dbf


    ???

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

    Re: import from .dbf (2000 sr 1)

    In the code higher up in this thread, you have a line

    Set OggettoRecordset = OggettoConnessione.Execute("SELECT * from PAGATI")

    You can replace PAGATI with the name of the DBase file you want to open, without the .dbf extension:

    Set OggettoRecordset = OggettoConnessione.Execute("SELECT * from test2")

    Alternatively, you could use

    OggettoRecordset.Open "test2"

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

    Re: import from .dbf (2000 sr 1)

    GOOD...

Posting Permissions

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