RP or TO - Add all files EXCEPT active file?
 

Hi Tom or Bob,

I added the If LCase(Filename) < LCase(ThisWorkbook.Filename) Then
line of code like you suggested, but got an error Method or Data member not
found. So I then tried top replace Filename with Fullname. It did not
error, but gave the same results as when I did not have the line of code in.
Did I put the line in the wrong place? The sub and function are below.
Thanks!

Sub ProcessFiles()
Dim FSO As Object
Dim fldr As Object
Dim sFolder As String
Dim Folder As Object


Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "S:\Global_Share\Operations\Kineticom Timesheet\Approvals\" &
Range("R5").Value

If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
If FileCountOK(Folder) Then
CDO_Email_GM
End If
End If ' sFolder < ""

End Sub

Function FileCountOK(pzFolder As Object)
Dim i As Long
Dim file As Object
Dim Files As Object

FileCountOK = True
Set Files = pzFolder.Files
For Each file In Files
If LCase(Filename) < LCase(ThisWorkbook.Filename) Then
If file.Type = "Microsoft Excel Worksheet" Then
i = i + 1
Workbooks.Open Filename:=file.path
With ActiveWorkbook
FileCountOK = .ActiveSheet.Range("A2").Value = 1
.Close savechanges:=False
If Not FileCountOK Then Exit Function
End With
End If
End If
Next file

End Function
"Steph" wrote in message
...
Thanks Tom, I'll give it a shot.

"Tom Ogilvy" wrote in message
...
When you opening files put in code like

if lcase(filename) < lcase(thisWorkbook.filename) then

end if

or if filename is a fully qualified filename like
"C:\Myfolder\myfile.xls"
then use

if lcase(filename) < lcase(thisworkbook.fullname) then


End if
--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Bob - I hate to ask this - but I ran into hopefully a small problem. I
think I'm screwing myself up because the click event that calls your
ProcessFiles sub (and therefore your FileCountOK function) is in a file
WITHIN the same folder the function is checking. So the function wants
to
check all files including the one thats open. So when I debug and turn
displayalerts back to true, excel prompts with "file already open,
opening
new will discard changes". I tried to add a few save commands
throughout,
but I still get eratic results.

So as to not confuse you too much with too much detail as to what the
heck
I'm doing, is there a way to check all files within the folder EXCEPT
the
current file? Assume the current file has a 1 in cell A1 (the click
event
calls your sub and also puts a 1 in cell A1).

RP or TO - Add all files EXCEPT active file?
 

Never mind guys....I figured it out. It needed to be
If LCase(file) < LCase(ThisWorkbook.FullName) Then
Thanks for all your help!!!

"Steph" wrote in message
...
Hi Tom or Bob,

I added the If LCase(Filename) < LCase(ThisWorkbook.Filename) Then
line of code like you suggested, but got an error Method or Data member
not
found. So I then tried top replace Filename with Fullname. It did not
error, but gave the same results as when I did not have the line of code
in.
Did I put the line in the wrong place? The sub and function are below.
Thanks!

Sub ProcessFiles()
Dim FSO As Object
Dim fldr As Object
Dim sFolder As String
Dim Folder As Object


Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "S:\Global_Share\Operations\Kineticom Timesheet\Approvals\" &
Range("R5").Value

If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
If FileCountOK(Folder) Then
CDO_Email_GM
End If
End If ' sFolder < ""

End Sub

Function FileCountOK(pzFolder As Object)
Dim i As Long
Dim file As Object
Dim Files As Object

FileCountOK = True
Set Files = pzFolder.Files
For Each file In Files
If LCase(Filename) < LCase(ThisWorkbook.Filename) Then
If file.Type = "Microsoft Excel Worksheet" Then
i = i + 1
Workbooks.Open Filename:=file.path
With ActiveWorkbook
FileCountOK = .ActiveSheet.Range("A2").Value = 1
.Close savechanges:=False
If Not FileCountOK Then Exit Function
End With
End If
End If
Next file

End Function
"Steph" wrote in message
...
Thanks Tom, I'll give it a shot.

"Tom Ogilvy" wrote in message
...
When you opening files put in code like

if lcase(filename) < lcase(thisWorkbook.filename) then

end if

or if filename is a fully qualified filename like
"C:\Myfolder\myfile.xls"
then use

if lcase(filename) < lcase(thisworkbook.fullname) then


End if
--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Bob - I hate to ask this - but I ran into hopefully a small problem.
I
think I'm screwing myself up because the click event that calls your
ProcessFiles sub (and therefore your FileCountOK function) is in a
file
WITHIN the same folder the function is checking. So the function
wants
to
check all files including the one thats open. So when I debug and
turn
displayalerts back to true, excel prompts with "file already open,
opening
new will discard changes". I tried to add a few save commands
throughout,
but I still get eratic results.

So as to not confuse you too much with too much detail as to what the
heck
I'm doing, is there a way to check all files within the folder EXCEPT
the
current file? Assume the current file has a 1 in cell A1 (the click
event
calls your sub and also puts a 1 in cell A1).

RP or TO - Add all files EXCEPT active file?
 

That was basically pseudo code.

Filename represented the filename of the workbook

for a workbook you get the name with

Activeworkbook.Name
ThisWorkbook.Name

or for the name with path

ActiveWorkbook.FullName
ThisWorkbook.FullName

for the Lcase(filename) that was to indicate the variable that contains the
name of the file that would next be opened. Assume it probably has the
Path as well and the ThisWorkbook.FullName would be appropriate.

Looks like you figured all this out however.

Sorry for the confusion.

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Never mind guys....I figured it out. It needed to be
If LCase(file) < LCase(ThisWorkbook.FullName) Then
Thanks for all your help!!!

