Salvare Fogli + Rename su un altro file

[ildavepabloPartecipante ildavepablo]

Ciao a tutti,

sono nuovo e cerco di approcciarmi a macro e VBA. Ho cercato nel forum, ho trovato alcuni spunti interessanti ma non riesco a trovare la "quadra". Quello che sto cercando di fare è:

1) salvare i fogli di un ALTRO file excel separatamente e rinominati secondo quanto presente in una cella (di quel file)

2) rinominare il file aggiungendo il testo presente in una cella specifica presente nel file che lancia la macro

per entrambi i punti ho trovato una soluzione che funziona quando il file è lo stesso (dove viene salvata la macro), il problema arriva quando il file è un altro, così ho provato di lavorare su  due macro distinte collegandole poi a due bottoni che lanciano le due azioni:

Sub SalvaConNome()

Dim sh As Worksheet
Dim sPath As String
Dim sNome As String

Application.ScreenUpdating = False

sPath = "C:\Users\" '<<=== modificate con la path della directory dove volete salvare i file

On Error Resume Next

[I]Workbooks.Open Filename:="prova.xlsx"[/I]
Application.Run "C:\Users\PROVA.xlsm"!SalvaConNome"

For Each sh In ThisWorkbook.Worksheets

sNome = "INIZIO" & " " & sh.Range("B12") & " " & sh.Range("B11") & " END"

Application.DisplayAlerts = False
sh.Copy
With ActiveWorkbook
.SaveAs Filename:=sPath & sNome
.Close
End With
Next

Application.ScreenUpdating = True

Set sh = Nothing

End Sub

il problema di questa è che non riesco a far lavorare la macro su un file diverso presente in una specifica cartella, ho porvato ad inserire qualcosa trovato in giro ma non riesco a collegarlo poi al resto...

la seconda query:

Sub rename()
    Dim sNome As String
    sNome = "C:\Users\" & sh.Range("A1") & ".xlsx"
    Name "C:\Users\" As sNome
End Sub

capisco che c'è un problema tra il settare il nome come stringa e il fatto che "name" probabilmente non gestisce la variabile "sNome", ma non capisco come aggirare il problema....

infine, intuisco che sia possibile sviluppare una macro che incorpori i due comandi ma non so come svilupparlo... basta metterli uno dopo l'altro?

Grazie mille a tutti per l'aituo!

Pablo

[patelModeratore patel Karma: 51 pts]

Non so se ho capito bene il tuo obiettivo, ma io rinominerei i fogli sul file su cui lanci la macro e poi farei un SaveAs con altro nome, quindi il file originale non viene toccato

[ildavepabloPartecipante ildavepablo]

Grazie Patel,

grazie per il suggerimento, il problema è che entrambe le macro non funzionano 🙁 non so come scrivere un codice che sia utilizzabile su un altro file

Forse ho tralasciato qualche dettaglio nella precedente descrizione:

File A >> excel vuoto, contiene solo le macro (ed è il file che vorrei usare)

File B >> excel con i dati. Non può contenere macro perchè è un file che viene creato di volta in volta e sovrascritto

Io avrei bisogno che dal "file A" lanciando una macro:

1) Apre File B

2) Salva tutti i singoli fogli separatamente (con un nome specifico presente in una cella del File B)

3) Rinomini il File B (originario, quindi con tutti i fogli)

non necessariamente in questo ordine, come suggerisci tu 🙂 potrebbe essere 1-3-2 >> ma resta il problema del file diverso da dove risiede la macro

Grazie!

[patelModeratore patel Karma: 51 pts]

puoi allegare un file di esempio di tipo B ?

[ildavepabloPartecipante ildavepablo]

Ciao,
in allegato il "FILE B".
1) è un normalissimo excel, dove tutti i fogli hanno la stessa struttura e cambiano solo i dati all'interno della tabella.
2) ogni foglio deve essere salvato singolarmente nella cartella e il nome dovrà essere: così composto: "DIP"&"B2"&"B1"
quindi nella cartella dovrò avere:
DIP xxxxxxx July 19.xlsx >> che contiene solo la tablella del "Foglio1"
DIP yyyyyyy July 19.xlsx >> che contiene solo la tablella del "Foglio2"
DIP zzzzzzz July 19.xlsx>> che contiene solo la tablella del "Foglio3"
...
DIP n July 19.xlsx

