vba macro za export podataka iz excela u access

[ zafa @ 02.04.2012. 21:33 ] @

Probao sam sa sledećim primerom, ali mi prijavljuje grešku

u excelu imam podatke u kolonama B do E i sa sledećim kodom sam pokušao da exportujem podatke iz excela u access ali prijavljuje grešku kod
db As Database

Sub p()
Dim db As Database, rs As Recordset, r As Long
Set db = OpenDatabase("C:\FPC\od3.mdb")
' open the database
Set rs = db.OpenRecordset("od31", dbOpenTable)
' get all records in a table
r = 3 ' the start row in the worksheet
Do While Range("B" & r).Value > 0
' repeat until first empty cell in column A
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("ID") = Range("B" & r).Value
.Fields("prezime") = Range("C" & r).Value
.Fields("ime") = Range("D" & r).Value
.Fields("ocena") = Range("E" & r).Value
' add more fields if necessary...
.Update ' stores the new record
End With
r = r + 1 ' next row
Loop
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

[ FOX028 @ 03.04.2012. 08:09 ] @

Problem je u tome sto ti nisi cekirao referencu na Microsoft DAO 3.6 Object Library (Tools=>References). U prilogu ti saljem malo izmenjen ovaj tvoj kod zajedno sa Access bazom. Potrebno je da oba fajla budu u istom folderu ili ako ne zelis da ti budu u istom folderu moraces u kodu promenljivoj dbName da dodelis adresu kao string gde ti se nalazi baza.

Code:
Sub ExportAccess()

Rem *********************************************************************
Rem Potrebno je da cekiras referencu na Microsoft DAO 3.6 Object Library*
Rem Tools=>References                                                   *
Rem *********************************************************************

Dim db As Database
Dim rs As Recordset
Dim r As Long
Dim dbName As String    'adresa Access baze

'jedno od ova dva je potrebno da bude aktivno, nikako oba
dbName = ActiveWorkbook.Path & "\Ocene.mdb"             'ako je baza u istom folderu gde je i Excel fajl
'dbName = "C:\Users\USER\Desktop\Vezba\Export\Ocene.mdb" 'ovde mozes rucno uneti adresu Access baze

Set db = DBEngine.OpenDatabase(dbName)
Set rs = db.OpenRecordset("Ocene", dbOpenDynaset)

' get all records in a table
r = 2 ' the start row in the worksheet
Do While Cells(r, 2) > 0
' repeat until first empty cell in column A
    With rs
        .AddNew ' create a new record
        ' add values to each field in the record
        .Fields("ID") = Cells(r, 2)
        .Fields("prezime") = Cells(r, 3)
        .Fields("ime") = Cells(r, 4)
        .Fields("ocena") = Cells(r, 5)
        ' add more fields if necessary...
        .Update ' stores the new record
    End With
    r = r + 1 ' next row
Loop

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

[ srdrazic @ 03.04.2012. 21:46 ] @

Pa zar nije lakše iz access-a importovati excel tabelu pa čak i linkovati?

[ mld @ 04.04.2012. 07:41 ] @

Ovo bi moglo još funkcionalnije biti kada bi se dodalo da to uradi sa više sheetova, jer to ne može običnim importom iz accesa.