errore Run-time '9': Indice non incluso nell'intervallo

[mario...@gmail.com]

Buongiorno,
ho trovato in rete questa macro che permette di esportare tutti i fogli della cartella di lavoro raggruppandoli in un unico pdf.
=====================>
'per stampare tutti i fogli su un unico pdf
Sub pdf_unico()
Dim foglio As Variant

foglio = Array()

For i = 1 To Sheets.Count
ReDim Preserve foglio(i - 1)
foglio(i - 1) = Sheets(i).Name
Next i

nomecartella = ActiveWorkbook.Name
Percorso = "C:UsersDesktopstampe"
Sheets(foglio).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=Percorso & nomecartella & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True
End Sub
<===================

La macro funziona egregiamente.
Ho provato ad adattarla alle mie necessità ma non riuscendo nell’intento, sono a chiedere un vostro aiuto.
(Microsoft Office 2010 Plus)

Questo è il mio listato con commenti e indicazioni:

=====================>
'esportare più fogli in un unico file pdf
Sub pdf_unico()
Dim n_unità As Integer, i As Integer, FogliIniziali As Integer
Dim Ripartizione As Worksheet, Report As Worksheet
Dim Cartella As String, Percorso As String
Dim foglio As Variant

Set Ripartizione = ThisWorkbook.Worksheets("Ripartizione")
Set Report = ThisWorkbook.Worksheets("Report Individuale")

n_unità = Ripartizione.Range("X3")
FogliIniziali = Sheets.Count '= n. di fogli presenti all'avvio
foglio = Array()
Cartella = ActiveWorkbook.Name
Percorso = "C:\Users\Mario\Desktop\"

'qui crea tutti i fogli da esportare in PDF
For i = 1 To n_unità
Report.Range("E2") = Ripartizione.Cells(20 + i, 3)
Report.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Nominativo n." & i
Next i

'qui carica l'Array con i soli fogli appena creati
For i = FogliIniziali + 1 To Sheets.Count
ReDim Preserve foglio(i - 1)
foglio(i - 1) = Sheets(i).Name
Next i

'========= e qui mi da errore di run-time '9':============
'========= Indice non incluso nell'intervallo ============
Sheets(foglio).Select '<==========================

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=Percorso & Cartella & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

Application.DisplayAlerts = False
For i = Sheets.Count To FogliIniziali + 1 Step -1
Sheets(i).Delete
Next i
Application.DisplayAlerts = True
Set Report = Nothing
Set Ripartizione = Nothing
End Sub
<===================

Dove sbaglio?
Come posso risolvere?
Grazie per ogni aiuto possibile,
Mario

[casanmaner]

A mio parere è un problema di dimensionamento della matrice e di ciclo assegnazione valori.
Prova così:

'esportare più fogli in un unico file pdf
Sub pdf_unico()
Dim n_unità As Integer, i As Integer, FogliIniziali As Integer
Dim Ripartizione As Worksheet, Report As Worksheet
Dim Cartella As String, Percorso As String

Dim foglio() As Variant

Set Ripartizione = ThisWorkbook.Worksheets("Ripartizione")
Set Report = ThisWorkbook.Worksheets("Report Individuale")

n_unità = Ripartizione.Range("X3")
FogliIniziali = Sheets.Count '= n. di fogli presenti all'avvio

Cartella = ActiveWorkbook.Name
Percorso = "C:\Users\Mario\Desktop\"

'qui crea tutti i fogli da esportare in PDF
For i = 1 To n_unità
Report.Range("E2") = Ripartizione.Cells(20 + i, 3)
Report.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Nominativo n." & i
Next i

'qui carica l'Array con i soli fogli appena creati
For i = FogliIniziali + 1 To Sheets.Count

ReDim Preserve foglio(1 To i - FogliIniziali)
foglio(i - FogliIniziali) = Sheets(i).Name
Next i

[mario...@gmail.com]

Il giorno mercoledì 13 settembre 2017 15:25:42 UTC+2, casanmaner ha scritto:
> A mio parere è un problema di dimensionamento della matrice e di ciclo assegnazione valori.
> Prova così:

> .....
>
Purtroppo nulla di fatto.
Con la modifica che mi hai suggerito, lo stesso errore si sposta 3 righe più in su, ovvero:

ReDim Preserve foglio(1 To i - FogliIniziali)

> 'qui carica l'Array con i soli fogli appena creati
> For i = FogliIniziali + 1 To Sheets.Count
> ReDim Preserve foglio(1 To i - FogliIniziali)
> foglio(i - FogliIniziali) = Sheets(i).Name
> Next i
>

Grazie comunque per il tuo intervento,
Mario

