save as macro for same file name

[yshridhar]

Hello everybody
I wrote a macro that save the workbook as with the given name. I am
encoutering a problem, if the file is already exists it is asking to
overwrite. in this case what i want is to warn the user as the file already
exists and prompt to enter new name to save as.

The following is the code

Sub itr12()

Dim fpath As String
Dim fname As String

fpath = ActiveWorkbook.Path
fname = Sheets("data").Range("b3")

On Error Resume Next
MkDir fpath & "\" & Format(Now, "dd-mmm")
ActiveWorkbook.SaveAs (ActiveWorkbook.Path & "\" & Format(Now, "dd-mmm")
& "\" & fname & ".XLS")

End Sub
The file containing macro is stored in the "fpath" directory.
i am using off-203
any suggestions
thanks in advance
With best regards
sreedhar

 

[Barb Reinhardt]

You could try something like this. Note that this requires a reference to
"Microsoft Scripting Runtime". In your VBE, go to TOOLS -> References and
select the reference.

I'm finding that I need to go through code that forces a save to .xls to
allow for other extensions for Office 2007 because we're in the middle of a
migration. Keep that in mind if/when you convert.

Sub itr12()

Dim fpath As String
Dim fname As String
Dim folderpath As String
Dim filepath As String

fpath = ActiveWorkbook.Path
If fpath = "" Then
MsgBox ("Save Workbook and try again")
Exit Sub
End If

fname = Sheets("data").Range("b3")
folderpath = fpath & "\" & Format(Now, "dd-mmm")
If Not myFolderExists(folderpath) Then
MkDir (folderpath)
End If

filepath = folderpath & "\" & Format(Now, "dd-mmm") & ".xls"
If Not myFileExists(filepath) Then
ActiveWorkbook.SaveAs (filepath)
End If
End Sub

Function myFileExists(myPath As String) as string
'Requires Reference to Microsoft Scripting Runtime
Dim FSO As FileSystemObject
myFileExists = False
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(NewDir) Then
myFileExists = True
End If
End Function

Function myFolderExists(myFolderPath As String) as string
'Requires Reference to Microsoft Scripting Runtime
Dim FSO As FileSystemObject
Set FSO = CreateObject("Scripting.FileSystemObject")
myFolderExists = False
If FSO.FolderExists(myFolderPath) Then
myFolderExists = True
End If
End Function

 

[Barb Reinhardt]

I've corrected the CODE. I must be brain dead this morning. For the
Functions, change it from

Function .... ( ... as String) as String to
Function .... ( ... as string) as BOOLEAN

I added the last "as string" once I copied it to this thread.

Sorry about any confusion.

Barb Reinhardt

 

[yshridhar]

Barb you suggestion solved my problem. Thanks alot
warm regards
sreedhar