diminuire tempo di calcolo

[scanacc]

Carissimi, ogni 100 righe il computer mi mette 14 secondi per calcolare. Dato che devo arrivare a 600 ci mette quasi 2 minuti.
C'è un modo per velocizzare il tutto?
Cosa ho fatto?
PREPARAZIONE:
Ho scaricato tutta una serie di FILE di tutti i miei clienti e li ho inseriti nella cartella C:\CONCEPT
'C:\CONCEPT\[RUPM-Estrazione NOME1.xlsx]FOGLIO1'!A1
'C:\CONCEPT\[RUPM-Estrazione NOME2.xlsx]FOGLIO1'!A1
'C:\CONCEPT\[RUPM-Estrazione NOME3.xlsx]FOGLIO1'!A1

Dopo di che in un nuovo foglio di lavoro dove voglio che mi compaiano i dati che voglio pescare, ho inserito la formula CONCATENA in modo che quando inserisco nella cella “V1” il nome del cliente “scanacc”, nella cella “G1” compaia
'C:\CONCEPT\[RUPM-Estrazione scanacc.xlsx]FOGLIO1'!A1
'C:\CONCEPT\[RUPM-Estrazione scanacc.xlsx]FOGLIO1'!A2
'C:\CONCEPT\[RUPM-Estrazione scanacc.xlsx]FOGLIO1'!A3

poi ho aggiunto questa macro che aggiunge il segno = e ho ottenuto nella colonna "I" i vari valori ricercati

Codice: Seleziona tutto

Sheets("estrai spvm").Select
    For i = 1 To 600
    Cells(i, "G").Select
    Selection.Copy
    Cells(i, "H").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Cells(i, "I").FormulaLocal = "=" & Cells(i, "H")
    Next i
End If

Il punto è che a mano a mano che facevo questo lavoro, mi sono accorto che il tempo di elaborazione diventava sempre più lungo e questo ovviamente ostacola un po' tutto il lavoro svolto.
Avete dei suggerimenti?

[scanacc]

Ho provato a cabiare così e sembra un pochino meglio (1minuto e 10 secondi di attesa)

Codice: Seleziona tutto

   Sub NUOVO_Estrai_spvm()   
   Sheets("estrai spvm").Select
    Range("G1:G571").Select
    Selection.Copy
    Range("H1:H571").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    For i = 1 To 571
    Cells(i, "I").FormulaLocal = "=" & Cells(i, "H")
    Next i
End
End Sub


Ma mi sembra impossibile che non ci sia una procedura più veloce

[scanacc]

.

[scanacc]

Purtroppo da un ulteriore controllo le modifiche da me apportate non diminuiscono i tempi di calcolo.
Ogni aiuto è ben accetto

[Marius44]

Ciao
Un accorciamento dei tempi puoi ottenerlo eliminando tutti quei .Select

Codice: Seleziona tutto

   Sub NUOVO_Estrai_spvm()   
   Sheets("estrai spvm").Select
    Range("G1:G571").Copy
    Range("H1:H571").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    For i = 1 To 571
    Cells(i, "I").FormulaLocal = "=" & Cells(i, "H")
    Next i
End
End Sub


Ma credo sia ben poca cosa.
Ciao,
Mario

[scanacc]

Grazie! Ho provato, il risultato è molto modesto ma per cominciare è già buono

[scanacc]

Ho anche aggiunto all'inizio

Codice: Seleziona tutto

    Application.ScreenUpdating = False
    Application.Calculation = xlManual

e alla fine

Codice: Seleziona tutto

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

e il tempo è sceso a circa 1 minuto

Ma secondo voi questo comando

Codice: Seleziona tutto

    For i = 1 To 600
    Cells(i, "G").Copy
    Cells(i, "H").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

    Cells(i, "I").FormulaLocal = "=" & Cells(i, "H")
    Next i
End If


non ha alternative migliori?

[Anthony47]

Non l'hai detto esplicitamente, ma quel "trucco" serve per lavorare su file chiusi.
Ora secondo te excel e' in grado di estrarre un dato da un file chiuso? Ovvio che No.
Quindi Excel ogni qualvolta che trova quei collegamenti apre il file, estrae il dato richiesto, richiude il file; 10 formule, 10 Apri /Estrai /Chiudi; 100 formule, 100 Apri /Estrai /Chiudi
Il risultato e' quello che vedi