[casanmaner]

Per curiosità prova questo file
https://www.dropbox.com/s/gnbm5qbi8bdzag5/Cartel1.xlsm?dl=0

[casanmaner]

Il giorno mercoledì 13 settembre 2017 16:50:47 UTC+2, casanmaner ha scritto:
> Per curiosità prova questo file
> https://www.dropbox.com/s/gnbm5qbi8bdzag5/Cartel1.xlsm?dl=0

Ah ... ovviamente modifica il percorso dove salvare il pdf :-)

[mario...@gmail.com]

E' semplicemente FANTASTICO!!!!!
Grazie.

L'ho provato su un file con 128 record dei quali, per ognuno di loro, creo una reportistica individuale.

L'unica pecca è il tempo di esecuzione (più di due minuti e mezzo) dovuto al tempo di creazione dei 128 fogli (che poi comunque vanno eliminati).

Tu pensi si possa ovviare alla creazione dei fogli per poi generare il PDF unico ciclando un solo foglio dinamico, senza doverlo aggiungere nella cartella, e inviando poi tutti i fogli ciclati ad un unico file PDF?

Se si potesse, mi dai qualche indicazione?
Di nuovo grazie per l'enorme aiuto che mi hai dato,
Mario

[casanmaner]

Premesso che provando il file che avevo "allegato" senza alcuna modifica il tempo per copiare 128 fogli, il relativo PDF, e l'eliminazione con il ciclo (che può essere evitata) con il mio pc ci impiega 10/11 secondi, non credo che sia possibile avere un unico PDF di più "pagine" senza creare tanti fogli poi da selezionare per effettuare la stampa dell'unico PDF (ma magari mi sbaglio).

Forse la tua cartella di lavoro ha molte formule che vengono aggiornate e questo potrebbe allungare i tempi.
Magari vengono anche scatenati degli eventi.
Si può ovviare disabilitando il calcolo automatico e andando ad effetturare il calcolo per il singolo foglio (così man mano che i fogli si creano le formule del solo nuovo foglio vengono ricalcolate).
Si possono disabilitare gli eventi se non è necessario che vengano lanciati in fase di copia dei fogli e riporto dei dati negli stessi.
Inoltre si può "congelare" lo screenupdating.
Anche la cancellazione dei fogli può avvenire non con un ciclo ma con un unico comando, in quanto i fogli da cancellare risultano già individuati dall'array.
Facendo queste modifiche sul mio file ho risparmiato circa 3".
Ma magari con il tuo il risparmio potrebbe essere maggiore.
Prova a vedere questo file:
https://www.dropbox.com/s/z381q1eevb0cj14/errore%20Run-time%20%279%27%20Indice%20non%20incluso%20nell%27intervallo.xlsm?dl=0

dove nel Modulo1 ho così modificato il codice precedente:
'---
Option Explicit

'esportare più fogli in un unico file pdf
Sub pdf_unico()

Dim sTimer: sTimer = Timer

Dim n_unità As Integer, i As Integer, FogliIniziali As Integer
Dim Ripartizione As Worksheet, Report As Worksheet
Dim Cartella As String, Percorso As String
Dim foglio() As Variant

With ThisWorkbook
Set Ripartizione = .Worksheets("Ripartizione")
Set Report = .Worksheets("Report Individuale")
End With

n_unità = Ripartizione.Range("X3")
FogliIniziali = Sheets.Count '= n. di fogli presenti all'avvio
Cartella = ActiveWorkbook.Name

Percorso = "C:\Test\" '<=== MODIFICARE!!!

With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
.DisplayAlerts = False
End With

On Error GoTo Esci

'qui crea tutti i fogli da esportare in PDF
For i = 1 To n_unità
Report.Range("E2") = Ripartizione.Cells(20 + i, 3)
Report.Copy After:=Sheets(Sheets.Count)

With ActiveSheet

.Name = "Nominativo n." & i

.Calculate
End With
Next i

'qui carica l'Array con i soli fogli appena creati
For i = FogliIniziali + 1 To Sheets.Count
ReDim Preserve foglio(1 To i - FogliIniziali)
foglio(i - FogliIniziali) = Sheets(i).Name
Next i

Sheets(foglio).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=Percorso & Cartella & ".pdf", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=True

Sheets(foglio).Delete 'i fogli in precedenza individuati per la stampa Pdf vengono ora cancellati

Ripartizione.Activate

RiprendiErrore:
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
.DisplayAlerts = True
End With

Set Report = Nothing
Set Ripartizione = Nothing