"Steph" wrote in message
...
Hi Tom or Bob,

I added the If LCase(Filename) < LCase(ThisWorkbook.Filename) Then
line of code like you suggested, but got an error Method or Data member
not
found. So I then tried top replace Filename with Fullname. It did not
error, but gave the same results as when I did not have the line of code
in.
Did I put the line in the wrong place? The sub and function are below.
Thanks!

Sub ProcessFiles()
Dim FSO As Object
Dim fldr As Object
Dim sFolder As String
Dim Folder As Object


Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "S:\Global_Share\Operations\Kineticom Timesheet\Approvals\"
&
Range("R5").Value

If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
If FileCountOK(Folder) Then
CDO_Email_GM
End If
End If ' sFolder < ""

End Sub

Function FileCountOK(pzFolder As Object)
Dim i As Long
Dim file As Object
Dim Files As Object

FileCountOK = True
Set Files = pzFolder.Files
For Each file In Files
If LCase(Filename) < LCase(ThisWorkbook.Filename) Then
If file.Type = "Microsoft Excel Worksheet" Then
i = i + 1
Workbooks.Open Filename:=file.path
With ActiveWorkbook
FileCountOK = .ActiveSheet.Range("A2").Value = 1
.Close savechanges:=False
If Not FileCountOK Then Exit Function
End With
End If
End If
Next file

End Function
"Steph" wrote in message
...
Thanks Tom, I'll give it a shot.

"Tom Ogilvy" wrote in message
...
When you opening files put in code like

if lcase(filename) < lcase(thisWorkbook.filename) then

end if

or if filename is a fully qualified filename like
"C:\Myfolder\myfile.xls"
then use

if lcase(filename) < lcase(thisworkbook.fullname) then


End if
--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Bob - I hate to ask this - but I ran into hopefully a small problem.
I
think I'm screwing myself up because the click event that calls your
ProcessFiles sub (and therefore your FileCountOK function) is in a
file
WITHIN the same folder the function is checking. So the function
wants
to
check all files including the one thats open. So when I debug and
turn
displayalerts back to true, excel prompts with "file already open,
opening
new will discard changes". I tried to add a few save commands
throughout,
but I still get eratic results.

So as to not confuse you too much with too much detail as to what
the
heck
I'm doing, is there a way to check all files within the folder
EXCEPT
the
current file? Assume the current file has a 1 in cell A1 (the click
event
calls your sub and also puts a 1 in cell A1).

RP or TO - Add all files EXCEPT active file?
 

Thanks for the clarification, Tom. I appreciate your time! Thanks again
for all of your help on this!!

"Tom Ogilvy" wrote in message
...
That was basically pseudo code.

Filename represented the filename of the workbook

for a workbook you get the name with

Activeworkbook.Name
ThisWorkbook.Name

or for the name with path

ActiveWorkbook.FullName
ThisWorkbook.FullName

for the Lcase(filename) that was to indicate the variable that contains
the
name of the file that would next be opened. Assume it probably has the
Path as well and the ThisWorkbook.FullName would be appropriate.

Looks like you figured all this out however.

Sorry for the confusion.

--
Regards,
Tom Ogilvy

"Steph" wrote in message
...
Never mind guys....I figured it out. It needed to be
If LCase(file) < LCase(ThisWorkbook.FullName) Then
Thanks for all your help!!!

"Steph" wrote in message
...
Hi Tom or Bob,

I added the If LCase(Filename) < LCase(ThisWorkbook.Filename) Then
line of code like you suggested, but got an error Method or Data
member
not
found. So I then tried top replace Filename with Fullname. It did
not
error, but gave the same results as when I did not have the line of
code
in.
Did I put the line in the wrong place? The sub and function are
below.
Thanks!

Sub ProcessFiles()
Dim FSO As Object
Dim fldr As Object
Dim sFolder As String
Dim Folder As Object


Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "S:\Global_Share\Operations\Kineticom
Timesheet\Approvals\"
&
Range("R5").Value

If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
If FileCountOK(Folder) Then
CDO_Email_GM
End If
End If ' sFolder < ""

End Sub

Function FileCountOK(pzFolder As Object)
Dim i As Long
Dim file As Object
Dim Files As Object

FileCountOK = True
Set Files = pzFolder.Files
For Each file In Files
If LCase(Filename) < LCase(ThisWorkbook.Filename) Then
If file.Type = "Microsoft Excel Worksheet" Then
i = i + 1
Workbooks.Open Filename:=file.path
With ActiveWorkbook
FileCountOK = .ActiveSheet.Range("A2").Value =
1
.Close savechanges:=False
If Not FileCountOK Then Exit Function
End With
End If
End If
Next file

End Function
"Steph" wrote in message
...
Thanks Tom, I'll give it a shot.

"Tom Ogilvy" wrote in message
...
When you opening files put in code like

if lcase(filename) < lcase(thisWorkbook.filename) then

end if

or if filename is a fully qualified filename like
"C:\Myfolder\myfile.xls"
then use

if lcase(filename) < lcase(thisworkbook.fullname) then


End if
--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Bob - I hate to ask this - but I ran into hopefully a small
problem.
I
think I'm screwing myself up because the click event that calls
your
ProcessFiles sub (and therefore your FileCountOK function) is in a
file
WITHIN the same folder the function is checking. So the function
wants
to
check all files including the one thats open. So when I debug and
turn
displayalerts back to true, excel prompts with "file already open,
opening
new will discard changes". I tried to add a few save commands
throughout,
but I still get eratic results.

So as to not confuse you too much with too much detail as to what
the
heck
I'm doing, is there a way to check all files within the folder
EXCEPT
the
current file? Assume the current file has a 1 in cell A1 (the
click
event
calls your sub and also puts a 1 in cell A1).