Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Exist
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Exist
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Exist
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Exist
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" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Exist
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Exist
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Exist
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Exist
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
File Exist
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. "Joel" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
does file exist | Excel Programming | |||
Command Line. How to tell to XL : If the xls file exist : Open it, if it does not exist : Create it. | Excel Programming | |||
Does File Exist | Excel Programming | |||
File does not exist | Excel Programming | |||
does a file exist? | Excel Programming |