RP - Add contents of cell A1 in all workbooks within a folder
 

Thanks for the follow-up Bob. I ran it, but I get an "Argument not
optional" error on the line
If FileCountOK Then
Am I doing something wrong? I'm runnung the ProcessFiles sub. Thanks!

"Bob Phillips" wrote in message
...
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

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


Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
If FileCountOK Then
StephsMacro
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 file.Type = "Microsoft Excel Worksheet" Then
i=i+1
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
FileCountOK = .Activesheet.Range("AQ1").Value = 1
.Close savechanges:=False
If Not FileCountOK The Exit Function
End With
End If
Next file

End Function


--

HTH

RP

RP - Add contents of cell A1 in all workbooks within a folder
 

Would that be because you declare

Function FileCountOK(pzFolder as Object)

to require the folder (as string) passed as an argument?

If FilecountOK("C:\Myfolder") then

--
Regards,
Tom Ogilvy


"Steph" wrote in message
...
Thanks for the follow-up Bob. I ran it, but I get an "Argument not
optional" error on the line
If FileCountOK Then
Am I doing something wrong? I'm runnung the ProcessFiles sub. Thanks!

"Bob Phillips" wrote in message
...
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

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


Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
If FileCountOK Then
StephsMacro
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 file.Type = "Microsoft Excel Worksheet" Then
i=i+1
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
FileCountOK = .Activesheet.Range("AQ1").Value = 1
.Close savechanges:=False
If Not FileCountOK The Exit Function
End With
End If
Next file

End Function


--

HTH

RP

RP - Add contents of cell A1 in all workbooks within a folder
 

Steph, I have replied in the original thread.

--

HTH

RP

"Steph" wrote in message
...
Thanks for the follow-up Bob. I ran it, but I get an "Argument not
optional" error on the line
If FileCountOK Then
Am I doing something wrong? I'm runnung the ProcessFiles sub. Thanks!

"Bob Phillips" wrote in message
...
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

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


Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
If FileCountOK Then
StephsMacro
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 file.Type = "Microsoft Excel Worksheet" Then
i=i+1
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
FileCountOK = .Activesheet.Range("AQ1").Value = 1
.Close savechanges:=False
If Not FileCountOK The Exit Function
End With
End If
Next file

End Function


--

HTH

RP

RP - Add contents of cell A1 in all workbooks within a folder
 

Now I'm confused. Strings aren't "objects", are they? The following code gave
me a compiler "argument type mismatch" error

Sub Test1()
Dim x As String
x = "abc"
y = Fn(x)
End Sub

Function Fn(obj As Object)
Debug.Print obj.Name
End Function


On Mon, 4 Oct 2004 16:28:18 -0400, "Tom Ogilvy" wrote:

Would that be because you declare

Function FileCountOK(pzFolder as Object)

to require the folder (as string) passed as an argument?

If FilecountOK("C:\Myfolder") then

RP - Add contents of cell A1 in all workbooks within a folder
 

No, it should be a folder object as the function expects an object, not a
folder name.

--

HTH

RP

"Myrna Larson" wrote in message
...
Now I'm confused. Strings aren't "objects", are they? The following code
gave
me a compiler "argument type mismatch" error

Sub Test1()
Dim x As String
x = "abc"
y = Fn(x)
End Sub

Function Fn(obj As Object)
Debug.Print obj.Name
End Function


On Mon, 4 Oct 2004 16:28:18 -0400, "Tom Ogilvy" wrote:

Would that be because you declare

Function FileCountOK(pzFolder as Object)

to require the folder (as string) passed as an argument?

If FilecountOK("C:\Myfolder") then

RP - Add contents of cell A1 in all workbooks within a folder
 

Give me a break Myrna. <g

I looked at pzFolder and didn't pay close enough attention - slap my wrist
for reading too fast.

Tell the truth, you really put that code in a module and tested it so you
could post that response. I am honored that you would bother to waste the
time.

--
Regards,
Tom Ogilvy

"Myrna Larson" wrote in message
...
Now I'm confused. Strings aren't "objects", are they? The following code
gave
me a compiler "argument type mismatch" error

Sub Test1()
Dim x As String
x = "abc"
y = Fn(x)
End Sub

Function Fn(obj As Object)
Debug.Print obj.Name
End Function


