Importare Foglio Excel in Access

Question

Buona sera Forum,

sono alle prese con un rompicapo.

Ho il seguente codice VBA che funziona alla perfezione in un file Excel che uso tutt'ora da diversi anni:

Sub CaricaDbAccess()

    Dim db As Database    'Database

    Dim rst As Recordset   'Recordset

    Dim qde As Querydef   'QueryDef

    ' Conto le colonne del foglio Excel

    colonne = 1

    Do While Cells(1, colonne) <> ""

        colonne = colonne + 1

    Loop

    colonne = colonne - 1                                               ' Perché la prima contiene l'ID e l'ultima colonna è vuota

    ' Il percorso del tuo data base

    Set db = OpenDatabase("C:\ ecommerce.mdb")

    db.Execute "DELETE * FROM PRODOTTI;"

    Set rst = db.OpenRecordset("PRODOTTI")       ' il nome della tabella in cui vuoi scrivere i dati

    riga = 2                                                            ' salto la riga 1 se contiene nome dei campi nel foglio di excel

    Do While Cells(riga, 1) <> ""                                   ' L'ultima cella nella colonna A deve contenere il testo **fine** per fermare l'esecuzione

    rst.AddNew                                                                 ' aggiungi nuovo record

   ' al posto dei nomi dei campi "Nome" ecc.. si può utilizzare un indice che in questo caso comincia da 1 perché l'indice 0 è riservato all'ID che non è scrivibile.

    For i = 1 To colonne                                                   ' L'indice i inizia da 1 perché si deve saltare l'ID

        rst.Fields(i - 1) = Cells(riga, i)

    Next

    rst.Update                                                                    ' Aggiorna (scrive il record nel DB)

    riga = riga + 1                                                                ' incrementa il numero di riga

    Loop

    'close the objects

    rst.Close

    db.Close

    'destroy the variables

    Set rst = Nothing

    Set db = Nothing

End Sub

è sopraggiunta l'esigenza di lavorare a un nuovo file, copio la stessa, identica macro ma non funziona.

Mi restituisce il seguente errore:

Errore di compilazione

Tipo definito dall'utente non definito.

L'errore è in:

Sub CaricaDbAccess()

    Dim db As Database    'Database

riuscite a darmi una mano per la soluzione? Grazie.

Answer 1

ciao Vatss,

Da Access:

se il foglio di excel è ben impostato per essere una tabella normalizzata, puoi semplicemente collegarlo anziché importarlo,

Alternativa, prova l'importazione guidata.

Ancora, da Excel, non ho controllato accuratamente il codice, prova semplicemente in questo modo :

    Dim db As dao.Database    'Database

    Dim rst As dao.,ecordset   'Recordset

    Dim qde As dao.Querydef   'QueryDef

controllando nei riferimenti, dall'edito di VBA, strumenti, riferimenti ci sia la spunta su Microsoft Office xx.0 Access database Engine Object libray.

Facci sapere!

Ciao, Sandro.

Answer 2

ciao Valerio,

credo sia normale ragionando con la logica di Excel ragionare riga per riga, l'approccio cambia invece se ci rapportiamo ad un database relazionale.

suppondo la tabella di destinazione come quella che segue, presa dal database di esempio NorthWind :

e questo per quanto al file di excel di origine :

prova per esempio ad eseguire le due soluzione che seguono.

Option Explicit

Private Const strPathDb As String = "C:\database.accdb" '<------personalizza

Private Function fileExists(strFullPath As String) As Boolean

    On Error Resume Next

    fileExists = ((GetAttr(strFullPath) And vbDirectory) = 0)

End Function

Public Sub exportTOAccess3()

If Not fileExists(strPathDb) Then Exit Sub

On Error GoTo errorHandler

Dim strSql      As String

Dim wbk         As Excel.Workbook

Dim wsh         As Excel.Worksheet

Dim conn        As ADODB.Connection

Dim lngRecords  As Long

Dim lngLastRow  As Long

Dim lngLastCol  As Long

Dim rngAddress  As Excel.Range

Const cnn         As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPathDb

Const lngFirstRow As Long = 1

Const wshName     As String = "foglio1"

Set wbk = ThisWorkbook

Set wsh = wbk.Worksheets(wshName)

With wsh

    lngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row

    lngLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

    Set rngAddress = .Range(Cells(1, 1), Cells(lngLastRow, lngLastCol))

