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

[Steph]

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!

 

[Tom Ogilvy]

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

 

[Bob Phillips]

Steph, I have replied in the original thread.

 

[Myrna Larson]

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

 

[Bob Phillips]

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

 

[Tom Ogilvy]

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.

 

[Steph]

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

 

[Tom Ogilvy]

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

 

[Steph]

Thanks Tom, I'll give it a shot.

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

 

[Steph]

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

 

[Tom Ogilvy]

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.