BeforeSave problem

mikeymay

Well-known Member
Joined
Jan 17, 2006
Messages
1,467
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
Code:
wbTemplate.SaveAs strYearFolder & "\" & strForecast & " " & strPeriod
Anyone know how to resolve this?


Thanks
Code:
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
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

Well-known Member
Joined
Jan 17, 2006
Messages
1,467
strYearFolder represents the path and strForecast & " " & strPeriod represent the file name I want to use.
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
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

Well-known Member
Joined
Jan 17, 2006
Messages
1,467
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

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
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
 

Watch MrExcel Video

Forum statistics

Threads
1,112,877
Messages
5,543,002
Members
410,583
Latest member
gazz57
Top