Probabilmente potresti avere qualche miglioramendo mettendo la directory CONCEPT e i suoi file su un disco a stato solido; ma un miglioramento radicale lo puoi solo avere cambiando approccio.

Ad esempio, se devi prelevare da un singolo file (quello che specifichi in V1) un tot di celle allora potresti lavorare con una macro di Worksheet_Change, che quando modifichi V1 allora Apre quel file, copia dal file cosi' aperto le celle che ti interessano (non so se sono una qui e l'altra là, oppure sono dei blocchi di tabelle) e richiude il file; in qualche secondo te la cavi.
Se vuoi un po' piu' di flessibilita', Apri il file, Copi il contenuto di uno o piu' fogli in aree predisposte del tuo file, Chiudi il file; poi fai in modo che le tue formule "peschino" i dati da questa aree predisposte. Per me questo e' il metodo preferibile

Ciao

[scanacc]

Farò senz'altro così.
Ma come si fa a operare con con una macro di Worksheet_Change?
In pratica come si apre un foglio?
Io generalmente li apro così

Codice: Seleziona tutto

    Workbooks.Open Filename:= _
        "C:\CONCEPT\[RUPM-Estrazione NOME1.xlsx]FOGLIO1'!A1", Notify:= _
        False
    Application.WindowState = xlNormal

ma le domande sono:
1) E' giusto questo metodo?
2) E con nome del file che cambia sempre come la la mettiamo?
PS
Io nella cella "V1" metto solo il nome del file non tutto l'indirizzo completo. l'indirizzo lo prendo con quella macro di cui sopra

[scanacc]

Sono arrivato qui e mi sembra già un successone!

Codice: Seleziona tutto

Sub Apro_Stringa_Pasquinelli_e_Copio()
'
' Macro1 Macro
'
    Dim rng As Range, bk As Workbook

    ' Apro il foglio il cui nome è indicato nella cella A1
    Set rng = Worksheets("STRINGA PASQUINELLI").Cells(1, 1)
    If (Trim(rng.Value) <> "") Then
        Set bk = Workbooks.Open("C:\CONCEPT\" & rng.Value)
    End If
    Range("A2:BA50").Select
    Selection.Copy
    Windows("Concept 5.xlsm").Activate
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A1").Select
End Sub

ma ora come lo chiudo il file il cui nome è nella cella A1?????

[Anthony47]

Quasi quasi sto ancora zitto per vedere dove arrivi da solo: perche' quel codice sara' pure inefficiente e in alcune parti inutili, ma certamente efficace...

L'unico commento importante che devo fare e' sulla struttura dell' If /End If; perche' e' evidente che tutta la seconda parte del codice va dentro l'If.
Quindi sposta End If a subito prima di End Sub, in modo che tutta l'operazione di Copia e successiva IncollaSpeciale avvenga solo se il nuovo file e' stato aperto.
Quanto a chiudere il file che hai aperto, beh ti bastera' aggiungere appena prima di End If l'istruzione

Codice: Seleziona tutto

bk.Close SaveChanges:=False


In una prossima puntata come poter copiare e incollare senza selezionare fisicamente il workbook, il foglio, la cella.

Ciao

[scanacc]

ahahahahahaha mi fai impazzire Anthony.
Grazie per aver detto che è efficace ... almeno quello
Non vedo l'ora che venga la prossima puntata "come poter copiare e incollare senza selezionare fisicamente il workbook, il foglio, la cella."
Intanto ho fatto quanto mi suggerivi.
Prima di chiudere completamente il file mi chiede se voglio conservare gli appunti e dato che non mi interessa conservarli, ho inserito il comando

Codice: Seleziona tutto

Application.DisplayAlerts = False

prima di

Codice: Seleziona tutto

bk.Close SaveChanges:=False

Mi sembra giusto.
Che ne dici?
PS
Grazie di esistere

[Ricky0185]

In una prossima puntata come poter copiare e incollare senza selezionare fisicamente il workbook, il foglio, la cella.

A me questo metodo funziona alla grande con il link in prima riga.
Sono però sicuro che Anthony sa qualcosa di meglio.

[Anthony47]

Quello che dice Ennius e' sempre cosa buona e giusta...

Quanto alla riga Application.DisplayAlerts = False direi che e' il comando giusto per evitare il messaggio.

Ciao a tutti!