mi sono accorto ora che il file allegato è .xls, in realtà il file sarà un .xlsx

grazie!!

Allegati:

You must be logged in to view attached files.

[mauro27Partecipante mauro27]

mi sembra una macro possa funzionare correttamente anche se va a lavorare in un file diverso

(non usare .xlsx , ma .xls o .xlsm )

Sub SalvaFogli()
'
    
    Dim NuovoFile As String
    Dim QuestoFile As String
    Dim Base As String
    
    QuestoFile = ActiveWorkbook.Name
    Base = QuestoFile
    ' tolgo l'estensione, tolgo 4 caratteri dalla fine, poi anche il punto se c'e'
    Base = Mid(Base, 1, Len(Base) - 4)
    If Right(Base, 1) = "." Then
        Base = Mid(Base, 1, Len(Base) - 1)
    End If
    
    Sheets("Foglio1").Select
    Sheets("Foglio1").Copy
    'Sheets(Array("Foglio1", "Foglio2")).Copy
    NuovoFile = Base & " - " & Range("B1").Value & " - " & Range("B2").Value & ".xls"
    
    ' creo il file
    ActiveWorkbook.SaveAs Filename:=NuovoFile
    NuovoFile = ActiveWorkbook.Name
    ' ritorna
    Workbooks(QuestoFile).Activate
    
    Sheets("Foglio2").Select
    Sheets("Foglio2").Copy
    'Sheets(Array("Foglio1", "Foglio2")).Copy
    NuovoFile = Base & " - " & Range("B1").Value & " - " & Range("B2").Value & ".xls"
    
    ' creo il file
    ActiveWorkbook.SaveAs Filename:=NuovoFile
    NuovoFile = ActiveWorkbook.Name
    ' ritorna
    Workbooks(QuestoFile).Activate
    
    Sheets("Foglio3").Select
    Sheets("Foglio3").Copy
    'Sheets(Array("Foglio1", "Foglio2")).Copy
    NuovoFile = Base & " - " & Range("B1").Value & " - " & Range("B2").Value & ".xls"
    
    ' creo il file
    ActiveWorkbook.SaveAs Filename:=NuovoFile
    NuovoFile = ActiveWorkbook.Name
    ' ritorna
    Workbooks(QuestoFile).Activate

End Sub

 

 

Allegati:

You must be logged in to view attached files.

[ildavepabloPartecipante ildavepablo]

Ciao Mauro! Grazie per il supporto. Ho visto la tua risposta, ma ci sono due punti: il primo è che i fogli del report saranno 2-300, quindi non è fattibile seguire una strada che lavora di volta in volta su ogni singolo report, dovrei usare un ciclo o un comando valido per tutti gli "-n" fogli" del file. Il secondo è che il bottone della macro così sarebbe nel "file B", mentre il mio problema è far partire il tutto con un "pulsate" inserito nel "File A".

grazie

[ildavepabloPartecipante ildavepablo]

Ciao,

credo di essere riuscito a sistemare il codice per lanciare la query da un altro file... ho provato semplicemente a fare registratore di macro... 🙂

Mi resta solo un piccolo scoglio ora... la parte finale: ovvero il rename del file... c'è qualcosa in questa stringa che non va (errore di sintassi) ma non riesco a capire come correggere:

sNome1 = ActiveWorkbook.Worksheets Range("B11").Copy

sotto il codice completo della parte finale.... 

Workbooks.Open Filename:="C:\fileB.xlsx"
    
    sNome1 = ActiveWorkbook.Worksheets Range("B11").Copy
    
    nameActiveWorkbook.SaveAs Filename:= _
        "C:\fileB " & sNome1 & ".xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=Fals
    ActiveWindow.Close

[Marius44Moderatore Marius44 Karma: 58 pts]

Salve a tutti

Vedi se la seguente macro fa tutto quello che chiedi. 

E' importante che TUTTI i file stiano nella stessa Directory.

