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 Specifics
Returns a DocumentProperties 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 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
Next

SamT 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!