In questo articolo vedremo come realizzare una procedura in VBA (Visual Basic Application), la quale carica i dati da un file Excel, e li inserisce in un foglio Excel in cui viene eseguita la query.
Si crea un file Excel, nel quale andremo a rilevare i dati, questo file Excel avrà due colonne, in A1 avremo l’intestazione Nome, mentre in B1 il valore Cognome.
Inserite a vostro piacimento i dati.
Aprite un nuovo file Excel il quale caricherà i dati dal file precedentemente creato. Dopo aver aperto il programma Microsoft Excel, tramite la voce di menu “Sviluppo” fate click sul pulsante “Visual Basic” .
Aggiungere al progetto un riferimento alla libreria “Microsoft ActiveX Data Object 2.8 library” in questo modo possiamo utilizzare ADO, per aggiungere tale riferimento, fare click sulla voce “Riferimenti” e successivamente mettere la spunta a tale voce.
Stesura del codice
Si creano le varie funzioni per la gestione dei dati.
La seguente funzione permette di rilevare la stringa di connessione del file Excel dove rilevare i dati.
'''''''''''''''''''''''''''''''''''''''''''''''''''
'Funzione che restituisce il pecorso della fonte dati dove rilevare i dati
'
'''''''''''''''''''''''''''''''''''''''''''''''''''
Function OttieniConnectionStringDatiOrigine() As String
Dim percorso As String
percorso = Worksheets("Foglio1").Range("B2")
Dim ConnectionString As String
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=c:\Dati.xlsx;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;MaxScanRows=1;"""
OttieniConnectionStringDatiOrigine = ConnectionString
End Function
Ora si crea la funzione Macro, ossia la “Sub” che verrà richiamata dalla maschera “Macro” di Excel, che avvia la procedura di importazione dati.
La procedura riportata qui di seguito, permette di avviare il caricamento, dopo aver rilevato i dati dal file Excel, scorre il singolo recordSet per inserirlo nelle prime due celle del file Excel di Destinazione.
Qui di seguito il codice completo di tale procedura.
Option Explicit
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Macro per importare i dati
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sub ImportaDati()
On Error GoTo errore
'Oggetti per estrapolare i dati
Dim ConEstrapolaDati As New ADODB.Connection
ConEstrapolaDati.Open OttieniConnectionStringDatiOrigine
'RecordSet per il rilevamento dei dati
Dim RecEstrapolaDati As New ADODB.Recordset
Dim QuerySql As String
QuerySql = "Select Nome, Cognome From [Foglio1$]"
RecEstrapolaDati.Open QuerySql, ConEstrapolaDati, adOpenKeyset, adLockPessimistic, 1
'verifico se sono presenti dati
If RecEstrapolaDati.RecordCount > 0 Then
If Not RecEstrapolaDati.EOF Then
Dim Riga As Integer
Riga = 2
'Inserisco i valori per ogni colonna
Dim ContaRighe As Integer
For ContaRighe = 0 To RecEstrapolaDati.RecordCount - 1
Cells(Riga, 1) = RecEstrapolaDati(0) 'nome
Cells(Riga, 2) = RecEstrapolaDati(1) 'cognome
Riga = Riga + 1
RecEstrapolaDati.MoveNext
Next ContaRighe
If RecEstrapolaDati.State = adStateOpen Then
RecEstrapolaDati.Close
End If
If ConEstrapolaDati.State = adStateOpen Then
ConEstrapolaDati.Close
End If
Set RecEstrapolaDati = Nothing
Set ConEstrapolaDati = Nothing
Exit Sub
End If
Else
MsgBox "Non ci sono dati da caricare.", vbInformation, "ImportaDati"
End If
RecEstrapolaDati.Close
ConEstrapolaDati.Close
Set RecEstrapolaDati = Nothing
Set ConEstrapolaDati = Nothing
Exit Sub
'Gestione errore
errore:
MsgBox "ERRORE: " & Err.Description, vbCritical + vbOKOnly, "ImportDati"
On Error GoTo 0
End Sub
Il codice crea due oggetti, uno di tipo Recordset, che permette la gestione dei dati, mentre l'oggetto Connection, ha il compito di gestire la connessione al file Excel.
Tramite l'oggetto CELLS di VBA, andiamo inserire il valore del recordset nella cella.
Da notare la query SQL, nella quale il nome della tabella, o meglio del foglio è indicato tra parentesi quadrate con il simbolo dollaro.
Conclusioni
L’articolo ha preso in esame un caso reale che si può verificare, uno scenario di realizzare procedure interne al pacchetto Office, ed in particolare di Excel per l’auto caricamento dei dati.
La procedura in VBA si può estendere e renderla più flessibile.
Tramite il link
download potete scaricare i file di esempio.