Macro per progressivo e salvataggio

[Franco Franco]

Buongiorno a tutti, vorrei automatizzare un file nota spese, cercando di dare un progressivo ad una cella specifica, nel mio caso H1, e vorrei che alla sola chiusura, senza salvare il file, mi permettesse di salvare in pdf, e di rinominare il file utilizzando le celle con nome e cognome, G1 e G2, n. Carta di credito, M5, e data N5.

Il nome da dare al file sarebbe NS23-001_nome_cognome_n.Carta_dd.mm.yyyy.

Alla riapertura del file vorrei che il progressivo denominato NS23-001 passasse in automatico ad NS23-002 e così via. 

Pensate sia possibile?

Grazie per l'aiuto, sono totalmente digiuno di vba. 

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Franco ha scritto:

Pensate sia possibile?

Sì è possibile ma

Franco ha scritto:

sono totalmente digiuno di vba

...equivale a chiedere a qualcuno di fare questo lavoro 🙂
Poiché con le formule non è possibile fare quello che chiedi, è necessaria l'automazione: io personalmente sono anche disposto a venirti incontro ma non garantisco piena disponibilità di tempo e comunque otterresti un prodotto rigido, che non puoi modificare e adattare alle tue esigenze future (perché ti assicuro che l'appetito viene mangiando 😀 )

Il consiglio è quello di cominciare a mettere il naso in questa materia, c'è un tutorial online di base piuttosto chiaro (pur se datato) anche nella Home del nostro forum, ma in giro ne trovi a badilate.

[Franco71Partecipante Franco71]

Innanzitutto ti ringrazio per la risposta, e si, essendo totalmente digiuno avrei avuto bisogno di un qualcosa di già compilato. Non intendo certo rubare del tempo a nessuno, e prenderò in seria considerazione il tuo consiglio. 

vecchio frac ha scritto:

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Non è che voglio scoraggiarti e anzi confermo la disponibilità per un lavoro veloce, e puoi scrivermi in privato così non intasiamo il forum (vecchio_frac[at]hotmail.it). 

[Franco71Partecipante Franco71]

vecchio frac ha scritto:

Non è che voglio scoraggiarti e anzi confermo la disponibilità per un lavoro veloce, e puoi scrivermi in privato così non intasiamo il forum (vecchio_frac[at]hotmail.it). 

Grazie mille, approfitto della tua generosità allora. 

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Il problema era aggiornare al volo le informazioni del foglio NOTA SPESE e predisporre il file per i futuri inserimenti senza intaccarne la struttura e senza salvarlo, ma conservando il contatore aggiornato per il nuovo inserimento.
Ho provato varie soluzioni, alcune normali, altre più esotiche:
- compilo i dati richiesti, genero il pdf, aggiorno il contatore, svuoto i campi, salvo il file;
- compilo i dati richiesti, aggiorno il contatore e lo salvo in una cella remota, genero il pdf, svuoto i campi, salvo il file;
- compilo i dati richiesti, aggiorno il contatore e lo salvo in un file esterno di testo, genero il pdf, non salvo il file e chiudo. Al prossimo riavvio leggo il file di testo e aggiorno il contatore;
- salvo il file con altro nome, leggo il contatore dal file originale (con varie tecniche, tra cui anche la connessione via ADO, con esiti strampalati), aggiorno il contatore del nuovo file, compilo i campi, genero il pdf, chiudo senza salvare;
- creo un file vbs che effettua la copia del file originale in un file di appoggio, legge il contenuto del campo contatore originale, apre una copia del file, vi inserisce il contatore aggiornandolo, mi lascia compilare i campi, genero il pdf, chiudo senza salvare, quindi elimina il file appoggio.

