BeforeSave problem

mikeymay

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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

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,490
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,490
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
 

Forum statistics

Threads
1,141,427
Messages
5,706,372
Members
421,445
Latest member
blueman0110

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top