Sub SalvaFoglioConNome()
perc = ActiveWorkbook.Path & "\"
percorso = perc & "Esempio-FOGLIO-B.xls"
Workbooks.Open percorso
For i = 1 To ActiveWorkbook.Sheets.Count
  With Sheets(i)
    .Cells.Copy                       'copia il foglio
    Workbooks.Add                     'crea un nuovo file
    ActiveSheet.Paste                 'incolla i dati
    'rinomina il file e lo salva
    NuovoFile = "Dip - " & Range("B2").Value & " - " & Range("B1").Value & ".xls"
    ActiveWorkbook.SaveAs Filename:=perc & NuovoFile
  End With
  ActiveWorkbook.Close 'chiude il file appena salvato
Next i
End Sub

Ciao,

Mario

[ildavepabloPartecipante ildavepablo]

Grazie mille Mario, provo subito e ti faccio sapere!

[ildavepabloPartecipante ildavepablo]

Ciao Mario,

OK la prima parte funziona alla grande!!! Mi manca ora un secondo passaggio: creare una sorta di back-up dell'ultimo periodo (perchè ogni mese/settimana riscaricherò nella cartella un nuovo "FILE B" relativo all'ultimo periodo).

Quindi avrei bisogno che dopo la prima parte di codice che hai scritto, venga creata una copia del  "FILE B" che si chiami "FILE B SEP19" (dove SEP19) è il contenuto di una cella "B11" di un qualsiasi foglio del "FILE B" (tanto i fogli di "FILE B" hanno tutti la stessa struttura).

Nota: non posso usare comandi/funzioni di data o formato perchè non è detto che il periodo coincida: ad esempio  con data automatica uscirebbe SEP19, mentre il file che aggiorno potrebbe essere relativo a (GIU18 o Mag19 etc etc...) e per questo è importante che il nome peschi dalla cella, un po' come fatto prima.

Grazie mille!!!

[ildavepabloPartecipante ildavepablo]

Suuuuperrrr Mario!!!

ho provato a fare una modifica partendo  dal tuo file e funziona!!  ho semplicemente aggiunto una parte del codice che avevi scritto tu e messa in calce "creando delle nuove variabili!   

perc1 = ActiveWorkbook.Path & "\"
percorso1 = perc1 & "DIFOT.xlsx"
Workbooks.Open percorso

    NuovoFile1 = "DIFOT " & Range("B11") & ".xlsx"
    ActiveWorkbook.SaveAs Filename:=perc1 & NuovoFile1

ActiveWorkbook.Close 'chiude il file appena salvato

[ildavepabloPartecipante ildavepablo]

Buongiorno a tutti, buongiorno Mario... non odiarmi! 🙂

mi sono accorto che potrei implementare un piccolo upgrade nella maro che però mi farebbe risparmiare un bel po' ti tempo... visto che i fogli da salvare singolarmente sono circa 300, ma non tutti sono "carichi" (contengono tabelle con dati), è possibile inserire una specie di "cliclo IF" che dice "se la cella B8" è vuota, salta foglio e non salvare, se contiene dati allora procedi con la routine e salvi e vai anvanti.

Grazie mille!!!! 

[Marius44Moderatore Marius44 Karma: 58 pts]

Ciao

Occorre "aggiustare" la macro alle nuove esigenze (guarda i commenti)

Sub SalvaFoglioConNome()
perc = ActiveWorkbook.Path & "\"
percorso = perc & "Esempio-FOGLIO-B.xls"
Workbooks.Open percorso
For i = 1 To ActiveWorkbook.Sheets.Count
  With Sheets(i)
    If .Range("B8") <> "" Then        'riga aggiunta xxxxxxxxxxxxxxxxxxxxxxx
    .Cells.Copy                       'copia il foglio
    Workbooks.Add                     'crea un nuovo file
    ActiveSheet.Paste                 'incolla i dati
    'rinomina il file e lo salva
    NuovoFile = "Dip - " & Range("B2").Value & " - " & Range("B1").Value & ".xls"
    ActiveWorkbook.SaveAs Filename:=perc & NuovoFile
    End If                            'riga aggiunta xxxxxxxxxxxxxxxxxxxxxxxx
  End With
  ActiveWorkbook.Close 'chiude il file appena salvato
