Saving Using Path Definition

[hanjunxi80]

Beautiful, Norie. As usual.

Greetings. Great thread. Couple of questions? I'd like to be a define with file path with more granularity in additional to saving if it doesn't exist . In an Excel Spreadsheet I'd like be able to define the file name based on cell values. For example:

Path Component	Name	Output
Drive	C:\
Folder	Desktop	C:\Desktop
Folder	User	C:\Desktop\User\job
File Name	job	C:\Desktop\User\job
File Name	123	C:\Desktop\User\job\123-
File Name	Small Town USA	C:\Desktop\User\job\123- Small Town USA
File Name	12/13/2014 6:44	C:\Desktop\User\job\123- Small Town USA 12-13-2014

I found your thread very helpful and will try some of the code I found here. Below is what I've tried so far. I've had challenges with Object definitions. In my code, do I need to build up the file name as demonstrated below or can I just reference a concatenated cell?

Sub Define_SaveAs_Path()
     Dim Path1 As String  'Define drive in my example I'd used C:\
     Dim Path2 As String  'l'd like to allow for selection of three levels of folders and to set the value of a cell to establish the folder name.  If the folder name already exists, I'd like to use else create.
     Dim Path3 As String    ''Sub Folder 1
     Dim Path4 As String    'Sub Folder 2
     Dim FileName1 As String
     Dim FileName2 As String
     Dim FileName3 As String
     Path1 = Sheets("sheet1").Range("a1").Text   'DRIVE'


     Path2 = Sheets("sheet1").Range("a2").Text  'Master Folder'


     If Not fso.FolderExists(Path2) Then
        
         On Error Resume Next
         MkDir directoryname
         On Error GoTo 0


     Path3 = Sheets("sheet1").Range("a3").Text    'Sub Folder 1'


        If Not fso.FolderExists(Path3) Then
        fso.CreateFolder (FLDR_NAME)
        End If


        On Error Resume Next
        MkDir directoryname
        On Error GoTo 0


     Path4 = Sheets("sheet1").Range("a4").Text   'Sub Folder 2'


         If Not fso.FolderExists(Path4) Then
         fso.CreateFolder (FLDR_NAME)
         End If


         On Error Resume Next
         MkDir directoryname
         On Error GoTo 0


         FileName1 = Range("a5")
         FileName2 = Range("a6")
         FileName3 = Range("a7")


         'save the file
         ActiveWorkbook.SaveAs Filename:=Path1 & Path2 & Path3 & Path4 & _
         FileName1 & "" & FileName2 & FileName3 & ".xlsx", FileFormat:=xlsx, _
         CreateBackup:=False
     
     End If
     End Sub

[Bob Phillips]

Sub Define_SaveAs_Path()
Dim fso As Object
Dim directoryName As String  'I'd like to allow for selection of three levels of folders and to set the value of a cell to establish the folder name. _
                              If the folder name already exists, I'd like to use else create.
Dim folderName As String
Dim fileName As String

    Set fso = CreateObject("Scripting.FileSystemObject")
    
    With Worksheets("Sheet1")
    
        directoryName = .Range("a1").Text & .Range("a2").Text 'Master Folder'

        If Not fso.FolderExists(directoryName) Then
            
            On Error Resume Next
            MkDir directoryName
            On Error GoTo 0
        End If
        
        folderName = directoryName & Application.PathSeparator & .Range("a3").Text  'Sub Folder 1'
        If Not fso.FolderExists(folderName) Then
            fso.CreateFolder folderName
        End If
    
        folderName = folderName & Application.PathSeparator & .Range("a4").Text  'Sub Folder 2'
        If Not fso.FolderExists(folderName) Then
            fso.CreateFolder (folderName)
        End If
    
        fileName = .Range("a5").Text & "-" & .Range("a6") & " " & Format(.Range("a7").Value, "yyyy-mm-dd")
    
        'save the file
        ActiveWorkbook.SaveAs fileName:=folderName & Application.PathSeparator & fileName, _
                              FileFormat:=xlOpenXMLWorkbook, _
                              CreateBackup:=False
    End With
End Sub