File Save Sequance Excel 2007

[mc16v]

Hi everyone

I have recently upgrading from Excel 2000 to 2007 and a File Save As macro nolonger works and I dont understand why, can you help! The macro should search the save folder and the save the current file as the next number in the sequance eg "Week 01" then "Week 02" etc

The problem line I have highlighted in red

Thanks very much in advance
mc16v



Sub SaveAs()

Dim strDirectory As String
Dim strBaseName As String
Dim strLastNumber As String
Dim intLastNumber As Integer
Dim strLastFile As String
Dim strNewFile As String

Application.CutCopyMode = False
ActiveWorkbook.Save

'set the base directory and name for your files
strDirectory = "C:\Users\User\Documents\#####\#####\"
strBaseName = "Week" & " "

With Application.FileSearch
.LookIn = strDirectory
.FileType = msoFileTypeExcelWorkbooks
.Filename = strBaseName & "*.xls"
If .Execute = 0 Then
strLastFile = strDirectory & strBaseName & "01.xls"
Else
strLastFile = .FoundFiles(.FoundFiles.Count)
End If
End With

strLastNumber = Mid(strLastFile, Len(strLastFile) - 6, 3)
intLastNumber = CInt(strLastNumber)
strNewFile = strDirectory & strBaseName & Format(intLastNumber + 1, "00") & ".xls"


ActiveWorkbook.SaveAs (strNewFile)


End Sub

 

[VoG]

See if post #2 here http://www.mrexcel.com/forum/showthread.php?t=574190 helps.

 

[mc16v]

Firstly, Thanks for the quick reply,

reading that post it seems to be a "look for" macro? I have limited abilities with macros and I cant work out how to fit it in to perform the "Save As" command I am looking for.

Really appriciate your help.