PDA

View Full Version : Saving Using Path Definition



hanjunxi80
12-13-2014, 04:55 AM
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
12-17-2014, 04:18 PM
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