# Save as macro with no overwrite



## Erwinvandod (Sep 1, 2011)

So I got 1 more issue I'm stuck with at the moment. Now I've made myself a save macro which automatically makes new maps if the map doesn't exist yet and then saves the Excel file with the name from some cells as a .pdf and opens it right after its saved. Now my problem is that it automatically saves over the old file if it has the exact same file name.

Now I tried to deal with this by putting 
Application.DisplayAlerts=False and Application.DisplayAlerts=True somewhere in it but that doesn't seem to work.

This is my macro so far 



> Sub SaveBunkercheclist()
> Dim strNewFolderName As String
> 
> strNewFolderName = "Herman Senior"
> ...


If there is someone that could help me it would be greatly appreciated once again 

Thanks Erwin


----------



## Glaswegian (Sep 16, 2005)

Hi

A quick thought would be to test the filename before saving. Do you mean it overwrites an existing file or the file running the code?


----------



## Erwinvandod (Sep 1, 2011)

It overwrites a file which has been saved already with the same name. I want to prevent this to get mistakes maken.


----------



## Glaswegian (Sep 16, 2005)

Hi Erwin

Try this to test if the filename already exists

```
opslag = myndir & boot & " " & naam & " " & datum & ""
If Dir("C:\Herman Senior safety\Bunker checklist\") = opslag Then
    MsgBox "File Name already exists!", vbInformation + vbOKOnly, "Duplicate File Found"
        Exit Sub
End If
```
This is untested so may need some amendment for language and file path etc.


----------



## Erwinvandod (Sep 1, 2011)

Hey Glaswegian,

Tried it in several languages and on different places but it doesn't want to work still getting the same effect still automatic overwriting without a warning.


----------



## Glaswegian (Sep 16, 2005)

Hi Erwin

What version of Excel are you using?

There is a Filesearch object we can use if you have Excel 2003 or earlier. If not then we would need to use a FileExists method.


----------



## Erwinvandod (Sep 1, 2011)

Hey Glas,

I'm using office 2007, I could post a small piece of the file if this makes it easier to solve.


----------



## Glaswegian (Sep 16, 2005)

Yes, that would be most helpful - thanks.


----------



## Erwinvandod (Sep 1, 2011)

Glas,

Here is a part of my file I had to put it on another site since I could not upload it somehow through the system in the forum.
Safety data online.xlsm downloaden

Kind regards


----------



## Glaswegian (Sep 16, 2005)

Hi Erwin

The VBA is password protected but that's OK.

I think it's best to use a separate function to test if the filename already exists

```
Private Function FileExists(fname) As Boolean
'Returns TRUE if the file exists
Dim x As String
x = Dir(fname)
If x <> "" Then FileExists = True Else FileExists = False
End Function
```
Then you just need to add a couple of lines like this as part of your main routine

```
'previous code here - check the filename BEFORE trying to save
If FileExists(fname) = True Then
     MsgBox "File name already in use", vbInformation + vbOKOnly, "File Name Check"
Else
     'run the rest of your code here to save the file
End If
     'and so on...
```
You can change the name of the variable in the Function to match your own variable or change your variable name to match the function - whichever suits you best.


----------



## Erwinvandod (Sep 1, 2011)

Hey Glaswegian yeah forgot to take the security of it got a new file posted without protection.

Safety data online.xlsm downloaden


----------



## Erwinvandod (Sep 1, 2011)

Glas,

I tried the option you posted but that won't do the trick. With the following code it does sort of work but not fully:

Sub SaveFire()
Dim strNewFolderName As String
strNewFolderName = "Herman Senior"
If Len(Dir("c:\Herman Senior safety", vbDirectory)) = 0 Then
MkDir ("c:\Herman Senior safety")
End If
If Len(Dir("c:\Herman Senior safety\Drills", vbDirectory)) = 0 Then
MkDir ("c:\Herman Senior safety\Drills")
End If
If Len(Dir("c:\Herman Senior safety\Drills\Fire drills", vbDirectory)) = 0 Then
MkDir ("c:\Herman Senior safety\Drills\Fire drills")
End If


Dim datum, naam, boot, myndir As String

myndir = "C:\Herman Senior safety\Drills\Fire drills\"
If Len(myndir) > 0 Then
Msg = "File already exists, do you want to continue?" 
Style = vbYesNo + vbCritical + vbDefaultButton2 
Title = "Warning" 

Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then 
Exit Sub 
Else 
End If
End If
boot = Range("C7").Value
naam = Range("A5").Value
datum = Range("c9").Value
opslag = myndir & boot & " " & naam & " " & datum & ""
MsgBox opslag
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=opslag, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

The problem with this macro is that it does look for existing files but it fails because it detects the folders that the macro is making that they are already there, and so every time asked if I want to save because the folders are already there. 

The problem lies with the variable saving method I guess because it is saving the file according to date, name ship and name drill.


----------



## Glaswegian (Sep 16, 2005)

Hi

The FileExists function should be in a Module on its own.

This line

```
If FileExists(fname) = True Then
```
should be placed just before the Save command in your code. It looks to me like your variable *opslag* will be the one that holds the new file path and name. If that's correct, simply change *fname* in the Function and in the line of code above to *opslag*. The code should be placed just after MsgBox opslag. Then the result of the Function should determine if the file is saved or you receive a message saying the file name is already in use.


----------



## Erwinvandod (Sep 1, 2011)

Sorry for being a noob atm but from that code I don't really get it because you say it has to be a separate module but then it doesn't work for me. I'm prob doing something wrong :sigh:


Safety data online.xlsm downloaden


----------



## Glaswegian (Sep 16, 2005)

Hi Erwin

Amended workbook attached.

I had to make some changes to your code.

The FileExists Function should be on its own - separate from your main macro. In your macro Excel does not like the date in the format dd/mm/yyy - backslashes are generally not allowed in a file name, so I changed that using Format. I also added the .xlsx extension as part of the filename.

```
datum = Format(Range("c9").Value, "dd mm yyyy")
opslag = myndir & boot & " " & naam & " " & datum & ".xlsx"
```
Then we just test the variable *opslag*

```
If FileExists(opslag) = True Then
      MsgBox " File name already in use", vbInformation + vbOKOnly, "File name check"
      Else
        MsgBox opslag
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=opslag, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End If
```
which calls the FileExists Function and passes the variable *opslag* to that function.

This works perfectly for me in testing.


----------



## Erwinvandod (Sep 1, 2011)

Hey Glas,

Thanks for the amended file although somehow It does not work for me, it saves it perfectly but it doesn't give me the error window if the file already exists.

Erwin


----------



## Glaswegian (Sep 16, 2005)

Hi Erwin

It does seem to work for me - have you tried testing with a dummy workbook already saved in the target folder? That's what I did and when I ran your code it said the file name was already in use.

I'll try and have a look at it over the weekend.


----------



## Glaswegian (Sep 16, 2005)

Hi Erwin

It still works fine for me so I've asked one of my colleagues here to have a look and see if he can see anything that could be causing a problem.


----------



## Erwinvandod (Sep 1, 2011)

Hey Glas, 

Sorry for the late reply but I had a long weekend away. Really appreciate that what your doing awaiting your next post.

Kknd regards


----------



## Glaswegian (Sep 16, 2005)

Hi Erwin

One of my colleagues tried the amended workbook I attached earlier and he said it worked just fine.

I'm not sure what's happening - is there anything you can think of that may help us understand why it does not work for you?


----------