Nessuna di queste soluzioni mi è piaciuta, o perché macchinosa o perché si tratta del cane che si morde la coda (gli eventi BeforeClose e BeforeSave si continuano ad eseguire ad ogni tentativo di chiusura anche senza salvare).
Alla fine ho optato per questa:
- creo una copia del foglio e lo chiamo MASTER;
- modifico i campi del foglio NOTA SPESE;
- al tentativo di chiusura (come da istruzioni dell'OP) genero il pdf;
- aggiorno il contatore del foglio MASTER;
- elimino il foglio NOTA SPESE compilato;
- copio il file MASTER come nuovo foglio NOTA SPESE (quindi è vuoto ma col contatore aggiornato);
- salvo (questo è inevitabile).

Non posso postare il file per motivi di riservatezza ma il codice è questo:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim s As String
Dim v As Variant
Dim fd As FileDialog

    Application.EnableEvents = False
    
    If Range("B9") = "" Then s = "Inserire nominativo.§"
    If Range("B10") = "" Then s = s & "Inserire cognome.§"
    If Range("D4") = "" Then s = s & "Inserire n° carta di credito.§"
    If Range("H1") = "" Or Not IsDate(Range("H1")) Then s = s & "Inserire data."
    
    If s <> "" Then
        s = Replace("Attenzione§" & s & "§Salvataggio in pdf non possibile.§Uscire ugualmente?", "§", vbNewLine)
        If MsgBox(s, vbInformation + vbYesNo, "Informazioni mancanti") = vbNo Then
            Application.EnableEvents = True
            Cancel = True
            Exit Sub
        End If
    Else
        s = "¶A_¶B_¶C_¶D"
        s = Replace(s, "¶A", Range("H1"))
        s = Replace(s, "¶B", Range("B9"))
        s = Replace(s, "¶C", Range("B10"))
        s = Replace(s, "¶D", Format(Range("H1"), "dd.mm.yyyy"))
        
        Set fd = Application.FileDialog(msoFileDialogSaveAs)
        With fd
            .AllowMultiSelect = False
            .InitialFileName = s & ".pdf"
            .FilterIndex = 25
            If .Show = 0 Then Exit Sub
        End With
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fd.SelectedItems(1) & ".pdf"
        
        v = Split(Worksheets("NOTA SPESE").Range("H1"), "-")
        s = v(0) & "-" & Format(v(1) + 1, "000")
        Worksheets("MASTER").Range("H1") = s
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        Worksheets("MASTER").Visible = True
        Worksheets("MASTER").Copy after:=Worksheets("MASTER")
        Worksheets("NOTA SPESE").Delete
        Worksheets("MASTER (2)").Name = "NOTA SPESE"
        Worksheets("MASTER").Visible = False
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        
        ThisWorkbook.Save
        Application.EnableEvents = True
    End If
    
    Application.Quit
End Sub

[Franco Franco]

Ringrazio vecchio frac per il supporto e la grande disponibilità dimostrata, ma probabilmente sto sbagliando qualcosa, perché non riesco a fare funzionare l'automazione. Non so se sia il caso a questo punto di allegare il file su cui vecchio frac ha lavorato, nel caso autorizzo la pubblicazione. 

[Franco71Partecipante Franco71]

Buongiorno, edito il messaggio precedente, in quanto sembrerebbe che ci fosse un errore nella compilazione di un valore riguardante una cella.

di seguito il codice corretto andando a modificare queste righe solo nella parte in neretto:

If Range("H2") = "" Or Not IsDate(Range("H2")) Then s = s & "Inserire data."

s = Replace(s, "¶D", Format(Range("H2"), "dd.mm.yyyy")

Ora riesco a salvare in pdf, dopodichè chiude e mi avanza il progressivo, mentre prima mi dava la message box in cui non mi permetteva di proseguire.

allego anche il file excel in bozza, devo ripulirlo da alcune cose, ma quello è compito mio.

 

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim s As String
Dim v As Variant
Dim fd As FileDialog

    Application.EnableEvents = False
    
    If Range("B9") = "" Then s = "Inserire nominativo.§"
    If Range("B10") = "" Then s = s & "Inserire cognome.§"
    If Range("D4") = "" Then s = s & "Inserire n° carta di credito.§"
    If Range("H1") = "" Or Not IsDate(Range("H2")) Then s = s & "Inserire data."
    
    If s <> "" Then
        s = Replace("Attenzione§" & s & "§Salvataggio in pdf non possibile.§Uscire ugualmente?", "§", vbNewLine)
        If MsgBox(s, vbInformation + vbYesNo, "Informazioni mancanti") = vbNo Then
            Application.EnableEvents = True
            Cancel = True
            Exit Sub
        'Else
        '    ThisWorkbook.Close False
        End If
    Else
        s = "¶A_¶B_¶C_¶D"
        s = Replace(s, "¶A", Range("H1"))
        s = Replace(s, "¶B", Range("B9"))
        s = Replace(s, "¶C", Range("B10"))
        s = Replace(s, "¶D", Format(Range("H2"), "dd.mm.yyyy"))
        
        Set fd = Application.FileDialog(msoFileDialogSaveAs)
        With fd
            .AllowMultiSelect = False
            .InitialFileName = s & ".pdf"
            .FilterIndex = 25
            If .Show = 0 Then Exit Sub
        End With
        
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fd.SelectedItems(1) & ".pdf"
        
        v = Split(Worksheets("NOTA SPESE").Range("H1"), "-")
        s = v(0) & "-" & Format(v(1) + 1, "000")
        Worksheets("MASTER").Range("H1") = s
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        Worksheets("MASTER").Visible = True
        Worksheets("MASTER").Copy after:=Worksheets("MASTER")
        Worksheets("NOTA SPESE").Delete
        Worksheets("MASTER (2)").Name = "NOTA SPESE"
        Worksheets("MASTER").Visible = False
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        
        ThisWorkbook.Save
        Application.EnableEvents = True
    End If
    
    Application.Quit
End Sub

Allegati:

You must be logged in to view attached files.

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Franco71 ha scritto:

If Range("H1") = "" Or Not IsDate(Range("H2")) Then s = s & "Inserire data."

Probabilmente mi sono confuso con i range originali.

Ma attenzione che se H2 è la cella in cui devi inserire la data allora l'istruzione deve essere coerente:

If Range("H2") = "" Or Not IsDate(Range("H2")) Then s = s & "Inserire data."

[Franco71Partecipante Franco71]

vecchio frac ha scritto:

Franco71 ha scritto:

If Range("H1") = "" Or Not IsDate(Range("H2")) Then s = s & "Inserire data."

si, mi sono accorto ed oltre a correggermi ho aggiunto anche un ulteriore stringa che chiede di inserire nel nome file anche il numero carta....

Ora, gentilmente avrei alcune domande per perfezionare la cosa:

1) vorrei che il file di origine avesse come riferimento data (H2) la cella g4, non sono riuscito a modificarlo, in quanto lavoro sul duplicato... come faccio a lavorare sul master?

2) quando chiudo mi porta alla finestra salva, ma mi da come predefinito estensione .xla, mentre mi sembra di capire che nel codice chieda esplicitamente di salvare in .pdf, perchè?

