In the Creat_MyFile sub I try to find a file called
2003.xls . If it exists it only opens it and creates a
new worksheet. If it does not exist it is created and a
new worksheet made.

I think my problem is that I am not using the right way
to find my file. Can you please help me out? I would
really appreciate it.

Thank you.

Private Sub CommandButton1_Click()
Dim MiMyDir As String
MiMyDir = "c:\Prices\"
If Dir(MiMyDir, vbDirectory) < "" Then
Create_MyFile MiMyDir
Else
MkDir MiMyDir
Create_MyFile MiMyDir
End If
End Sub

Sub Create_MyFile(MyDir As String)
Dim MiMyFile As String
Dim MyDate, MyYear
MyDate = Now
MiMyFile = Year(MyDate) & ".xls"
If Dir(MiMyFile, vbDirectory) < "" Then
Open_MyFile
Else
Application.Workbooks.Add
ActiveWorkbook.SaveAs MyDir & MiMyFile
ActiveWorkbook.Close
Open_MyFile
End If
End Sub
Sub Open_MyFile()
Dim MyDate
MyDate = Now
Application.Workbooks.Open ("C:\Prices\" & Year
(MyDate) & ".xls")
Dim ShName As String
Dim nChr As Integer
ShName = CStr(Date)
' Replace /
For nChr = 1 To Len(ShName)
If Mid(ShName, nChr, 1) = "/" Then Mid(ShName,
nChr, 1) = "-"
Next
Worksheets.Add(After:=Worksheets
(Worksheets.Count)).Name = ShName
End Sub

Here is a sub I use to open a workbook or activate if already open.
Sub GetWorkbook()
If ActiveCell.Value = "" Then Exit Sub
workbookname = ActiveCell.Value
On Error GoTo OpenWorkbook
Windows("" & workbookname & ".xls").Activate
Exit Sub
OpenWorkbook:
Workbooks.Open(workbookname & ".xls").RunAutoMacros xlAutoOpen
'Workbooks.Open("" & workbookname & ".xls").RunAutoMacros xlAutoOpen
Exit Sub
End Sub
====
Here is a sub I use to create a backup file in the current directory. If the
file exists the error handler takes over.

Sub Backup() 'kept in personal.xls & assigned to toolbar button
On Error GoTo BackupFile
MkDir CurDir & "\Backup"
BackupFile:
With ActiveWorkbook
MyWB = .Path & "\BACKUP\" & .Name
.SaveCopyAs MyWB
.Save
End With
End Sub


"Poseilus" wrote in message
...
In the Creat_MyFile sub I try to find a file called
2003.xls . If it exists it only opens it and creates a
new worksheet. If it does not exist it is created and a
new worksheet made.

I think my problem is that I am not using the right way
to find my file. Can you please help me out? I would
really appreciate it.

Thank you.

Private Sub CommandButton1_Click()
Dim MiMyDir As String
MiMyDir = "c:\Prices\"
If Dir(MiMyDir, vbDirectory) < "" Then
Create_MyFile MiMyDir
Else
MkDir MiMyDir
Create_MyFile MiMyDir
End If
End Sub

Sub Create_MyFile(MyDir As String)
Dim MiMyFile As String
Dim MyDate, MyYear
MyDate = Now
MiMyFile = Year(MyDate) & ".xls"
If Dir(MiMyFile, vbDirectory) < "" Then
Open_MyFile
Else
Application.Workbooks.Add
ActiveWorkbook.SaveAs MyDir & MiMyFile
ActiveWorkbook.Close
Open_MyFile
End If
End Sub
Sub Open_MyFile()
Dim MyDate
MyDate = Now
Application.Workbooks.Open ("C:\Prices\" & Year
(MyDate) & ".xls")
Dim ShName As String
Dim nChr As Integer
ShName = CStr(Date)
' Replace /
For nChr = 1 To Len(ShName)
If Mid(ShName, nChr, 1) = "/" Then Mid(ShName,
nChr, 1) = "-"
Next
Worksheets.Add(After:=Worksheets
(Worksheets.Count)).Name = ShName
End Sub