Forum di Excel e VBA, per condividere esperienze e risolvere problemi su:
Excel, Word, Access, Power Point, Microsoft Office, macro, Visual Basic e Visual Studio.
Autocompilazione di un file
- Hai un problema con Excel?
Autocompilazione di un file
di Mik data: 02/06/2017 17:30:46
Ciao Ragazzi,
finalmente mi sono iscritto!!!!
Comunque volevo chiedervi un altro aiuto... ho un progetto in mente ma dato che sono ancora alle prime armi con i codici VBA vorrei capire da voi se è possibile o meno. Parto dal principio:
io un file, che chiamiamo "Prova per client", che un operatore mi deve compilare per ogni nuovo cliente con alcuni dati che reperisce da un altro file excel, ce chiamiamo "elenco clienti". La mia macro autonomamente salva il file "prova per client" in un cartella specifica, lo rinomina, mi crea una mail e la manda al destinatario.
Ora quello che vorrei fare è eliminare totalemente il lavoro dell'operatore, creando una macro che mi permetta di auto compilare il file "elenco clienti".
Il file "elenco clienti" ha n clienti elencati e vorrei creare un codice che per ogni riga/cliente mi compili il file "prova per client", cosi dopo di che posso collegarli la macro per di cui parlavo prima.
Il problema è che non so proprio come partire con la macro.
in ogni caso vi allego qui i due file di cui vi parlavo
di patel data: 02/06/2017 17:40:44
con ambedue i file aperti attiva il registratore di macro, e copia i valori dall'uno all'altro, alla fine troverai già pronta una macro da modificare per automatizzarla, ma avrai una buona base di partenza
di Mik (utente non iscritto) data: 06/06/2017 17:11:34
Grazie mille Patel!!! In effetti a volte basta pensare a partire dalla parte più semplice.
Detto questo sono arrivato a buon punto nel mio codice, ho un piccolo intoppo ora perchè come vedete dal codice che vi allego sotto ho inserito un For/Next anche se non lo so usare ancora bene...
Essenzialmente quando faccio partire la macro funziona tutto perfettamente, il problema nasce quando deve passare alla "x" successiva mi esce il messaggio: "Errore di run-time 9: Indice non incluso nell'intervallo".
Sapete per caso dove sto sbagliando?
Sub InvioAutomatico() Dim x As Integer For x = 2 To 10 Cella = Worksheets("Elenco clienti").Cells(x, 1) If Not IsEmpty(Cella) Then Sheets("Elenco clienti").Select ' cdc Cells(x, 1).Select Selection.Copy Sheets("Client Acceptance").Select Range("H2").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' Codclie Cells(x, 2).Select Selection.Copy Sheets("Client Acceptance").Select Range("C4").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' Ragione sociale Cells(x, 3).Select Selection.Copy Sheets("Client Acceptance").Select Range("C2").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' P.Iva Cells(x, 4).Select Selection.Copy Sheets("Client Acceptance").Select Range("C6").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' Indirizzo Cells(x, 5).Select Selection.Copy Sheets("Client Acceptance").Select Range("C8").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' Telefono Cells(x, 6).Select Selection.Copy Sheets("Client Acceptance").Select Range("C10").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' E-mail Cells(x, 7).Select Selection.Copy Sheets("Client Acceptance").Select Range("C12").Select ActiveSheet.Paste PercorsoGenerico = "C:UsersDesktopprova" 'percorso completo su cui salvare NomeCdc = Range("H2").Value PercorsoSpecifico = PercorsoGenerico & NomeCdc & "" NomeFile = Range("c4").Value 'cella da cui prendere il nome file NomeFoglio = "Client Acceptance" 'nome esatto del foglio da copiare If NomeFile = "" Then Exit Sub If Right(NomeFile, 4) <> ".xls" Then NomeFile = NomeFile & ".xls" Sheets(NomeFoglio).Copy ActiveWorkbook.SaveAs Filename:=PercorsoSpecifico & NomeFile, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False End If Next x End Sub
di patel data: 06/06/2017 17:51:21
hai allegato una macro che lavora con due fogli dello stesso workbook (ed hai fatto bene), nel primo post invece hai parlato di 2 file diversi, a questo punto allega il file con la macro registrata
di Mik data: 07/06/2017 09:21:03
Si perdonami non ci ho pensato, in effetti nel mezzo della creazione del codice mi sono reso conto che era molto più comodo aggiungere semplicemente un foglio e fare tutto in un singolo file invece che dover mettere in relazione 2 file diversi.
Detto ciò a questo punto ti allego un file che ripropone il mio un pò semplificato, in ogni caso ti ho copiato nuovamente il codice leggermente "aggiustato" per il file che ho ricreato.
Grazie in anticipo
Sub InvioAutomatico() Dim x As Integer For x = 2 To 1000 Cella = Worksheets("Elenco clienti").Cells(x, 1) If Not IsEmpty(Cella) Then Sheets("Elenco clienti").Select ' cdc Cells(x, 1).Select Selection.Copy Sheets("Client Acceptance").Select Range("F2").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' Codclie Cells(x, 2).Select Selection.Copy Sheets("Client Acceptance").Select Range("B2").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' Ragione sociale Cells(x, 3).Select Selection.Copy Sheets("Client Acceptance").Select Range("B1").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' P.Iva Cells(x, 4).Select Selection.Copy Sheets("Client Acceptance").Select Range("B3").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' Indirizzo Cells(x, 5).Select Selection.Copy Sheets("Client Acceptance").Select Range("B4").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' Telefono Cells(x, 6).Select Selection.Copy Sheets("Client Acceptance").Select Range("B5").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' E-mail Cells(x, 7).Select Selection.Copy Sheets("Client Acceptance").Select Range("B6").Select ActiveSheet.Paste PercorsoGenerico = "C:UsersDesktopprova" 'percorso completo su cui salvare NomeCdc = Range("F1").Value PercorsoSpecifico = PercorsoGenerico & NomeCdc & "" NomeFile = Range("B2").Value 'cella da cui prendere il nome file NomeFoglio = "Client Acceptance" 'nome esatto del foglio da copiare If NomeFile = "" Then Exit Sub If Right(NomeFile, 4) <> ".xls" Then NomeFile = NomeFile & ".xls" Sheets(NomeFoglio).Copy ActiveWorkbook.SaveAs Filename:=PercorsoSpecifico & NomeFile, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Dim BodyMsg As String, Oggetto As String, PercorsoFile As String BodyMsg = "Gentili colleghi," _ & vbCrLf & "vi chiedo gentilmente l'invio della client, da compilare nei primi 9 punti." _ & vbCrLf & "Vi informo che il processo di invio ed archiviazione è stato automatizzato e vi chiedo quindi di NON rinominare il file e di reinoltrare la client all'indirizzo mail lince@lavoropiu.it." _ & vbCrLf & "Grazie e buon proseguimento." _ Oggetto = "Richiesta compilazione Client" Indirizzo = Worksheets("cdc mail").Cells(58, 2) IndirizzoCC = Worksheets("cdc mail").Cells(58, 4) PercorsoFile = Worksheets("cdc mail").Cells(59, 2) If Indirizzo <> "" Then Shell "C:Program Files (x86)Mozilla Thunderbird hunderbird -compose " _ & Chr$(34) & "to='" & Indirizzo & "',cc='" & IndirizzoCC & "',subject='" & Oggetto & "',body='" & BodyMsg _ & "',attachment='" & PercorsoFile & "'" & Chr$(34), vbNormalFocus Application.Wait Now + TimeValue("00:00:03") SendKeys "^{ENTER}" End If End If Next End Sub
di patel data: 07/06/2017 15:35:38
hai allegato un file dove non ci sono i fogli Elenco clienti e Client Acceptance, mi sembra che tu non abbia molta voglia di collaborare
di Mik data: 07/06/2017 15:42:30
patel perdonami l'ho creato stamattina al volo e mi è proprio sfuggito di rinominare i foglio... ora l'ho riallegato dovrebbe essere tutto ok adesso
di patel data: 07/06/2017 18:06:53
ci sono 2 errori
1) NomeCdc = Range("F1").Value
la cella F1 è vuota, forse volevi scrivere F2
2) Indirizzo = Worksheets("cdc mail").Cells(58, 2)
il documento attivo è quello che vuoi salvare e lì non c'è il foglio "cdc mail", quindi ti conviene leggere queste variabili prima di generare il documento da salvare.
Inoltre ti consiglio di evitare le celle unite B C D, con le macro spesso fanno scherzi
di Mik data: 08/06/2017 09:15:06
Allora:
- per il punto 1, non è un errore con l'istruzione " NomeCdc = Range("F1").Value " gli sto chiedendo di prendere la cella F1 del foglio Client acceptance dove una volta compilato il file troverò il Cdc.
A proposito di questo la macro funziona, però non capisco come faccia a capire da che foglio prendermi la cella dato che non glielo esplicito, quindi mi sono andato far bene il fatto che funzioni però mi piacerebbe capire se va bene così o è semplicemente un caso.
- Per quanto riguarda il punto 2 in effetti anche io immaginavo si fermasse per quel motivo quindi ho provato a fargli "interrogare" il file originale con un istruzione tipo ".select o .activate; però credo di sbagliare l'istruzione di partenza. A questo punto ti chiedo un aiuto anche su questo punto.
- Comunque ho notato che l'unione delle celle crea dei "brutti scherzi" però purtroppo sto scrivendo la macro su un file che mi è stato consegnato preimpostato e che non posso cambiare quindi sto sperando di arginare questi problemi
di patel data: 08/06/2017 11:41:22
come ti ho già suggerito ti conviene leggere le variabili prima di generare il documento da salvare, quindi
...... Sheets("Elenco clienti").Select ' E-mail Cells(x, 7).Select Selection.Copy Sheets("Client Acceptance").Select Range("B6").Select ActiveSheet.Paste Indirizzo = Worksheets("cdc mail").Cells(58, 2) '<<<<<<<<< spostate qui IndirizzoCC = Worksheets("cdc mail").Cells(58, 4) PercorsoFile = Worksheets("cdc mail").Cells(59, 2) PercorsoGenerico = "C:UsersDesktopprova" 'percorso completo su cui salvare NomeCdc = Range("F1").Value PercorsoSpecifico = PercorsoGenerico & NomeCdc & "" NomeFile = Range("B2").Value 'cella da cui prendere il nome file NomeFoglio = "Client Acceptance" 'nome esatto del foglio da copiare If NomeFile = "" Then Exit Sub If Right(NomeFile, 4) <> ".xls" Then NomeFile = NomeFile & ".xls" Sheets(NomeFoglio).Copy .......
di Mik data: 08/06/2017 13:01:28
Grande Patel!!! sei il mio eroe, grazie a questo forum piano piano sto imparando qualcosa e di questo vi ringrazio.
Detto questo il problema successivo mi nasce con la condizione For/Next ho delle difficoltà proprio a capire come funziona.
Dimmi se sbaglio: in teoria devo inserire dopo la dicitura "For" il mio indice dal quale partire (nel mio caso il 2) dopo di che le devo aggiungere il "To" che mi indica la fine del ciclio (in questo caso 1000) ed ad ogni istruzione Next x dovrebbe passarmi all'indice successivo (quindi 3 poi 4 poi 5 ecc), dopo di che mi va ad inserire quel valore in ogni punto dove trova la mia variabile ("x")...Corretto??
Se e dico se il ragionamente è corretto a questo punto non capisco perchè la macro non funziona
Io ho provato anche a cambiare il codice con il quale si identifica la x cambiando da "Dim x as Integer" a "Dim x as Variant" ma non cambia nulla quindi secondo me forse il problema è che quando riparte dall'inizio la macro con il nuovo indice excel si basa non sul file originale ma sul nuovo file appena creato e li non ritrova più i vari collegamenti. Possibile?
Nel caso in cui io abbia ragione come devo fare per fargli capire da quale file prendere i dati? Io ho provato con una cosa come "Workbooks("TEST.xls").Select", ma il risultato è stato nullo... Riesci nuovamente ad aiutarmi per favore?
di Vecchio Frac data: 08/06/2017 13:30:54
Un ciclo For...Next è concettualmente facile, ti riporto la spiegazione che ho già scritto altrove:
"Serve a costruire un ciclo all'interno di un insieme, cioè a ripetere una determinata serie di istruzioni per un certo numero di volte. E' costituito da una variabile di controllo, detta contatore, incrementata dal compilatore ad ogni passaggio. Il passaggio è segnato dall'istruzione che dice di proseguire: Next. La parola chiave To serve a definire il range di valori in cui ciclare.
Quindi si usa così:
For i = 1 to 10 'ripeti per dieci volte quello che c'è tra For e Next
... istruzioni ...
Next "
Scrivi correttamente:
"Dimmi se sbaglio: in teoria devo inserire dopo la dicitura "For" [For è una parola chiave, non una dicitura] il mio indice dal quale partire (nel mio caso il 2) dopo di che le devo aggiungere il "To" che mi indica la fine del ciclio (in questo caso 1000) ed ad ogni istruzione Next x dovrebbe passarmi all'indice successivo (quindi 3 poi 4 poi 5 ecc), dopo di che mi va ad inserire quel valore in ogni punto dove trova la mia variabile ("x")...Corretto?? "
Sembra tutto corretto ma se dici "non capisco perchè la macro non funziona" dovresti essere più chiaro nello spiegare cosa c'è che non funziona (non ho seguito tutto dall'inizio, può darsi che tu l'abbia già spiegato).
Cambiare il tipo di dato da integer a variant comporta solo lo sforzo del compilatore di tipizzare la variabile a seconda del valore che riceve durante l'elaborazione (in pratica c'è uno spreco di memoria occupato inutilmente).
Infine nel codice ci sono alcune cose da sistemare (le variabili è meglio DIMensionarle tutte a inizio codice della subroutine, manca Option Explicit, vedo che sono possibili alcune ottimizzazioni).
di patel data: 08/06/2017 17:52:04
Ogni volta che crei un nuovo docunento e lo salvi per farne un allegato lo devi chiudere per ritornare al documento in cui lavora la macro quindi
Sheets(NomeFoglio).Copy ActiveWorkbook.SaveAs Filename:=PercorsoSpecifico & NomeFile, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False Sheets(NomeFoglio).Copy ActiveWorkbook.Close False ' <<<<<<<<<<<<<<<<
di Mik data: 09/06/2017 10:13:40
Grazie mille Vecchio Frac per le varie spiegazioni appena ho un pò di tempo vedo di rivedere il codice nella sua interezza per "aggiustare" quei piccoli problemi che mi dici esserci.
GRAZIE MILLE patel ora la macro funziona perfettamente e finalmente riesco a svolgere il lavoro con più fluidità.
allego la macro completa a titolo didattico.
Sub InvioAutomatico() Dim x As Integer For x = 2 To 1000 Cella = Worksheets("Elenco clienti").Cells(x, 1) If Not IsEmpty(Cella) Then Sheets("Elenco clienti").Select ' cdc Cells(x, 1).Select Selection.Copy Sheets("Client Acceptance").Select Range("H2").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' Codclie Cells(x, 2).Select Selection.Copy Sheets("Client Acceptance").Select Range("C4").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' Ragione sociale Cells(x, 3).Select Selection.Copy Sheets("Client Acceptance").Select Range("C2").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' P.Iva Cells(x, 4).Select Selection.Copy Sheets("Client Acceptance").Select Range("C6").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' Indirizzo Cells(x, 5).Select Selection.Copy Sheets("Client Acceptance").Select Range("C8").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' Telefono Cells(x, 6).Select Selection.Copy Sheets("Client Acceptance").Select Range("C10").Select ActiveSheet.Paste Sheets("Elenco clienti").Select ' E-mail Cells(x, 7).Select Selection.Copy Sheets("Client Acceptance").Select Range("C12").Select ActiveSheet.Paste Dim PercorsoFile As String Indirizzo = Worksheets("cdc mail").Cells(58, 2) IndirizzoCC = Worksheets("cdc mail").Cells(58, 4) PercorsoFile = Worksheets("cdc mail").Cells(59, 2) PercorsoGenerico = "C:UserslavoropiuDesktopClientAcceptance" 'percorso completo su cui salvare NomeCdc = Range("H2").Value PercorsoSpecifico = PercorsoGenerico & NomeCdc & "" NomeFile = Range("c4").Value 'cella da cui prendere il nome file NomeFoglio = "Client Acceptance" 'nome esatto del foglio da copiare If NomeFile = "" Then Exit Sub If Right(NomeFile, 4) <> ".xls" Then NomeFile = NomeFile & ".xls" Sheets(NomeFoglio).Copy ActiveWorkbook.SaveAs Filename:=PercorsoSpecifico & NomeFile, FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False ActiveWorkbook.Close False Dim BodyMsg As String, Oggetto As String BodyMsg = "Gentili colleghi," _ & vbCrLf & "vi chiedo gentilmente l'invio della client, da compilare nei primi 9 punti." _ & vbCrLf & "Vi informo che il processo di invio ed archiviazione è stato automatizzato e vi chiedo quindi di NON rinominare il file e di reinoltrare la client all'indirizzo mail lince@lavoropiu.it." _ & vbCrLf & "Grazie e buon proseguimento." _ Oggetto = "Richiesta compilazione Client" If Indirizzo <> "" Then Shell "C:Program Files (x86)Mozilla Thunderbird hunderbird -compose " _ & Chr$(34) & "to='" & Indirizzo & "',cc='" & IndirizzoCC & "',subject='" & Oggetto & "',body='" & BodyMsg _ & "',attachment='" & PercorsoFile & "'" & Chr$(34), vbNormalFocus Application.Wait Now + TimeValue("00:00:03") SendKeys "^{ENTER}" End If End If Next End Sub
di patel data: 09/06/2017 16:22:23
oltre a quanto detto da VF ci sono molte cose da mettere a posto, per es eliminare tutti quei select che ti fanno saltare da un foglio all'altro in questo modo:
Sub Invio_Automatico() Dim x As Integer LR = Worksheets("Elenco clienti").Cells(Rows.Count, "A").End(xlUp).Row With Sheets("Client Acceptance") For x = 2 To LR .Range("F2") = Sheets("Elenco clienti").Cells(x, 1) .Range("B2") = Sheets("Elenco clienti").Cells(x, 2) .Range("B1") = Sheets("Elenco clienti").Cells(x, 3) .Range("B3") = Sheets("Elenco clienti").Cells(x, 4) .Range("B4") = Sheets("Elenco clienti").Cells(x, 5) .Range("B5") = Sheets("Elenco clienti").Cells(x, 6) .Range("B6") = Sheets("Elenco clienti").Cells(x, 7) PercorsoGenerico = "f:DOWNLOAD" 'percorso completo su cui salvare Indirizzo = Worksheets("cdc mail").Cells(58, 2) IndirizzoCC = Worksheets("cdc mail").Cells(58, 4) PercorsoFile = Worksheets("cdc mail").Cells(59, 2) ' ..........
di Mik data: 12/06/2017 17:42:09
grazie patel, sto continuando a lavorare sulla macro per cercare di ottimizzarla il più possibile ed eliminare quelle imperfezioni che mi state facendo notare mano a mano... Vedo di modificare anche questo passaggi! grazie ancora
- Vuoi Approfondire?