On Mon, 4 Oct 2004 16:28:18 -0400, "Tom Ogilvy" wrote:

Would that be because you declare

Function FileCountOK(pzFolder as Object)

to require the folder (as string) passed as an argument?

If FilecountOK("C:\Myfolder") then

RP - Add contents of cell A1 in all workbooks within a folder
 

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).

"Bob Phillips" wrote in message
...
Steph, I have replied in the original thread.

--

HTH

RP

"Steph" wrote in message
...
Thanks for the follow-up Bob. I ran it, but I get an "Argument not
optional" error on the line
If FileCountOK Then
Am I doing something wrong? I'm runnung the ProcessFiles sub. Thanks!

"Bob Phillips" wrote in message
...
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

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


Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
If FileCountOK Then
StephsMacro
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 file.Type = "Microsoft Excel Worksheet" Then
i=i+1
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
FileCountOK = .Activesheet.Range("AQ1").Value = 1
.Close savechanges:=False
If Not FileCountOK The Exit Function
End With
End If
Next file

End Function


--

HTH

RP

RP - Add contents of cell A1 in all workbooks within a folder
 

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).

"Bob Phillips" wrote in message
...
Steph, I have replied in the original thread.

--

HTH

RP

"Steph" wrote in message
...
Thanks for the follow-up Bob. I ran it, but I get an "Argument not
optional" error on the line
If FileCountOK Then
Am I doing something wrong? I'm runnung the ProcessFiles sub.
Thanks!

"Bob Phillips" wrote in message
...
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

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


Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
If FileCountOK Then
StephsMacro
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 file.Type = "Microsoft Excel Worksheet" Then
i=i+1
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
FileCountOK = .Activesheet.Range("AQ1").Value = 1
.Close savechanges:=False
If Not FileCountOK The Exit Function
End With
End If
Next file

End Function


--

HTH

RP

RP - Add contents of cell A1 in all workbooks within a folder
 

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).

"Bob Phillips" wrote in message
...
Steph, I have replied in the original thread.

--

HTH

RP

"Steph" wrote in message
...
Thanks for the follow-up Bob. I ran it, but I get an "Argument not
optional" error on the line
If FileCountOK Then
Am I doing something wrong? I'm runnung the ProcessFiles sub.
Thanks!

"Bob Phillips" wrote in message
...
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

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


Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
If FileCountOK Then
StephsMacro
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 file.Type = "Microsoft Excel Worksheet" Then
i=i+1
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
FileCountOK = .Activesheet.Range("AQ1").Value =
1
.Close savechanges:=False
If Not FileCountOK The Exit Function
End With
End If
Next file

End Function


--

HTH

RP

RP - Add contents of cell A1 in all workbooks within a folder
 

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).

"Bob Phillips" wrote in message
...
Steph, I have replied in the original thread.

--

HTH

RP

"Steph" wrote in message
...
Thanks for the follow-up Bob. I ran it, but I get an "Argument not
optional" error on the line
If FileCountOK Then
Am I doing something wrong? I'm runnung the ProcessFiles sub.
Thanks!

"Bob Phillips" wrote in message
...
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

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


Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
If FileCountOK Then
StephsMacro
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 file.Type = "Microsoft Excel Worksheet" Then
i=i+1
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
FileCountOK = .Activesheet.Range("AQ1").Value
=
1
.Close savechanges:=False
If Not FileCountOK The Exit Function
End With
End If
Next file

End Function


--

HTH

RP

RP - Add contents of cell A1 in all workbooks within a folder
 

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
...
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).

"Bob Phillips" wrote in message
...
Steph, I have replied in the original thread.

--

HTH

RP

"Steph" wrote in message
...
Thanks for the follow-up Bob. I ran it, but I get an "Argument
not
optional" error on the line
If FileCountOK Then
Am I doing something wrong? I'm runnung the ProcessFiles sub.
Thanks!

"Bob Phillips" wrote in
message
...
Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

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


Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)
If FileCountOK Then
StephsMacro
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 file.Type = "Microsoft Excel Worksheet" Then
i=i+1
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
FileCountOK =
..Activesheet.Range("AQ1").Value
=
1
.Close savechanges:=False
If Not FileCountOK The Exit Function
End With
End If
Next file

End Function


--

HTH

RP