BeforeSave problem

[mikeymay]

I have the following code behind 'This Workbook'.

My problem is that the check it is running returns true as the file is still called 'Q0 Template' so it won't let me save as another file.

the code that fires this off is

wbTemplate.SaveAs strYearFolder & "\" & strForecast & " " & strPeriod

Anyone know how to resolve this?


Thanks

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

If ActiveWorkbook.Name = "Q0 Template.xls" And Worksheets.Count > 9 Then
    MsgBox ("You are attempting to save this file as the Template workbook." & vbCr & vbCr & _
        "If this file contains data then save the file with another name," & vbCr & _
        "otherwise close this file without saving."), vbExclamation
    Cancel = True
    Exit Sub
    Else
End If
    
End Sub

 

[ktab]

wbTemplate.SaveAs strYearFolder & "\" & strForecast & " " & strPeriod

Try to rename a file and place "\" to name. You'll see that you cannot use some symbols to file names. Replace that with let's say "-".

 

[mikeymay]

strYearFolder represents the path and strForecast & " " & strPeriod represent the file name I want to use.

 

[ktab]

If strYearFolder does not inclue at the end "\" that could mess up code, then the problem is at the strings strForecast or strPeriod. Maybe if rest of code posted could help more

 

[mikeymay]

To be honest, I think this part of the process is not necessary. I also have a deadline of this afternoon which I don't want to miss.

Thanks for your help anyway.

 

[ktab]

Just to be sure add this code before the saveas command.
This will create folder if folder does not exists
You need to goto tools->references-> and check microsoft scripting runtime (I guess you already did)

Dim fsoObj As Scripting.FileSystemObject
strYearFolder = 'Your path here
If Not fsoObj.FolderExists(strYearFolder) Then fsoObj.CreateFolder (strYearFolder)


at the end:
Set fsoObj = Nothing
end sub