Steve0
06-05-2018, 01:15 PM
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.
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.