Advice on a potencial error when saving...

[Helper monkey]

Hi,

I've got a potencial dilema. I have a macro that saves a copy of a worksheet (if a check box is checked on exit). As i am leaving the macro in the hands of people who's IT skills is at best limited and have less VB experiance than me i can forsee a disaster on the horizon. I'm running excel 97 and if prompted to save using the recommomended file type i will loose all the userforms (and a lot of time and effort).

The macro saves a copy of the worksheet with the date and text from two textboxes as the title. Obviously if the file already exists there's going to be a clash. I do not want to replace the existing file but save it with a slighly different name (eg add a number - file name would be blabladate2.xls) or have a userform pop up asking the user to enter an alternate name. Could i attach this to an error line of code? i.e. if error then...

I could do with some advice on this problem and am running out of time to find a solution.

Thanks in advance!

 

[voodoo]

You could check whether a similar named file exists in the particular directory and if so change the name.

e.g.

Lets assume your files are all held in C:

dim strFileName as String
dim i As Integer

i = 1
strFileName = "XYZ" & i
' check if file exists on C:
If (Dir$("c:" & strFilename) <> "") Then
i = i + 1
End if

ActiveWorkbook.SaveAs "C:" & strFileName



You might have to add a do while loop to keep adding numbers if this is used often and then file numbers keep growing.

Probably a better way but taht might get you started

Vodoo

 

[voodoo]

oops forgot to change the file name if it does exist

dim strFileName as String
dim i As Integer

i = 1
strFileName = "XYZ" & i
' check if file exists on C:\
If (Dir$("c:\" & strFilename) <> "") Then
i = i + 1
strFileName = "XYZ" & i
End if

ActiveWorkbook.SaveAs "C:\" & strFileName

 

[Helper monkey]

Thanks for the help!

 

[Helper monkey]

Hi,

I gave Vodoos suggestion a shot yesterday, using the code below, and the file is now saved with a "1" on the end. However, if the file already exists the "1" doesn't change to a "2" etc etc. What am i doing wrong?

Thanks in advance


Dim strFileName As String
Dim i As Integer

i = 1
strFileName = Range(" b1") & i
If (Dir$("C:\" & strFileName) <> "") Then
i = i + 1
strFileName = Range(" b1") & i
End If
ActiveWorkbook.SaveAs "C:\" & strFileName

 

[voodoo]

Try this Ive just added a do while loop instead of the If statement

Dim strFileName As String
Dim i As Integer

i = 1
strFileName = Range(" b1") & i
Do While (Dir$("C:\\" & strFileName) <> "")
i = i + 1
Loop
strFileName = Range(" b1") & i
End If
ActiveWorkbook.SaveAs "C:\\" & strFileName

 

[Helper monkey]

Thanks Vodoo,

Your help is very much appriciated - what time is it down in Australia?

 

[voodoo]

oops let me try that again.

Dim strFileName As String
Dim i As Integer

i = 1
strFileName = Range(" b1") & i
Do While (Dir$("C:\" & strFileName) <> "")
i = i + 1
strFileName = Range(" b1") & i
Loop
ActiveWorkbook.SaveAs "C:\" & strFileName

I have not actually tried this but the theory is it checks if the FileName1 exists, if so it checks FileName2, if so FileName3 right up until FileNameN, where N becomes the number of the new file.

 

[voodoo]

Your welcome HP, I think that will work now.

I believe it is 6:44pm in Oz but Im not 100% sure because actually I working in Japan.

Cheers

voodoo

 

[Helper monkey]

Blimy...

Sounds a little better than Sheffield UK!

Thanks again!