File Exist

[jnf40]

The following code creates the file for the workbook. I need to be able to
disable a form button if the person has a file open, lets say #4 and file #5
exists I do not want them to be able to create another workbook from file #4
they would have to open #5.
myParentFolder = "C:\"
On Error Resume Next
MkDir myParentFolder & mycsj
MkDir myParentFolder & mycsj & "\Pay Reports"
MkDir myParentFolder & mycsj & "\Pay Reports" & "\" &
Range("name")
On Error GoTo 0
myFolder = myParentFolder & mycsj & "\Pay Reports" & "\" &
Range("name")
i = 1
myFileName = Range("file")
While Dir(myFolder & "\" & myFileName & " " & i & ".xls") <> ""
i = i + 1
Wend
myFileName = myFolder & "\" & myFileName & " " & i & ".xls"
ActiveWorkbook.SaveAs Filename:= _
myFileName, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
MsgBox "File Saved to " & myFileName
End If

 

[Joel]

his code will find all the open workbooks

Sub test()

For Each wkbk In Workbooks
workbookname = wkbk.Name
If workbookname = "file 5" Then

'enter your code here
End If

Next wkbk
End Sub

 

[jnf40]

Thanks, but I need to know if the workbook name that is open is the the last
one in the file or if it is one of the earlier ones. For instance if I have 5
workbooks in the folder I need to know if the workbook is Myfile 4 then I
need to disable a button on the userform that allows them to create another
workbook whereas if Myfile 5 is open then the button on the userform will be
enabled so they create a new workbook.

 

[Joel]

Sub test()

For Each wkbk In Workbooks
select case wkbk.Name
case "File A"
commandcontrol1.disable
case "File B"
commandcontrol3.disable
case "File C"
commandcontrol3.disable
case "File D"
commandcontrol4.disable
case "File E"
commandcontrol5.disable
end select
Next wkbk
End Sub

 

[jnf40]

I understand what you are saying about the case "File A" and so on but what I
need is it to somehow check for the file using the iCtr or some other means
because the file names start with CSB 1257 Reports 1 and can go up from there
however many workbooks they create.

 

[Joel]

I don't know why you are so concerned about the file names. My method works
with any valid filename and will work with any number of opened files.

Sub test()

For Each wkbk In Workbooks
'remove xls from filename
RawName = left(wkbk.name,instr(wkbk.name,".")-1)
select case RawName
case "CSB 1257 Reports 1"
commandcontrol1.disable
case "CSB 1257 Reports 2"
commandcontrol3.disable
case "CSB 1257 Reports 3"
commandcontrol3.disable
case "CSB 1257 Reports 4"
commandcontrol4.disable
case "CSB 1257 Reports 5"
commandcontrol5.disable
end select
Next wkbk
End Sub

 

[jnf40]

Well I haven't tried your code but my thinking is lets say there are 15
workbooks in the same folder and they open CSB 1257 Reports 12, CSB 1257
Reports 15 has not been opened and I don't want them to create another
workbook using the info in workbook CSB 1257 Reports 12, they would have to
open CSB 1257 Reports 15 to create another. I need some way to disable all of
the Commandbutton1 in all the workbooks 1 through 14 if they open them and
report 15 is still unopened.

 

[Joel]

I have thought about your problem a lot. Diasabling the button is a good
idea but it is not fool-proof. Somebody on another PC could open the file
and you would not cause the button to be disabled.

You need to assign a unique filename to each button. Button will only be
enabled when the filename corresponding to the button doesn't exist.

The fool-proof method is when the button is clicked to check the files in
the folder to make sure the file doesn't exist and create a new file
immediately so two people aren't working with temporary files with the same
name.

You can use a workbook open function to check if the files exist in the
folder and disable the buttons for files that already exist when the workbook
is opened.

If you need to keep track of the number of opened files you can add a count
to my code to count the files.

 

[jnf40]

here is some of what I have

Private Sub Workbook_Open()
UnhideSheets
End Sub

Private Sub UnhideSheets()
Dim sht As Object
Dim f As Worksheet
Application.ScreenUpdating = False
ActiveWorkbook.Unprotect Password:="csb"
If ThisWorkbook.Sheets.Count = 15 Then
Call Module1.macro3
ElseIf ThisWorkbook.Sheets.Count > 5 Then
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
If sht.Name <> Sheets("Macros").Name And sht.Name <> Sheets("CSB
Form 12572").Name _
And sht.Name <> Sheets("Create Pay Report").Name And sht.Name <>
Sheets("CSB Form 1257").Name _
And sht.Name <> Sheets("Manhole Inlet").Name Then
sht.CommandButton2.Enabled = True
If sht.CommandButton1.Visible = True Then
sht.CommandButton1.Enabled = True
End If
End If
sht.Protect Password:="csb", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
sht.EnableSelection = xlUnlockedCells
Next sht
For Each f In ActiveWorkbook.Worksheets
If f.Name = Sheets("Create Pay Report").Range("wksname") Then
f.Select
Exit For
End If
Next f
ThisWorkbook.Sheets("Create Pay Report").Visible = False
ThisWorkbook.Sheets("Macros").Visible = False
ThisWorkbook.Sheets("CSB Form 1257").Visible = False
ThisWorkbook.Sheets("CSB Form 12572").Visible = False
ThisWorkbook.Sheets("Manhole Inlet").Visible = False
ElseIf ThisWorkbook.Sheets.Count = 5 Then
For Each sht In ThisWorkbook.Sheets
sht.Visible = xlSheetVisible
sht.Protect Password:="csb"
Next sht
ThisWorkbook.Sheets("Macros").Visible = False
ThisWorkbook.Sheets("CSB Form 1257").Visible = False
ThisWorkbook.Sheets("CSB Form 12572").Visible = False
ThisWorkbook.Sheets("CSB Form 12572").Visible = False
ThisWorkbook.Sheets("Manhole Inlet").Visible = False
ActiveSheet.Cells(19, 26).Select
ActiveSheet.Protect Password:="csb", _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
UserInterfaceOnly:=True
ActiveSheet.EnableSelection = xlUnlockedCells
End If
Application.ScreenUpdating = True
ThisWorkbook.Protect Password:="csb"
End Sub

Module1 Macro3 loads a userform which has the commandbutton1 on it I am
trying to disable. This would only occur if they open a workbook that was
earlier than the latest one created.