Saving a file using vba

[mr_hiboy]

Hi,


I'm trying to save a file in a location, I'd like the "Month" sub folder to be taken from a cell in the file, i.e. May, June. So instead of month it would be "A1" for example?


ActiveWorkbook.SaveAs Filename:= _

"C:FolderSubfolderMONTHfile.xls"

, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False


thanks

 

[Colin Legg]

You can concatenate the cell's value into the string:

[pre]

ActiveWorkbook.SaveAs _
Filename:="C:FolderSubfolder" & ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value & "file.xlsx", _
FileFormat:=xlOpenXMLWorkbook, _
CreateBackup:=False

[/pre]
Note the FileFormat and the file extension: xlOpenXMLWorkbook is xlsx. Saving in this format will remove any VBA code from the file. If you want to retain VBA code then save as xlOpenXMLWorkbookMacroEnabled with an xlsm extension.

 

[mr_hiboy]

Thanks for quick reply, unfortunately when I try use it, not working. I'll provide my full line in case that makes a difference.


ActiveWorkbook.SaveAs Filename:= _

"C:ReportsWeeklyReports" & ActiveWorkbook.Worksheets("Summary").Range("D77").Value & "2013 Touring Weekly Revenue - " & Format(Now - 1, "DD-MMM-YY") _

, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False


The last bit datestamping it. It works fine if I just use text "May"


thanks

 

[mr_hiboy]

Sorted, it didn't like the date formatted as a month, had to use month()


Cheers