End With

Set conn = New ADODB.Connection

With conn

    .Open cnn

     strSql = "INSERT INTO OrdiniImport " _

            & "SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & wbk.FullName & "].[" & wsh.Name & "$" &  Replace(rngAddress.Address, "$", "") & "]"

    .Execute CommandText:=strSql, RecordsAffected:=lngRecords, Options:=-1

    .Close

End With

VBA.MsgBox prompt:="Ho inserito nella tabella OrdiniImport " & lngRecords & " Righe.", _

           Buttons:=vbOKOnly, _

           Title:="Informazione"

exitErrorHandler:

    Set wsh = Nothing

    Set wbk = Nothing

    Set rngAddress = Nothing

    Exit Sub

errorHandler:

    With Err

            VBA.MsgBox "ERR#" & CStr(.Number) _

                & vbNewLine & .Description _

                , vbOKOnly Or vbCritical

     End With

     Resume exitErrorHandler

End Sub

oppure :

Public Sub AccessExecute()

   On Error GoTo errorHandler

    Dim app_Access As Access.Application

    Dim wbk         As Excel.Workbook

    Dim wsh         As Excel.Worksheet

    Dim lngRecords  As Long

    Dim lngLastRow  As Long

    Dim lngLastCol  As Long

    Dim rngAddress  As Excel.Range

    Dim strSql      As String

    Dim dbs         As DAO.Database

    Set wbk = ActiveWorkbook

    Set wsh = wbk.Worksheets(1)

    With wsh

        lngLastRow = .Range("A" & .Rows.Count).End(xlUp).Row

        lngLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

        Set rngAddress = .Range(Cells(1, 1), Cells(lngLastRow, lngLastCol))

    End With

    strSql = "INSERT INTO OrdiniImport " _

            & "SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & wbk.FullName & "].[" & wsh.Name & "$" & Replace(rngAddress.Address, "$", "") & "]"

    Set app_Access = Access.Application

    Set dbs = app_Access.DBEngine(0).OpenDatabase(strPathDb)

    With dbs

        .Execute Query:=strSql, Options:=&H80

        lngRecords = .RecordsAffected

    End With

VBA.MsgBox prompt:="Ho inserito nella tabella OrdiniImport " & lngRecords & " Righe.", _

           Buttons:=vbOKOnly, _

           Title:="Informazione"

exitErrorHandler:

    Set wsh = Nothing

    Set wbk = Nothing

    Set rngAddress = Nothing

    dbs.Close

    Set app_Access = Nothing

    Exit Sub

errorHandler:

    With Err

            VBA.MsgBox "ERR#" & CStr(.Number) _

                & vbNewLine & .Description _

                , vbOKOnly Or vbCritical

     End With

     Resume exitErrorHandler

End Sub

meglio la prima.

Facci sapere.

Ciao, Sandro.

Answer 3

hai provato a controllare se magari non fossero già stati spuntati…?

Ti consiglierei, visto il codice che hai mostrato, ora lo vedo meglio di provare a migliorarlo, sfruttando Ado e/o soluzioni setBased tipiche dell'utilizzo dei database.

Credo possa diventare in tal modo più robusto e rigoroso.

Di Excel capisco quasi zero...ma se vuoi ci proviamo...!

ciao, Sandro.

Si certo è la prima cosa che ho fatto.

Circa il tuo consiglio certamente si, ogni miglioramento non può che essere ben accetto,

proviamoci.

Ciao Valerio

Answer 4

ciao Vatss,

[…]

per aver risposto e per la soluzione.

L'errore è nei riferimenti dell'Editor VBA.

Ora la domanda è perchè nell'altro file quel riferimento non è necessario?

[…]

prego!

hai provato a controllare se magari non fossero già stati spuntati…?

Ti consiglierei, visto il codice che hai mostrato, ora lo vedo meglio di provare a migliorarlo, sfruttando Ado e/o soluzioni setBased tipiche dell'utilizzo dei database.

Credo possa diventare in tal modo più robusto e rigoroso.

Di Excel capisco quasi zero...ma se vuoi ci proviamo...!

ciao, Sandro.

Answer 5

Ciao Sandro Peruz e grazie per aver risposto e per la soluzione.

L'errore è nei riferimenti dell'Editor VBA.

Ora la domanda è perchè nell'altro file quel riferimento non è necessario?