Michele Petraroli
Michele Petraroli

Reputation: 99

Save file xls in dynamic directory

I am trying to create a macro that saves the xls file in a specific directory called Months in this directory there are 12 other directories with the name of the months. I wrote a Macro where the file is saved in the directory months, but I need that the file is saved in a specific directory based from the value of the month in the cell of my sheet. Below my code:

Sub Macro1()

' ' Macro1 Macro ' Salva con nome giorno e data ' ' Scelta rapida da tastiera: CTRL+p '

NomeFile = Range("B2").Value 'Cella da cui prendere il nome file
NomeCartella = Range("D2").Value 'Cella da cui prendere il nome del mese
NomeFoglio = Range("A2").Value
If NomeFile = "" Then Exit Sub
If Right(NomeFile, 4) <> ".xls" Then NomeFile = NomeFile & ".xls"

Cartella = "C:\Users\Michele\Documents\la piazzetta\Mesi\" 'percorso completo su cui salvare
CartellaMese = NomeCartella
ActiveWorkbook.SaveAs Filename:=Cartella \ CartellaMese & NomeFile, FileFormat:=xlNormal, Password:="", WriteResPassword:="", CreateBackup:=False



End Sub

Upvotes: 2

Views: 212

Answers (2)

Michele Petraroli
Michele Petraroli

Reputation: 99

Thank you @Gabor worked but I needed to add some other things here the Whole code

Sub Macro1()
NomeFile = Range("B2").Value 'Cella da cui prendere il nome file
NomeCartella = Range("D2").Value **& "\"** 'Cella da cui prendere il nome del mese
NomeFoglio = Range("A2").Value
If NomeFile = "" Then Exit Sub
If Right(NomeFile, 4) <> ".xls" Then NomeFile = NomeFile & ".xls"

Cartella = "C:\Users\Michele\Documents\la piazzetta\Mesi" 'percorso completo su cui salvare
CartellaMese = NomeCartella
ActiveWorkbook.SaveAs Filename:=Cartella & "\" & CartellaMese & NomeFile, FileFormat:=xlNormal, Password:="", WriteResPassword:="", CreateBackup:=False
End Sub

Upvotes: 1

Gabor
Gabor

Reputation: 695

This should look like this:

ActiveWorkbook.SaveAs Filename:=Cartella & "\" & CartellaMese & NomeFile, FileFormat:=xlNormal, Password:="", WriteResPassword:="", CreateBackup:=False

Upvotes: 2

Related Questions