[VBA Access Excel] Velocizzare importazione da file excel

[abellos]

Ciao a tutti,
volevo sapere se c'e' un modo per poter velocizzare l'importazione di un file excel su access di circa 20300 righe (che aumentano di circa 1000 o piu al mese) e 11 colonne.
Il codice e' il seguente

Private Sub bImportaFileControlli_Click()
    Dim ref As Reference
    
    ' 0 if Late Binding
    ' 1 if Reference to Excel set.
    #Const ExcelRef = 0
    #If ExcelRef = 0 Then ' Late binding EXE standAlone
        Dim objA As Object
        Dim objWB As Object
        Dim objWS As Object
        Set objA = CreateObject("Excel.Application")
        ' Remove the Excel reference if it is present   -   <=======
        On Error Resume Next
        Set ref = References!Excel
        If Err.Number = 0 Then
            References.Remove ref
        ElseIf Err.Number <> 9 Then 'Subscript out of range meaning not reference not found
            MsgBox Err.Description
            Exit Sub
        End If
    ' Use your own error handling label here
    ' On Error GoTo tagError '-  <=======
    #Else
        ' usate nell'IDE VB
        ' a reference to MS Excel <version number> Object Library must be specified
        Dim objA As Excel.Application
        Dim objWB As Excel.Workbook
        Dim objWS As Excel.Worksheet
        Set objA = New Excel.Application
    #End If
    
    Dim ColonnaPartenza As String
    Dim RigaPartenza As Integer
    Dim SQLAgg As String
    Dim SQLC As String, RSC As DAO.Recordset
    
    'For Each ref In Access.References
    '    Debug.Print ref.Name & " " & ref.FullPath & " " & ref.Major & "." & ref.Minor
    'Next ref
    'Exit Sub
    
    Dim dataEmail As String
    
    Dim fDialog As Office.FileDialog
    Dim varfile
    Dim FileDaImportare As String
    
    dataEmail = InputBox("Inserire data email di riferimento (es. 01/10/2015)")
    
    If dataEmail <> "" Then
        'controllo che se ci sono già pratiche con data email inserita
        If IsDate(Format(dataEmail, "mm/dd/yyyy")) = True Then
            SQLC = "SELECT TOP 1 dataEmail FROM pratiche_controlli_originale WHERE dataEmail='" & dataEmail & "' AND tipoImportazione=0"
            Set RSC = CurrentDb.OpenRecordset(SQLC)
            If RSC.EOF Then
                RSC.Close
                Set RSC = Nothing
                
                If dataEmail <> "" And IsDate(dataEmail) Then
                        ' Set up the File Dialog.
                        Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
                        
                        With fDialog
                        
                            ' Allow user to make multiple selections in dialog box
                            .AllowMultiSelect = False
                            
                            ' Set the title of the dialog box.
                            .Title = "Selezionare un file excel *.XLS"
                            
                            'imposto la cartella di partenza
                            .InitialFileName = "w:\prestitalia\"
                            
                            ' Clear out the current filters, and add our own.
                            .Filters.Clear
                            .Filters.Add "Microsoft Excel Workbooks", "*.xls;*.xlsx"
                            
                            ' Show the dialog box. If the .Show method returns True, the
                            ' user picked at least one file. If the .Show method returns
                            ' False, the user clicked Cancel.
                            If .Show = True Then
                            
                                'Loop through each file selected and add it to our list box.
                                For Each varfile In .SelectedItems
                                    FileDaImportare = varfile
                                Next
                                
                                'MsgBox FileDaImportare
                                                    
                                'objA.Visible = True
                                'apre il file
                                Set objWB = objA.Workbooks.Open(FileDaImportare)
                                'seleziona il foglio piu a sinistra (parte da 1)
                                Set objWS = objWB.Worksheets(1)
                                
                                'MsgBox objWS.Range("a1")
                                
                                'controllo se le colonne sono corrette altrimenti non importo il file
                                If InStr(LCase(objWS.Range("a1")), "pratica") < 1 Or IsNull(InStr(LCase(objWS.Range("a1")), "pratica")) Then
                                    MsgBox "Manca la colonna pratica nel file"
                                    objWB.Close SaveChanges:=False
                                    objA.Quit
                                    Set objA = Nothing
                                    Exit Sub
                                End If
                                
                                'If InStr(LCase(objWS.Range("j1")), "nota difformità") < 1 Or IsNull(InStr(LCase(objWS.Range("j1")), "nota difformità")) Then
                                '    MsgBox "Manca la colonna nota difformità nel file"
                                '    objWB.Close SaveChanges:=False
                                '    objA.Quit
                                '    Set objA = Nothing
                                '    Exit Sub
                                'End If
                                
                                'If InStr(LCase(objWS.Range("k1")), "nota avanzo") < 1 Or IsNull(InStr(LCase(objWS.Range("k1")), "nota avanzo")) Then
                                '    MsgBox "Manca la colonna nota avanzo nel file"
                                '    objWB.Close SaveChanges:=False
                                '    objA.Quit
                                '    Set objA = Nothing
                                '    Exit Sub
                                'End If
                                                    
                                Set DB = CurrentDb
                                
                                Set RS = DB.OpenRecordset("pratiche_controlli_originale")
                                
                                RigaPartenza = 2
                                
                                'Data = Format(ws.Range("f" & RigaPartenza), "mm/dd/yyyy")
                                                
                                DoCmd.OpenForm ("attendere")
                                PraticheGiaCaricate = ""
                                
                                'For I = RigaPartenza To 20000
                                I = RigaPartenza
                                While objWS.Range("a" & I) <> ""
                                    'inserisco nella tabella pratiche_assicurazioni_originale n° pratica e dataEmail
                                    'mi serve come tabella di appoggio per poi esportare il file delle pratiche che
                                    'fra spedisce a roma
                                    SQLOrig = "INSERT INTO pratiche_controlli_originale (pratica, dataEmail, tipoImportazione) " & _
                                    " VALUES (" & objWS.Range("a" & I) & ",#" & Format(CDate(dataEmail), "mm/dd/yyyy") & "#, 0)"
                                    CurrentDb.Execute (SQLOrig)
                                    
                                    DoEvents
                                    I = I + 1
                                'Next
                                Wend
                                
                                DoCmd.Close acForm, "attendere"
                                
                                RS.Close
                                DB.Close
                                
                                objWB.Close SaveChanges:=False
                                objA.Quit
                                Set objA = Nothing
                                
                                MsgBox "Importazione file terminata."
                            Else
                                'MsgBox "You clicked Cancel in the file dialog box."
                            End If
                        End With
                Else
                    MsgBox "Attenzione inserire una data valida"
                End If
            Else
                RSC.Close
                Set RSC = Nothing
                MsgBox "Attenzione esiste gia' una lista con la dataEmail inserita: " & dataEmail
            End If
        Else
            RSC.Close
            Set RSC = Nothing
            MsgBox "Attenzione la dataEmail inserita non e' valida: " & dataEmail
        End If
    End If