3) se invece di salvare volessi solo che alla chiusura mi mandasse in stampa il file appena compilato sarebbe possibile? il file lo salverei dopo aver allegato altre scansioni (scontrini)

 

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Ciao Franco,

1) il foglio MASTER è nascosto. Per visualizzarlo  fai così:
- apri il file (non serve attivare le macro, anzi è meglio se non lo fai)
- accedi all'editor di VBA (Alt-F11)
- attiva la finestra Immediata (Ctrl-G)
- digita foglio1.visible=True e premi invio
- fai la modifica necessaria
- ritorna nell'a finestra Immediata e stavolta metti False invece che True: foglio1.visible=False e premi invio
- salva il file ed esci

2) Sulla mia copia il comando di esportazione propone nella finestra di dialogo il formato PDF (che è il 25.mo item nell'elenco della versione Excel in mio possesso, Excel 2013). Tu che versione di Excel utilizzi? Nota che è solo una proposta della finestra di dialogo per scegliere la cartella di destinazione, in quanto il nome di file viene costruito come pdf internamente. Non cambiare il nome al file e tutto si salverà come pdf.

3) Il salvataggio è necessario per conservare nel foglio Master (che viene mantenuto nascosto) il prossimo numero del contatore progressivo. Per mandare in stampa puoi usare ActiveSheet.PrintPreview (anteprima) o ActiveSheet.PrintOut (stampa diretta). Ma allora ti consiglio di creare un pulsantino e associare una macro che faccia questo (imposta il foglio, i margini, fa selezionare la stampante e lancia la stampa oppure l'anteprima). Nota che i pulsanti di stampa e anteprima sono già disponibili nella barra multifunzione in alto (basta personalizzarla).

 

[Franco71Partecipante Franco71]

vecchio frac ha scritto:

Ciao Franco,

vecchio frac, il punto 1 mi è chiaro, e procedo in merito, il punto 2 mi salva il file si come pdf, ma oltre al nome corretto, prima di ".pdf" mi inserisce un .xla che a me non serve. per capirci: "NS23-002_pinco_pallino_12.01.2022_1221.xla.pdf"

Uso office 365. 

il punto 3 sono abbastanza in alto mare, ma vedo se ragionandoci su, e facendo alcune prove posso riuscire nell'intento. grazie.

[Franco71Partecipante Franco71]

Finalmente sono riuscito ad ottenere quello che volevo, grazie a vecchio frac.

Metto di seguito il codice funzionante per chi vuole prendere spunto.

cercherò di studiare un pochino e di seguire di più il forum, non so come poter chiudere con "Risolto".

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim s As String
Dim v As Variant
Dim fd As FileDialog

    Application.EnableEvents = False
    
    If Range("B9") = "" Then s = "Inserire nominativo.§"
    If Range("B10") = "" Then s = s & "Inserire cognome.§"
    If Range("D4") = "" Then s = s & "Inserire n° carta di credito.§"
    If Range("H2") = "" Or Not IsDate(Range("H2")) Then s = s & "Inserire data."
    
    If s <> "" Then
        s = Replace("Attenzione§" & s & "§Salvataggio in pdf non possibile.§Uscire ugualmente?", "§", vbNewLine)
        If MsgBox(s, vbInformation + vbYesNo, "Informazioni mancanti") = vbNo Then
            Application.EnableEvents = True
            Cancel = True
            Exit Sub
        'Else
        '    ThisWorkbook.Close False
        End If
    Else
        s = "¶A_¶B_¶C_¶D_¶E"
        s = Replace(s, "¶A", Range("H1"))
        s = Replace(s, "¶B", Range("B9"))
        s = Replace(s, "¶C", Range("B10"))
        s = Replace(s, "¶D", Range("D4"))
        s = Replace(s, "¶E", Format(Range("H2"), "dd.mm.yyyy"))
        
        
        Set fd = Application.FileDialog(msoFileDialogSaveAs)
        With fd
            .AllowMultiSelect = False
            .InitialFileName = s & ".pdf"
            .FilterIndex = 25
            If .Show = 0 Then Exit Sub
        End With
        
        ActiveSheet.ExportAsFixedFormat xlTypePDF, Filename:=fd.SelectedItems(1) & ".pdf"
        ActiveSheet.PrintOut
        
        v = Split(Worksheets("NOTA SPESE").Range("H1"), "-")
        s = v(0) & "-" & Format(v(1) + 1, "000")
        Worksheets("MASTER").Range("H1") = s
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        Worksheets("MASTER").Visible = True
        Worksheets("MASTER").Copy after:=Worksheets("MASTER")
        Worksheets("NOTA SPESE").Delete
        Worksheets("MASTER (2)").Name = "NOTA SPESE"
        Worksheets("MASTER").Visible = False
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        
        ThisWorkbook.Save
        Application.EnableEvents = True
    End If
    
    Application.Quit
End Sub

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Chiudo io.

Grazie del feedback   

[Autore]

Articoli