Save file to folder with variable

[Steve0]

I have built what I believe to be an extremely complex and highly automated estimating program in excel / access which saves the estimate in the folder structure as such: ROOT\YYYY\MM\estimate number\estimate number.xlsm (ROOT\2018\06\659403\659403.xlsm)

this is fine for first run, but once the month rolls to the next month, the previous month is appended with the estimate range:

(ROOT\2018\06\659403\659403.xlsm becomes ROOT\2018\06 - 659400-659500\659403\659403.xlsm).

ALSO, if the estimate is turned into a job, the job number - or multiple job numbers - is appended to the estimate folder:

(ROOT\2018\06 - 659400-659500\659403\659403.xlsm becomes ROOT\2018\06 - 659400-659500\659403_54980_54981\659403.xlsm)

The first 2 numbers of the month folder will not change once created, and the first 6 numbers of the estimate folder not change once created.

However, If I go back into the estimate after either the month or estimate folder name has been altered, it obviously fails to save. (we create revisions on estimates, so an estimate 123456 will have in it's folder 123456.01 etc, and these almost always happen after one or more of the path folders has been altered.)

Here is my save code right now:



Sub SaveWorkbook(SrcWBType As String)
Dim Project As String, Customer As String, FinalName As String, FileNamePart As String
Dim BaseEJNum As String

'some of the variables called are dim'ed publicly as they are used in multiple macros


BaseEJNum = Left(EJNum, 6)

Application.DisplayAlerts = False
DateFilePath = Year(Now) & "\" & Right("00" & Month(Now), 2)
If TargetWorkbook.Sheets("RFQ").Range("Revision") = "Yes" Then DateFilePath = Year(TargetWorkbook.Sheets("Data_Tables").Range("QuoteDate")) & "\" & Right("00" & Month(TargetWorkbook.Sheets("Data_Tables").Range("QuoteDate")), 2)
Project = TargetWorkbook.Sheets("RFQ").Range("Desc1") & "_" & TargetWorkbook.Sheets("RFQ").Range("Desc2")
Customer = TargetWorkbook.Sheets("RFQ").Range("CustomerName")
FileNamePart = EJNum & "_" & Customer & "_" & Project & WBType & "-" & TargetWorkbook.Sheets("Data_Tables").Range("Version")
FinalName = FileNamePart & ".xlsm"
TargetWorkbook.SaveAs RootDir & mParentDir & "\" & DateFilePath & "\" & BaseEJNum & "\" & FinalName, FileFormat:=52
Application.DisplayAlerts = True


End Sub



I have had no luck in building the path.... and can't seem to find anyone else trying to do what I am trying to do. any help would be appreciated.

[SamT]

This is not complete, but I think you'll get the idea. It goes in a standard Module

Option Explicit

Sub InitNewCustomProperties()
'Run once, only when creating new Estimate
'Custom Properties are saved with the Workbook

Start:
.Item("PathsSet") = "False"
.Item("DateFilePath") = ""
.Item("ProjectPath") = ""
.Item("RevisionNumber") = "00"
.Item("JobNumber") = ""
'Ad Items as desired, set all to "" At this time
End With
End Sub

Sub InitWorkbookPaths()
'Used to set only the unchanging original paths

With ThisWorkbook.CustomDocumentProperties
If UCase(.Item("PathsSet")) = "TRUE" Then Exit Sub

.Item("DateFilePath") = Format(Date, "yyyy") & "\" & Formate(Date, "mm")
.Item("Project") = ThisWorkbook.Sheets("RFQ").Range("Desc1") & "_" & ThisWorkbook.Sheets("RFQ").Range("Desc2")
'Continue as desired


.Item("PathsSet") = "TRUE"
End With
End Sub

Sub NewRevision()
'Run whenever a new version of the estimate is created
Dim Oldersion As Long
Dim NewVersion As String
OldVersion = CLong(ThisWorkbook.CustomDocumentProperties("RevisionNumber").Value)
NewVersion = "0" & CStr(OldVersion + 1)
ThisWorkbook.CustomDocumentProperties("RevisionNumber") = Right(NewVersion, 2)
End Sub




To use this in your other code, precede the Sub Call with the module name and a Dot. Ex Module1.InitNewCustomProperties
Module1.InitWorkbookPaths

[SamT]

You might find the BuiltIn Document Properties heloful. At the least, don;t use these names as Custom Document Properties.
From the help file:
BuiltinDocumentProperties Property See Also Applies To Example (http://www.vbaexpress.com/forum/#example) Specifics
Returns a DocumentProperties (mk:@MSITStore:vbaof10.chm::/html/ofobjDocumentProperties.htm) collection that represents all the built-in document properties for the specified workbook. Read-only.
Remarks This property returns the entire collection of built-in document properties. Use the Item method to return a single member of the collection (a DocumentProperty object) by specifying either the name of the property or the collection index (as a number).
You can refer to document properties either by index value or by name. The following list shows the available built-in document property names:


Title Subject
Author
Keywords
Comments
Template
Last Author
Revision Number
Application Name
Last Print Date

Creation Date Last Save Time
Total Editing Time
Number of Pages
Number of Words
Number of Characters
Security
Category
Format
Manager

Company Number of Bytes
Number of Lines
Number of Paragraphs
Number of Slides
Number of Notes
Number of Hidden Slides
Number of Multimedia Clips
Hyperlink Base
Number of Characters (with spaces)




Container applications aren’t required to define values for every built-in document property. If Microsoft Excel doesn’t define a value for one of the built-in document properties, reading the Value property for that document property causes an error.
Because the Item method is the default method for the DocumentProperties collection, the following statements are identical:
BuiltinDocumentProperties.Item(1)
BuiltinDocumentProperties(1) Use the CustomDocumentProperties (http://www.vbaexpress.com/forum/xlproCustomDocumentProperties.htm) property to return the collection of custom document properties.
Example This example displays the names of the built-in document properties as a list on worksheet one.


rw = 1
Worksheets(1).Activate
For Each p In ActiveWorkbook.BuiltinDocumentProperties
Cells(rw, 1).Value = p.Name
rw = rw + 1
NextSamT Note: You can use the Syntax
ThisWorkbook.BuiltinDocumentProperties("Title") = "Estimate 1"
and MyVar = ThisWorkbook.BuiltinDocumentProperties("Title")

[Steve0]

I think I understand what you are doing. (everytime I think I am getting a grasp on VBA, I see some snippet of code that makes my brain explode all over again. The spreadsheet I have created is more complex than my skillset should allow, and it takes problems like this to remind me I still have no idea what I am doing.... lol)

Let me play with this today in my down time. I think I can make this work. Thank you so much for the quick and detailed response. +1 rep!