MsgBox "Tempo esecuzione: " & Timer - sTimer
Exit Sub
Esci:
MsgBox "Si è verificato un errore!" & vbCrLf & _
"Errore n. " & Err.Number & vbCrLf & _
Err.Description, vbCritical, "Errore"
GoTo RiprendiErrore
End Sub
'---

[mario...@gmail.com]

Il giorno mercoledì 13 settembre 2017 20:33:43 UTC+2, casanmaner ha scritto:
> Premesso che provando il file che avevo "allegato" senza alcuna modifica il tempo per copiare 128 fogli, il relativo PDF, e l'eliminazione con il ciclo (che può essere evitata) con il mio pc ci impiega 10/11 secondi, non credo che sia possibile avere un unico PDF di più "pagine" senza creare tanti fogli poi da selezionare per effettuare la stampa dell'unico PDF (ma magari mi sbaglio).

il tuo ultimo file (che ovviamente è privo di dati) sul mio PC impiega meno di 10 secondi;

>
> Forse la tua cartella di lavoro ha molte formule che vengono aggiornate e questo potrebbe allungare i tempi.

si, due degli otto fogli sono zeppi di formule ma non credo vadano in aggiornamento in quanto il foglio di report (mediante un elenco convalida dati) va a cercare i dati di interesse tramite dei CERCA.VERT;

> Magari vengono anche scatenati degli eventi.

è presente solo una macro su evento Worksheet_Change, ma anche quella non entra in gioco;

> Si può ovviare disabilitando il calcolo automatico e andando ad effetturare il calcolo per il singolo foglio (così man mano che i fogli si creano le formule del solo nuovo foglio vengono ricalcolate).
> Si possono disabilitare gli eventi se non è necessario che vengano lanciati in fase di copia dei fogli e riporto dei dati negli stessi.
> Inoltre si può "congelare" lo screenupdating.
> Anche la cancellazione dei fogli può avvenire non con un ciclo ma con un unico comando, in quanto i fogli da cancellare risultano già individuati dall'array.
> Facendo queste modifiche sul mio file ho risparmiato circa 3".
> Ma magari con il tuo il risparmio potrebbe essere maggiore.

si, ho sostituito il vecchio listato con il tuo nuovo ed i tempi si sono accorciati in maniera poco apprezzabile (203 sec al timer che hai inserito)

Visto che l'operazione non è frequente ma può avere cadenza trimestrale/semestrale/annuale, ritengo sia più che accettabile e rispettabile il tempo di attesa, non trovi?

Se poi, viste le tue doti con il VBA, trovi una soluzione più dinamica e più veloce, io sarei ben lieto di testarla.

Un "grazie" con 1.000.000 di "e".

Pizza e birra pagata (Roma - Cinecittà);-)
Ciao,
Mario

[mario...@gmail.com]

Oh questa è bella!!

Solo per tua info,
ha funzionato per diverse volte ma ora mi segnala:
"Errore di compilazione: Variabile non definita"
(evidenziando in blu sTimer).

Ma Santa Pasqua, non è definita ora come non lo era prima!!

E ALLORA PRIMA PERCHE' FUNZIONAVI???

Uhfff....

... anche se da qualche tempo ho il sospetto di avere qualche problemino con Excel; a volte capita che non risponde ma poi riprende.

Prima o poi il timer lo toglierò, intanto, dopo aver provato vari tipi di variabili, ora l'ho dichiarata come Variant, anche se purtroppo il dato che restituisce non so proprio cosa sia: 3472,199 (anno,ora?).... mah

[casanmaner]

Ma io l'avevo definita anche se, per "fretta" senza specificare il tipo (e quindi automaticamente variant).
Nella mia procedura era indicato come prima riga di comando:

Dim sTimer: sTimer = Timer

Dove Dim sTimer è la definizione della variabile e dopo i due punti l'assegnazione del "Timer" che rappresenta il numero di secondi trascorsi dalla mezzanotte.
Poi nel msgbox viene fatta la differezza tra il Timer iniziale e il Timer finale dando il numero di secondi trascorsi tra inizio e fine.

[mario...@gmail.com]

> .....

>
> Ma io l'avevo definita anche se, per "fretta" senza specificare il tipo (e quindi automaticamente variant).
> Nella mia procedura era indicato come prima riga di comando:
> Dim sTimer: sTimer = Timer
>

> .....

Si è vero, scusami.

Devo inavvertitamente aver combinato qualcosa.
(Chiedo scusa anche ad Excel :-) )
Ciao,
Mario

[mario...@gmail.com]

Ahi, ahi...

Buongiorno,
c'è modo di "allungare" la profondità del percorso di salvataggio di un file?

Ovvero, quante nidificazioni max di cartelle è possibile raggiungere?

Mario