Excel File Save As using Cell Value Info

[rjm65]

I have the following code so that the file save name will be the contents of cell B1, plus the date and time:

SuggName = Left(Sheets("PO").Range("B1"), 5) _
& ("_") & Application.Text(Now(), "mmddyy") & "_" & Application.Text(Now(), "hhmm") & ".XLS"
Application.Dialogs(xlDialogSaveAs).Show SuggName

This will suit by needs to some extent, but ideally I would like for it to save the file to a directory also named the same as the contents of cell B1, and to create that directory if it does not exist. I want to automate the saving process as much as possible. Cell B1 contains my customer's name, and I want to save their PO's in folders named after my customer, and each file in that folder to be named customername_date_time.xls.



Thanks,
Raymond

 

[MadForIt]

Hi rjm65

If the SaveAs dialog box is not required you could try something like this.

Sub PO_Save()
DirName = Sheets("PO").Range("B1")
SuggName = Left(DirName, 5) _
& ("_") & Application.Text(Now(), "mmddyy") & "_" & Application.Text(Now(), "hhmm") & ".XLS"

On Error Resume Next
ChDir ("C:\" & DirName)
If Err = 76 Then
MkDir ("C:\" & DirName)
End If

ActiveWorkbook.SaveAs FileName:="C:\" & DirName & "\" & SuggName
End Sub