Next i
End Sub

Fai sapere. Ciao,

Mario

[ildavepabloPartecipante ildavepablo]

Grazie  Mario,

lanciando la maro però mi segnala l'errore: <em>"Error di run-time '9': Indice non incluso nell'intervallo"</em>

con debug, la stringa incriminata è questa:

<em>With Sheets(i)</em>

Grazie mille!!

[Marius44Moderatore Marius44 Karma: 58 pts]

Ciao

Ti riposto la macro completa (c'era qualche errore di posizionamento). Adesso funziona (testata).

Sub SalvaFoglioConNome()
perc = ActiveWorkbook.Path & "\"
percorso = perc & "Esempio-FOGLIO-B.xls"
Workbooks.Open percorso
For i = 1 To ActiveWorkbook.Sheets.Count
  With Sheets(i)
    If .Range("B2") <> "" Then        'riga aggiunta xxxxxxxxxxxxxxxxxxxxxxx
      .Cells.Copy                       'copia il foglio
      Workbooks.Add                     'crea un nuovo file
      ActiveSheet.Paste                 'incolla i dati
      'rinomina il file e lo salva
      NuovoFile = "Dip - " & Range("B2").Value & " - " & Range("B1").Value & ".xls"
      ActiveWorkbook.SaveAs Filename:=perc & NuovoFile
      ActiveWorkbook.Close 'chiude il file appena salvato
    End If                            'riga aggiunta xxxxxxxxxxxxxxxxxxxxxxxx
  End With
Next i
End Sub

Fai sapere. Ciao,

Mario

[mauro27Partecipante mauro27]

sembra tutto facile vederlo fare da te   

[ildavepabloPartecipante ildavepablo]

Super Mario!!! Tutto alla grande!

Grazie mille ancora

[ildavepabloPartecipante ildavepablo]

Ciao a tutti ragazzi!

rieccomi a voi! nuovo miglioramento che vorrei implementare....

quello che vorrei fare è al lancio della macro:

1) creazione di una nuova cartella (posizionata nella stessa directory dove è presente il file con la macro e il file dove agisce la macro) il cui nome è il periodo di riferimento "yymm" (quindi ex 1909 per settembre, 1910 per ottobre...)

2) quando parte la macro vera e propria i file vengono salvati nella cartella precedentemente creata

 

la mia idea era quella di creare la variabile cartella...  con percorso e poi cambiare il salvataggio dei singoli  file.. qualcosa tipo questo:

Dim cartella As String

    cartella = ActiveWorkbook.Path "\" & Format(Date, "yymm")

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

NuovoFile = "DIFOT " & Range("A3").Value & " - " & Range("A6").Value & ".xlsx"
ActiveWorkbook.SaveAs Filename:=cartella & NuovoFile
 

...ma come sapete... i buoni prpositi e le azioni.. non sempre coincidono con risultato finale funzionante! 🙂 🙂

Grazie mille!!!

 

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

Hai definito il nome della cartella e sta bene, ma non l'hai creata. E per non crearla ogni volta che fai girare la macro ti serve un modo per controllare che non esista già. Quindi il flusso delle tue azioni deve essere questo:

- definisco la stringa assegnata alla variabile "cartella" (nota: "cartella" contiene già il percorso oltre al nome della cartella)

- se "cartella" esiste già come directory, prosegui,

- altrimenti crea la cartella di nome "cartella".

[ildavepabloPartecipante ildavepablo]

Ciao,

sono fermo al primo step. Non riesco a far creare una cartella "dinamica", nel senso che il nome è composto dal nome della cartella (fisso) e dalla data (yymm) ex. 1909

Allego codice. non so quale comando usare per renderla "dinamica"

Sub CreareCartella()
Dim periodo As String

Set fso = CreateObject("Scripting.FileSystemObject")
periodo = "DIFOT" & Format(Data, (YY))

fso.CreateFolder "C:\xxx\" & periodo

End Sub

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

E' l'uso di Format che è scorretto.

Si usa così (controlla la Guida in linea per i dettagli): Format(Date, "yymm") che restituisce "1910"