End Sub

[gibra]

Dovresti anche dare qualche dettaglio in più, perché non possiamo metterci a studiare tutti il codice.
Esattamente, qual'è la logica di elaborazione?

[abellos]

Ciao, hai ragione, scusami!
In pratica ho un file Excel con un elenco di pratiche e altri valori associati, io le devo caricare tutte in una tabella tramite access.
Prima di importare il file chiedo un valore che è la data di quando ci è stato spedito, e verifico che nella tabella non ci sia già qualcosa con questa data.
Se non è presente nulla ciclo il file Excel fino a che ci sono dei dati nella prima cella a sinistra (il numero di pratica) e li inserisco con un insert.
Però facendo in questo modo ci vogliono anche 15 minuti, volevo sapere se avete suggerimenti per fare un po' più in fretta questo passaggio.
grazie

[gibra]

Non sei chiarissimo...
- tu chiedi una data (Perché? A quale scopo?)
- se non è presente: cosa c'entra la pratica?
- Se invece è presente, che fai?
E' possibile vedere alcune righe del file?

I dati nel file Excel sono strutturati in formato 'tabella'?
Se sì, allora puoi aprire un recordset con i dati del foglio Excel e lavorare con quello.
In tal caso è notevolmente più veloce, perché hai a disposizione i metodi del recordset che sono ottimizzati, e non ti serve nemmeno aprire un'istanza di Excel.
Ci sono molti esempi sul web, prova a cercare.

