Check for file before saving

[Striker]

I use the following code to save a file with the current date in the name.
ONly problem, on occasion there is a file name there with that name, and I
am not doing any checking to make sure the file does not exist before
saving. What is the best way to check if the name exists before saving.

Thanks

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sub Import_txt_File()
Dim sLineOfText As String
Dim rRange As Range
Dim sPath As String
Dim iInt As Integer

'Set cursor for visual indication that application is busy.
Application.Cursor = xlWait

iInt = 0

On Error GoTo ErrHandler

'Set the path for saving the file later in the SUB.
sPath = "K:\Data\COS" + "\" + "COS " + "Data Import Date " + Date$

'set the variables
Set rRange = Range("A2")

'Open text data file
Open "C:\Test.txt" For Input As #1

Worksheets("Import").Activate

'Extract the data I need from the file.
Do Until EOF(1)
Line Input #1, sLineOfText
rRange.Offset(0, 0) = Trim(Mid(sLineOfText, 63, 35)) 'FirstName
rRange.Offset(0, 1) = Trim(Mid(sLineOfText, 98, 20)) 'LastName
rRange.Offset(0, 2) = Trim(Mid(sLineOfText, 179, 30)) 'Address
rRange.Offset(0, 3) = Trim(Mid(sLineOfText, 209, 20)) 'City
rRange.Offset(0, 4) = Trim(Mid(sLineOfText, 229, 2)) 'State
rRange.Offset(0, 5) = Trim(Mid(sLineOfText, 231, 5)) 'Zip
rRange.Offset(0, 6) = Trim(Mid(sLineOfText, 11, 7)) 'Reference #
'rRange.Offset(0, 7) = Trim(Mid(sLineOfText, 738, 9)) 'Phone1 #
'rRange.Offset(0, 8) = Trim(Mid(sLineOfText, 748, 9)) 'Phone2 #

'move down one row
Set rRange = rRange.Offset(1, 0)

'increment iInt
iInt = iInt + 1
Loop
Close #1

'Return cursor to normal.
Application.Cursor = xlDefault

'Let the user know how many records were imported.
MsgBox "You Imported " & iInt & " Records, Press OK to save this file
with the current Date.", vbInformation

'Save the active sheet to a new file name.
ActiveWorkbook.SaveAs Filename:=sPath

Exit Sub

ErrHandler:
MsgBox "Unexpected error. Type " & Err.Description
Resume Next

End Sub

 

[Guest]

Hi Striker,

Once you've constructed your path/name, slot the following in:

***
Set fs = CreateObject("Scripting.FileSystemObject")
If fs.FileExists(FULLFILENAME) Then
' Get it to do what you want it to do when it finds a file with this
name.
End If
***

where FULLFILENAME is replaced by the path and filename for the workbook
you're looking for.

This is one I snagged a while ago from one of the helpful chaps on here,
believe it was probably My Phillips or Ogilvy.

HTH
DS