ildavepablo ha scritto:

non so quale comando usare per renderla "dinamica"

Come hai fatto va bene (rispettando la sintassi di Format).

Sub CreareCartella()
Dim periodo As String

    Set fso = CreateObject("Scripting.FileSystemObject")
    periodo = "DIFOT" & Format(Date, "yymm")

    fso.CreateFolder "C:\xxx\" & periodo

End Sub

Senza scomodare un oggetto fso (che è sempre una possibilità, ma appesantisce) puoi usare un metodo nativo di VBA per creare directory:

Sub CreareCartella()
Dim periodo As String

    periodo = "DIFOT" & Format(Date, "yymm")
    MkDir "C:\xxx\" & periodo

End Sub

Ora che hai scoperto come si crea una cartella, procedi con il controllo sull'esistenza di tale cartella nel percorso specificato, e comportati diversamente a seconda dei casi (esiste/non esiste).

[ildavepabloPartecipante ildavepablo]

ciao!

è uno stillicidio! 🙂 spero di non rompere troppo le..... passo a passo cerco di andare avanti.

cercando in rete adesso ho capito quale è il comando per il controllo ma non riesco a scrivere il codice. mi sembra di aver capito che  non sia possibile mettere MKDIR in una funzione... ma come faccio allora!?! 🙂 lo so che la soluzione è proprio lì li... 🙂

Sub CreareCartella()
Dim periodo As String
Dim percorso As String


    periodo = "file_" & Format(Date, "yymm")
    percorso = (MkDir("C:\Users\xxxxxxx\") & periodo)

        If (Len(Dir(percorso, vbDirectory)) > 0) Then
            MsgBox "La cartella esiste"
        Else
            MsgBox "La cartella non esiste"
        End If

End Sub

[vecchio fracSenior Moderator vecchio frac Karma: 272 pts]

ildavepablo ha scritto:

è uno stillicidio!

Meglio fare piccoli passi e assimilare i concetti. In fondo è solo questione di tempo 🙂

ildavepablo ha scritto:

mi sembra di aver capito che  non sia possibile mettere MKDIR in una funzione

ildavepablo ha scritto:

percorso = (MkDir("C:\Users\xxxxxxx\") & periodo)

L'istruzione MkDir (che sta per MaKe Dir, crea directory) può stare in qualsiasi blocco di istruzioni, sub o function. Ma è un comando che tu dai all'interprete e non restituisce un valore da riassegnare a una variabile.

Infine, il pezzo di codice che hai postato da ultimo va abbastanza bene, solo che prima devi controllare se la cartella esiste e solo dopo, in caso di esito negativo, la crei (non circondare la variabile "percorso" di parentesi, non servono in questo contesto).

Sub CreareCartella()
Dim periodo As String
Dim percorso As String

    periodo = "file_" & Format(Date, "yymm")
    percorso = "C:\Users\xxxxxxx\" & periodo

        If (Len(Dir(percorso, vbDirectory)) > 0) Then
            MsgBox "La cartella esiste"
            ' tutto ok, questa sub ha esaurito il suo compito
            ' perchè la cartella esiste già
        Else
            MsgBox "La cartella non esiste"
            ' qui creo la cartella
            ...istruzione per creare la directory: quale sarà?
        End If
End Sub

 

[ildavepabloPartecipante ildavepablo]

Ciao VF!

non sono sparito!!!

...molto contento perchè ho individuato una modifica da fare e sono riuscito ad implementarla (il nome della cartella non sarà più legato al format(date) ma  al valore di una cella (come accade per gli altri file). 

quello che sto facendo ora è cercare di fondere le due parti della macro: creare la cartella, far girare la macro "originale" e salvare i file nella cartella precedentemente salvata (cambiando la path di salvataggio).

ahime qualcosa non funziona, mi sto un po' perdendo tra riferimenti e percorsi... prima di chiederti aiuto volevo fare ancora un po' di tentativi... ho capito che devo modificare la cartella di riferimento andando a modificare la struttura della variabile vecchia sostituendola con il il percorso creato da MKDir (variabile che ho chiamato CARTELLA).

spero di non fare troppi casini 🙂 

[Autore]

Articoli