In ogni caso, puoi velocizzare l'elaborazione impostando le seguenti proprietà dell'applicazione Excel:

    objA.Calculation = -4135  ' xlCalculationManual
    objA.DisplayAlerts = False

in cui objA rappresenta il nome dell'istanza di Excel.

[abellos]

Ciao gibra,
1 - chiedo una data perch� nella tabella dove importo i dati ho un campo dataEmail, che mi serve per sapere se ho gia' importato oppure no il file con la data della mail da cui il cliente me lo ha inviato
2 - se la data email non e' presente carico il file che mi e' stato inviato
3 - se e' presente non carico i dati e avviso l'utente che c'e' gi� qualcosa con quella data.

Il file ha queste colonne, io carico nella mia tabella access solamente la colonna pratica

Pratica	nota	data lavorazione	prest177	data invio nota a sistema	mail	data archiviata	archiviata	nota difformit�	nota avanzo	data spedizione plico	data arrivo fascicolo
1000254	Pratica controllata	03/05/2012	X			30/09/2016	X				01/04/2012
1000382	Pratica controllata	17/07/2012	X			17/11/2016	X				01/02/2012
1001216	Pratica controllata	23/02/2012	X			17/11/2016	X				02/01/1900

Ora cerco come aprire il file come recordset, grazie mille della dritta, credo sia proprio quello che mi serve.

Inoltre ogni settimana devo generare un file con le stesse colonne (devo inviare un report al cliente), anche qui ci vuole un sacco di tempo provo a vedere come fare anche con i recordset, grazie ancora

[gibra]

1 - chiedo una data perch� nella tabella dove importo i dati ho un campo dataEmail, che mi serve per sapere se ho gia' importato oppure no il file con la data della mail da cui il cliente me lo ha inviato

OK, quindi la data è quella dell'invio dell'email del cliente.
Ma l'utente che inserisce la data per la ricerca, in base a quale criterio la sceglie?
Vedi più avanti...

2 - se la data email non e' presente carico il file che mi e' stato inviato
3 - se e' presente non carico i dati e avviso l'utente che c'e' gi� qualcosa con quella data.

Seconda cosa:
hai detto che il file Excel aumenta di 1000 righe al mese, quindi mi pare ovvio che le righe precedenti sono già state caricate con email precedenti, e dovresti caricare solo le 1000 nuove.
Se sì, allora perché al punto (3) affermi invece di non caricare i dati?

[abellos]

OK, quindi la data è quella dell'invio dell'email del cliente.
Ma l'utente che inserisce la data per la ricerca, in base a quale criterio la sceglie?

L'utente inserisce la data della mail in cui era allegato il file che carico se non e' presente gia' qualcosa nella tabella di destinazione che ha la stessa data email.

Seconda cosa:
hai detto che il file Excel aumenta di 1000 righe al mese, quindi mi pare ovvio che le righe precedenti sono già state caricate con email precedenti, e dovresti caricare solo le 1000 nuove.
Se sì, allora perché al punto (3) affermi invece di non caricare i dati?

Hai ragione potrei caricare solo le ultime pratiche inserite nel file che mi inviano, pero' devo avere tutto il set di pratiche del file Excel per i rispettivi invii delle varie mail. Ad esempio il 13/06/2017 mi mandano un file con 3500 pratiche, il 16/06/2017 ne mandano un altro con 6000 pratiche, io devo avere nella tabella tutti i record sia del 13 che del 16, questo perché poi devo fare delle esportazioni sempre in base a quello che mi aveva inviato il cliente il 13 oppure il 16.
Probabilmente non mi sono spiegato bene, nella tabella destinazione ho una colonna dataemail e una colonna pratica, se importo il file della data 16/06/2017 ed e' gia presente nella tabella quella data avviso l'utente che non puo' caricare un altra volta il file di questa data.

Io ho trovato questo http://www.accessmvp.com/kdsnell/EXC...ort.htm#ImpSQL per quanto riguarda il recordset per Excel, si tratta di quello che mi serve?

grazie ancora della pazienza

[gibra]

Direi che il link è proprio azzeccato.
La sola cosa da tenere presente è quale versione di Excel usate, perché il link riporta la 8.0 che va bene per il formato XLS, mentre per il formato XLSX occorre la 12.0.