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]

Shefield UK isnt that the thriving industrial city where The Full Monty was based? Great movie that one.

 

[Helper monkey]

Yep, thats the one... and it is a very funny film.

The canals, floating cars and unemployment are all true!

Not to be too negative - there are some very nice bits too...

 

[Helper monkey]

Hi,

I posted this message a while ago, and thanks to vodoo's help got the code below, but have only recenlty had the opportunity to use it in anger... and ran into trouble.

Instead of taking in the info from range B2 the file is simply saved as "1". The code is also surposed to check if a file already exists and add an extra 1 to the "i" which it doesn't do.

Could anyone explain where i'm going wrong?


Private Sub CommandButton4_Click()

If CheckBox1.Value = True Then
Worksheets("Sheet 1").PrintOut Copies:=1, Collate:=True

Dim strFileName As String
Dim i As Integer

Sheets("Sheet 1").Select
Sheets("Sheet 1").Copy

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


Else


Thanks very much!

Helper Monkey - Still in Full Monty land!

 

[Richie(UK)]

Hi Helper Monkey,

Shouldn't it be:

strFileName = Range(" b2").value & i

See if that helps. (Remember to change both lines concerned).

 

[Helper monkey]

Hi Richie,

Already given that a shot but it still saves the file as "1" rather than Range("b2").value (or text - i tried both) & i

Thnaks for your help though!

Helper monkey

 

[Richie(UK)]

Hi,

The following works for me.<pre>
'filename test
Sub filename()
Dim strFileName As String
Dim i As Integer

Sheets("Sheet1").Copy

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

End Sub</pre>


The "b2" reference in your earlier post was shown as " b2", ie with a leading space, maybe this was the problem.

Remember that there should only be one backslash after the C: drive - the board gets a little carried away sometimes! :wink:

HTH

_________________<font color="blue"> «««<font color="red">¤<font color="blue"><font size=+1>Richie</font><font color="red">¤<font color="blue"> »»»</font>

</gif>
This message was edited by Richie(UK) on 2002-09-03 03:12

 

[Helper monkey]

Hi Richie,

I've tried the code that you suggested but it still doesn't work properley...

The code doesn't appear to add one to "i" and therefore keeps on asking/telling me to replaced the existing file...

Where am i going wrong? I'm not sure whether it makes any differnce but i'm running excel 97

Thanks again!

 

[Richie(UK)]

Sorry mate - didn't check the loop part. Doh!

Anyway, needless to say it didn't work. Try this instead.

<pre>
'filename test
Sub filename()
Dim strFileName As String
Dim i As Integer

Sheets("Sheet1").Copy

Start:
i = i + 1
strFileName = Range("b2").Value & i

With Application.FileSearch
.LookIn = "C:"
.filename = strFileName & ".xls"
If .Execute > 0 Then 'Workbook exists
GoTo Start
Else 'Workbook doesn't exist so do save
ActiveWorkbook.SaveAs "C:" & strFileName
ActiveWorkbook.Close
End If
End With

End Sub
</pre>

HTH

 

[Helper monkey]

Hi Richie,

That works a treat, cheers mate