ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Add contents of A1 in all workbooks within a folder (https://www.excelbanter.com/excel-programming/312196-add-contents-a1-all-workbooks-within-folder.html)

Steph[_3_]
Add contents of A1 in all workbooks within a folder
 
I have a bunch of workbooks within a folder. I need to add the contents of
cell A1 in each of the workbooks within the folder, but the names of the
files within the folder will vary. Can I write a function that will add the
contents of all files cell A1?

Much appreicated.

Bob Phillips[_6_]
Add contents of A1 in all workbooks within a folder
 
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub



'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add the contents
of
cell A1 in each of the workbooks within the folder, but the names of the
files within the folder will vary. Can I write a function that will add
the
contents of all files cell A1?

Much appreicated.

Steph[_3_]
Add contents of A1 in all workbooks within a folder
 
Hi Bob. Thanks for the code. But how do I call it, or specify which folder
I want to sum the contents of cell A1? And do I need both the sub and the
function?

Sorry, but my ignorance is getting the best of me here! Thanks for your
help!


"Bob Phillips" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub



'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add the contents
of
cell A1 in each of the workbooks within the folder, but the names of the
files within the folder will vary. Can I write a function that will add
the
contents of all files cell A1?

Much appreicated.

Bob Phillips[_6_]
Add contents of A1 in all workbooks within a folder
 
Steph,

This code is all inclusive, that is when you run it it will provide a
dialog box that will allow you to navigate to the folder of your choice.
When chosen, it goes through each workbook, and gets the value from cell A1.
You will need to add some code to process the sum, in variable nTotal, else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then just run the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or specify which
folder
I want to sum the contents of cell A1? And do I need both the sub and the
function?

Sorry, but my ignorance is getting the best of me here! Thanks for your
help!


"Bob Phillips" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub



'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add the
contents
of
cell A1 in each of the workbooks within the folder, but the names of
the
files within the folder will vary. Can I write a function that will
add
the
contents of all files cell A1?

Much appreicated.

Steph[_3_]
Add contents of A1 in all workbooks within a folder
 
Hi Bob. Thanks for the info. I'm going to use the resulting sum in other
areas, so that being said I always try to use functions to do that. In a
nutshell, in cell A1 of the files is either a 1 (processed) or 0 (not
processed). I have a click event on all the files in the folder that I want
to sum the contents of all the clls A1 of each file. If that sum equals the
number of files in the folder (I already have that function), then I execute
another procedure. If not, then nothing.

So then in the click event, I'd have:
If Numberprocessed{your code} = Numberoffiles, then
Application.run Updateall
else exit sub

Can I still do something like that with your code?

"Bob Phillips" wrote in message
...
Steph,

This code is all inclusive, that is when you run it it will provide a
dialog box that will allow you to navigate to the folder of your choice.
When chosen, it goes through each workbook, and gets the value from cell
A1.
You will need to add some code to process the sum, in variable nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then just run the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or specify which
folder
I want to sum the contents of cell A1? And do I need both the sub and
the
function?

Sorry, but my ignorance is getting the best of me here! Thanks for your
help!


"Bob Phillips" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub



'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add the
contents
of
cell A1 in each of the workbooks within the folder, but the names of
the
files within the folder will vary. Can I write a function that will
add
the
contents of all files cell A1?

Much appreicated.

Steph[_3_]
Add contents of A1 in all workbooks within a folder
 
Hi Bob, also, I tried to execute as you stated earlier, and got a
User-defined type not defined on the line
Dim bInfo As BROWSEINFO in the function..

"Bob Phillips" wrote in message
...
Steph,

This code is all inclusive, that is when you run it it will provide a
dialog box that will allow you to navigate to the folder of your choice.
When chosen, it goes through each workbook, and gets the value from cell
A1.
You will need to add some code to process the sum, in variable nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then just run the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or specify which
folder
I want to sum the contents of cell A1? And do I need both the sub and
the
function?

Sorry, but my ignorance is getting the best of me here! Thanks for your
help!


"Bob Phillips" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub



'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add the
contents
of
cell A1 in each of the workbooks within the folder, but the names of
the
files within the folder will vary. Can I write a function that will
add
the
contents of all files cell A1?

Much appreicated.

Bob Phillips[_6_]
Add contents of A1 in all workbooks within a folder
 
Yeah, but probably best to modify like so.


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


Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
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
Fim nTotals as double

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
nTotal = nTotal + .ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

FileCountOK = nTotal = i

End Function

'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the info. I'm going to use the resulting sum in other
areas, so that being said I always try to use functions to do that. In a
nutshell, in cell A1 of the files is either a 1 (processed) or 0 (not
processed). I have a click event on all the files in the folder that I
want
to sum the contents of all the clls A1 of each file. If that sum equals
the
number of files in the folder (I already have that function), then I
execute
another procedure. If not, then nothing.

So then in the click event, I'd have:
If Numberprocessed{your code} = Numberoffiles, then
Application.run Updateall
else exit sub

Can I still do something like that with your code?

"Bob Phillips" wrote in message
...
Steph,

This code is all inclusive, that is when you run it it will provide a
dialog box that will allow you to navigate to the folder of your choice.
When chosen, it goes through each workbook, and gets the value from cell
A1.
You will need to add some code to process the sum, in variable nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then just run
the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or specify which
folder
I want to sum the contents of cell A1? And do I need both the sub and
the
function?

Sorry, but my ignorance is getting the best of me here! Thanks for
your
help!


"Bob Phillips" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub



'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder =
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add the
contents
of
cell A1 in each of the workbooks within the folder, but the names
of
the
files within the folder will vary. Can I write a function that
will
add
the
contents of all files cell A1?

Much appreicated.

Bob Phillips[_6_]
Add contents of A1 in all workbooks within a folder
 
Sorry Steph, forgot an important declaratives bit of code.

Put this bit of code before each of my poslast pwhichever of my posts you
use

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


--

HTH

RP

"Steph" wrote in message
...
Hi Bob, also, I tried to execute as you stated earlier, and got a
User-defined type not defined on the line
Dim bInfo As BROWSEINFO in the function..

"Bob Phillips" wrote in message
...
Steph,

This code is all inclusive, that is when you run it it will provide a
dialog box that will allow you to navigate to the folder of your choice.
When chosen, it goes through each workbook, and gets the value from cell
A1.
You will need to add some code to process the sum, in variable nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then just run
the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or specify which
folder
I want to sum the contents of cell A1? And do I need both the sub and
the
function?

Sorry, but my ignorance is getting the best of me here! Thanks for
your
help!


"Bob Phillips" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub



'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder =
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add the
contents
of
cell A1 in each of the workbooks within the folder, but the names
of
the
files within the folder will vary. Can I write a function that
will
add
the
contents of all files cell A1?

Much appreicated.

Steph[_3_]
Add contents of A1 in all workbooks within a folder
 
Thanks so much Bob. I really appreciate all your help!!

"Bob Phillips" wrote in message
...
Sorry Steph, forgot an important declaratives bit of code.

Put this bit of code before each of my poslast pwhichever of my posts you
use

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


--

HTH

RP

"Steph" wrote in message
...
Hi Bob, also, I tried to execute as you stated earlier, and got a
User-defined type not defined on the line
Dim bInfo As BROWSEINFO in the function..

"Bob Phillips" wrote in message
...
Steph,

This code is all inclusive, that is when you run it it will provide a
dialog box that will allow you to navigate to the folder of your
choice.
When chosen, it goes through each workbook, and gets the value from
cell
A1.
You will need to add some code to process the sum, in variable nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then just run
the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or specify which
folder
I want to sum the contents of cell A1? And do I need both the sub and
the
function?

Sorry, but my ignorance is getting the best of me here! Thanks for
your
help!


"Bob Phillips" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal +
.ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub



'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder =
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory
to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add the
contents
of
cell A1 in each of the workbooks within the folder, but the names
of
the
files within the folder will vary. Can I write a function that
will
add
the
contents of all files cell A1?

Much appreicated.

Steph[_3_]
Add contents of A1 in all workbooks within a folder
 
Hey Bob, one more question if I may. What if know the path of the folder
that I need evaluated? Can I eliminate a bunch of that code? For instance,
I know the folder I need evaluated is C:\evaluation\

Thanks!

"Bob Phillips" wrote in message
...
Sorry Steph, forgot an important declaratives bit of code.

Put this bit of code before each of my poslast pwhichever of my posts you
use

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


--

HTH

RP

"Steph" wrote in message
...
Hi Bob, also, I tried to execute as you stated earlier, and got a
User-defined type not defined on the line
Dim bInfo As BROWSEINFO in the function..

"Bob Phillips" wrote in message
...
Steph,

This code is all inclusive, that is when you run it it will provide a
dialog box that will allow you to navigate to the folder of your
choice.
When chosen, it goes through each workbook, and gets the value from
cell
A1.
You will need to add some code to process the sum, in variable nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then just run
the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or specify which
folder
I want to sum the contents of cell A1? And do I need both the sub and
the
function?

Sorry, but my ignorance is getting the best of me here! Thanks for
your
help!


"Bob Phillips" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal +
.ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub



'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder =
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory
to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add the
contents
of
cell A1 in each of the workbooks within the folder, but the names
of
the
files within the folder will vary. Can I write a function that
will
add
the
contents of all files cell A1?

Much appreicated.

Tom Ogilvy
Add contents of A1 in all workbooks within a folder
 
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ActiveSheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file


End Sub

or for the Function

If filecountOK("c:\evaluation") then


-------------
Function FileCountOK(pzFolder as Object)
Dim i As Long
Dim file As Object
Dim Files As Object
Fim nTotals as double

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
nTotal = nTotal + .ACtivesheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file

FileCountOK = nTotal = i

End Function


Although it appears to me that the question is answered as soon as you hit
the first file with a 0

--
Regards,
Tom Ogilvy
"Steph" wrote in message
...
Hey Bob, one more question if I may. What if know the path of the folder
that I need evaluated? Can I eliminate a bunch of that code? For
instance,
I know the folder I need evaluated is C:\evaluation\

Thanks!

"Bob Phillips" wrote in message
...
Sorry Steph, forgot an important declaratives bit of code.

Put this bit of code before each of my poslast pwhichever of my posts
you
use

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


--

HTH

RP

"Steph" wrote in message
...
Hi Bob, also, I tried to execute as you stated earlier, and got a
User-defined type not defined on the line
Dim bInfo As BROWSEINFO in the function..

"Bob Phillips" wrote in message
...
Steph,

This code is all inclusive, that is when you run it it will provide
a
dialog box that will allow you to navigate to the folder of your
choice.
When chosen, it goes through each workbook, and gets the value from
cell
A1.
You will need to add some code to process the sum, in variable
nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then just run
the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or specify
which
folder
I want to sum the contents of cell A1? And do I need both the sub
and
the
function?

Sorry, but my ignorance is getting the best of me here! Thanks for
your
help!


"Bob Phillips" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal +
.ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub



'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder =
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory
to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the
dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add the
contents
of
cell A1 in each of the workbooks within the folder, but the
names
of
the
files within the folder will vary. Can I write a function that
will
add
the
contents of all files cell A1?

Much appreicated.

Steph[_3_]
Add contents of A1 in all workbooks within a folder
 
Hi Tom. EXCELLENT point. The question IS answered once I encounter a file
with zero in A1. I assume that greatly simplifies things. I hate to even
ask, but is asking you to modify your code asking too much??

I can't believe I never thought of that!
"Tom Ogilvy" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ActiveSheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file


End Sub

or for the Function

If filecountOK("c:\evaluation") then


-------------
Function FileCountOK(pzFolder as Object)
Dim i As Long
Dim file As Object
Dim Files As Object
Fim nTotals as double

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
nTotal = nTotal + .ACtivesheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file

FileCountOK = nTotal = i

End Function


Although it appears to me that the question is answered as soon as you hit
the first file with a 0

--
Regards,
Tom Ogilvy
"Steph" wrote in message
...
Hey Bob, one more question if I may. What if know the path of the
folder
that I need evaluated? Can I eliminate a bunch of that code? For
instance,
I know the folder I need evaluated is C:\evaluation\

Thanks!

"Bob Phillips" wrote in message
...
Sorry Steph, forgot an important declaratives bit of code.

Put this bit of code before each of my poslast pwhichever of my posts
you
use

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


--

HTH

RP

"Steph" wrote in message
...
Hi Bob, also, I tried to execute as you stated earlier, and got a
User-defined type not defined on the line
Dim bInfo As BROWSEINFO in the function..

"Bob Phillips" wrote in message
...
Steph,

This code is all inclusive, that is when you run it it will provide
a
dialog box that will allow you to navigate to the folder of your
choice.
When chosen, it goes through each workbook, and gets the value from
cell
A1.
You will need to add some code to process the sum, in variable
nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then just
run
the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or specify
which
folder
I want to sum the contents of cell A1? And do I need both the sub
and
the
function?

Sorry, but my ignorance is getting the best of me here! Thanks for
your
help!


"Bob Phillips" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal +
.ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub




'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)

'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder =
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of
directory
to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the
dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add the
contents
of
cell A1 in each of the workbooks within the folder, but the
names
of
the
files within the folder will vary. Can I write a function that
will
add
the
contents of all files cell A1?

Much appreicated.

Bob Phillips[_6_]
Add contents of A1 in all workbooks within a folder
 
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

"Steph" wrote in message
...
Hi Tom. EXCELLENT point. The question IS answered once I encounter a
file
with zero in A1. I assume that greatly simplifies things. I hate to even
ask, but is asking you to modify your code asking too much??

I can't believe I never thought of that!
"Tom Ogilvy" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ActiveSheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file


End Sub

or for the Function

If filecountOK("c:\evaluation") then


-------------
Function FileCountOK(pzFolder as Object)
Dim i As Long
Dim file As Object
Dim Files As Object
Fim nTotals as double

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
nTotal = nTotal + .ACtivesheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file

FileCountOK = nTotal = i

End Function


Although it appears to me that the question is answered as soon as you
hit
the first file with a 0

--
Regards,
Tom Ogilvy
"Steph" wrote in message
...
Hey Bob, one more question if I may. What if know the path of the
folder
that I need evaluated? Can I eliminate a bunch of that code? For
instance,
I know the folder I need evaluated is C:\evaluation\

Thanks!

"Bob Phillips" wrote in message
...
Sorry Steph, forgot an important declaratives bit of code.

Put this bit of code before each of my poslast pwhichever of my posts
you
use

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


--

HTH

RP

"Steph" wrote in message
...
Hi Bob, also, I tried to execute as you stated earlier, and got a
User-defined type not defined on the line
Dim bInfo As BROWSEINFO in the function..

"Bob Phillips" wrote in message
...
Steph,

This code is all inclusive, that is when you run it it will
provide
a
dialog box that will allow you to navigate to the folder of your
choice.
When chosen, it goes through each workbook, and gets the value
from
cell
A1.
You will need to add some code to process the sum, in variable
nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then just
run
the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or specify
which
folder
I want to sum the contents of cell A1? And do I need both the
sub
and
the
function?

Sorry, but my ignorance is getting the best of me here! Thanks
for
your
help!


"Bob Phillips" wrote in
message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal +
.ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub




'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)

'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder =
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of
directory
to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the
dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add
the
contents
of
cell A1 in each of the workbooks within the folder, but the
names
of
the
files within the folder will vary. Can I write a function
that
will
add
the
contents of all files cell A1?

Much appreicated.

Steph[_3_]
Add contents of 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

"Steph" wrote in message
...
Hi Tom. EXCELLENT point. The question IS answered once I encounter a
file
with zero in A1. I assume that greatly simplifies things. I hate to
even
ask, but is asking you to modify your code asking too much??

I can't believe I never thought of that!
"Tom Ogilvy" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ActiveSheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file


End Sub

or for the Function

If filecountOK("c:\evaluation") then


-------------
Function FileCountOK(pzFolder as Object)
Dim i As Long
Dim file As Object
Dim Files As Object
Fim nTotals as double

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
nTotal = nTotal + .ACtivesheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file

FileCountOK = nTotal = i

End Function


Although it appears to me that the question is answered as soon as you
hit
the first file with a 0

--
Regards,
Tom Ogilvy
"Steph" wrote in message
...
Hey Bob, one more question if I may. What if know the path of the
folder
that I need evaluated? Can I eliminate a bunch of that code? For
instance,
I know the folder I need evaluated is C:\evaluation\

Thanks!

"Bob Phillips" wrote in message
...
Sorry Steph, forgot an important declaratives bit of code.

Put this bit of code before each of my poslast pwhichever of my
posts
you
use

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


--

HTH

RP

"Steph" wrote in message
...
Hi Bob, also, I tried to execute as you stated earlier, and got a
User-defined type not defined on the line
Dim bInfo As BROWSEINFO in the function..

"Bob Phillips" wrote in message
...
Steph,

This code is all inclusive, that is when you run it it will
provide
a
dialog box that will allow you to navigate to the folder of your
choice.
When chosen, it goes through each workbook, and gets the value
from
cell
A1.
You will need to add some code to process the sum, in variable
nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then just
run
the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or specify
which
folder
I want to sum the contents of cell A1? And do I need both the
sub
and
the
function?

Sorry, but my ignorance is getting the best of me here! Thanks
for
your
help!


"Bob Phillips" wrote in
message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal +
.ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub




'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)

'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder =
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of
directory
to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the
dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add
the
contents
of
cell A1 in each of the workbooks within the folder, but the
names
of
the
files within the folder will vary. Can I write a function
that
will
add
the
contents of all files cell A1?

Much appreicated.

Myrna Larson
Add contents of A1 in all workbooks within a folder
 
Look at the header for the FileCountOK procedure. It requires an argument. You
didn't supply one. Maybe it should be

If FileCountOK(Folder) Then

On Mon, 4 Oct 2004 15:44:44 -0400, "Steph" wrote:

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

"Steph" wrote in message
...
Hi Tom. EXCELLENT point. The question IS answered once I encounter a
file
with zero in A1. I assume that greatly simplifies things. I hate to
even
ask, but is asking you to modify your code asking too much??

I can't believe I never thought of that!
"Tom Ogilvy" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ActiveSheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file


End Sub

or for the Function

If filecountOK("c:\evaluation") then


-------------
Function FileCountOK(pzFolder as Object)
Dim i As Long
Dim file As Object
Dim Files As Object
Fim nTotals as double

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
nTotal = nTotal + .ACtivesheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file

FileCountOK = nTotal = i

End Function


Although it appears to me that the question is answered as soon as you
hit
the first file with a 0

--
Regards,
Tom Ogilvy
"Steph" wrote in message
...
Hey Bob, one more question if I may. What if know the path of the
folder
that I need evaluated? Can I eliminate a bunch of that code? For
instance,
I know the folder I need evaluated is C:\evaluation\

Thanks!

"Bob Phillips" wrote in message
...
Sorry Steph, forgot an important declaratives bit of code.

Put this bit of code before each of my poslast pwhichever of my
posts
you
use

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


--

HTH

RP

"Steph" wrote in message
...
Hi Bob, also, I tried to execute as you stated earlier, and got a
User-defined type not defined on the line
Dim bInfo As BROWSEINFO in the function..

"Bob Phillips" wrote in message
...
Steph,

This code is all inclusive, that is when you run it it will
provide
a
dialog box that will allow you to navigate to the folder of your
choice.
When chosen, it goes through each workbook, and gets the value
from
cell
A1.
You will need to add some code to process the sum, in variable
nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then just
run
the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or specify
which
folder
I want to sum the contents of cell A1? And do I need both the
sub
and
the
function?

Sorry, but my ignorance is getting the best of me here! Thanks
for
your
help!


"Bob Phillips" wrote in
message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal +
.ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub




'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)

'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder =
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of
directory
to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the
dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to add
the
contents
of
cell A1 in each of the workbooks within the folder, but the
names
of
the
files within the folder will vary. Can I write a function
that
will
add
the
contents of all files cell A1?

Much appreicated.

Bob Phillips[_6_]
Add contents of A1 in all workbooks within a folder
 
Hi Steph,

Thought you had given up.

Sorry, my fault

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(Folder) 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

"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

"Steph" wrote in message
...
Hi Tom. EXCELLENT point. The question IS answered once I encounter a
file
with zero in A1. I assume that greatly simplifies things. I hate to
even
ask, but is asking you to modify your code asking too much??

I can't believe I never thought of that!
"Tom Ogilvy" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ActiveSheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file


End Sub

or for the Function

If filecountOK("c:\evaluation") then


-------------
Function FileCountOK(pzFolder as Object)
Dim i As Long
Dim file As Object
Dim Files As Object
Fim nTotals as double

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
nTotal = nTotal + .ACtivesheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file

FileCountOK = nTotal = i

End Function


Although it appears to me that the question is answered as soon as
you
hit
the first file with a 0

--
Regards,
Tom Ogilvy
"Steph" wrote in message
...
Hey Bob, one more question if I may. What if know the path of the
folder
that I need evaluated? Can I eliminate a bunch of that code? For
instance,
I know the folder I need evaluated is C:\evaluation\

Thanks!

"Bob Phillips" wrote in message
...
Sorry Steph, forgot an important declaratives bit of code.

Put this bit of code before each of my poslast pwhichever of my
posts
you
use

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


--

HTH

RP

"Steph" wrote in message
...
Hi Bob, also, I tried to execute as you stated earlier, and got a
User-defined type not defined on the line
Dim bInfo As BROWSEINFO in the function..

"Bob Phillips" wrote in
message
...
Steph,

This code is all inclusive, that is when you run it it will
provide
a
dialog box that will allow you to navigate to the folder of
your
choice.
When chosen, it goes through each workbook, and gets the value
from
cell
A1.
You will need to add some code to process the sum, in variable
nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then
just
run
the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or
specify
which
folder
I want to sum the contents of cell A1? And do I need both the
sub
and
the
function?

Sorry, but my ignorance is getting the best of me here!
Thanks
for
your
help!


"Bob Phillips" wrote in
message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal +
.ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub





'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)


'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder
=
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of
directory
to
Return
oDialog = SHBrowseForFolder(bInfo) 'display the
dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path)
Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to
add
the
contents
of
cell A1 in each of the workbooks within the folder, but
the
names
of
the
files within the folder will vary. Can I write a function
that
will
add
the
contents of all files cell A1?

Much appreicated.

Bob Phillips[_6_]
Add contents of A1 in all workbooks within a folder
 
Oops

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 Object
Dim 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(Folder) 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 Then Exit Function
End With
End If
Next file

End Function





--

HTH

RP

"Bob Phillips" wrote in message
...
Hi Steph,

Thought you had given up.

Sorry, my fault

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(Folder) 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

"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

"Steph" wrote in message
...
Hi Tom. EXCELLENT point. The question IS answered once I encounter
a
file
with zero in A1. I assume that greatly simplifies things. I hate to
even
ask, but is asking you to modify your code asking too much??

I can't believe I never thought of that!
"Tom Ogilvy" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal + .ActiveSheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file


End Sub

or for the Function

If filecountOK("c:\evaluation") then


-------------
Function FileCountOK(pzFolder as Object)
Dim i As Long
Dim file As Object
Dim Files As Object
Fim nTotals as double

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
nTotal = nTotal + .ACtivesheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file

FileCountOK = nTotal = i

End Function


Although it appears to me that the question is answered as soon as
you
hit
the first file with a 0

--
Regards,
Tom Ogilvy
"Steph" wrote in message
...
Hey Bob, one more question if I may. What if know the path of
the
folder
that I need evaluated? Can I eliminate a bunch of that code? For
instance,
I know the folder I need evaluated is C:\evaluation\

Thanks!

"Bob Phillips" wrote in
message
...
Sorry Steph, forgot an important declaratives bit of code.

Put this bit of code before each of my poslast pwhichever of my
posts
you
use

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


--

HTH

RP

"Steph" wrote in message
...
Hi Bob, also, I tried to execute as you stated earlier, and got
a
User-defined type not defined on the line
Dim bInfo As BROWSEINFO in the function..

"Bob Phillips" wrote in
message
...
Steph,

This code is all inclusive, that is when you run it it will
provide
a
dialog box that will allow you to navigate to the folder of
your
choice.
When chosen, it goes through each workbook, and gets the
value
from
cell
A1.
You will need to add some code to process the sum, in
variable
nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then
just
run
the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or
specify
which
folder
I want to sum the contents of cell A1? And do I need both
the
sub
and
the
function?

Sorry, but my ignorance is getting the best of me here!
Thanks
for
your
help!


"Bob Phillips" wrote in
message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet"
Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal +
.ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub





'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)


'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root
folder
=
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of
directory
to
Return
oDialog = SHBrowseForFolder(bInfo) 'display
the
dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path)
Then
GetFolder = Left(path, InStr(path, Chr$(0)) -
1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to
add
the
contents
of
cell A1 in each of the workbooks within the folder, but
the
names
of
the
files within the folder will vary. Can I write a
function
that
will
add
the
contents of all files cell A1?

Much appreicated.

Steph[_3_]
Add contents of A1 in all workbooks within a folder
 
Beautiful!! Thanks SO much Bob! And sorry for the second thread - it's
been so long, the original was way down the bottom and I wasn't sure you'd
see it. Again, Thank you!

"Bob Phillips" wrote in message
...
Oops

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 Object
Dim 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(Folder) 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 Then Exit Function
End With
End If
Next file

End Function





--

HTH

RP

"Bob Phillips" wrote in message
...
Hi Steph,

Thought you had given up.

Sorry, my fault

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(Folder) 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

"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

"Steph" wrote in message
...
Hi Tom. EXCELLENT point. The question IS answered once I encounter
a
file
with zero in A1. I assume that greatly simplifies things. I hate
to
even
ask, but is asking you to modify your code asking too much??

I can't believe I never thought of that!
"Tom Ogilvy" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal +
.ActiveSheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file


End Sub

or for the Function

If filecountOK("c:\evaluation") then


-------------
Function FileCountOK(pzFolder as Object)
Dim i As Long
Dim file As Object
Dim Files As Object
Fim nTotals as double

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
nTotal = nTotal +
.ACtivesheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file

FileCountOK = nTotal = i

End Function


Although it appears to me that the question is answered as soon as
you
hit
the first file with a 0

--
Regards,
Tom Ogilvy
"Steph" wrote in message
...
Hey Bob, one more question if I may. What if know the path of
the
folder
that I need evaluated? Can I eliminate a bunch of that code?
For
instance,
I know the folder I need evaluated is C:\evaluation\

Thanks!

"Bob Phillips" wrote in
message
...
Sorry Steph, forgot an important declaratives bit of code.

Put this bit of code before each of my poslast pwhichever of my
posts
you
use

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


--

HTH

RP

"Steph" wrote in message
...
Hi Bob, also, I tried to execute as you stated earlier, and
got
a
User-defined type not defined on the line
Dim bInfo As BROWSEINFO in the function..

"Bob Phillips" wrote in
message
...
Steph,

This code is all inclusive, that is when you run it it will
provide
a
dialog box that will allow you to navigate to the folder of
your
choice.
When chosen, it goes through each workbook, and gets the
value
from
cell
A1.
You will need to add some code to process the sum, in
variable
nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it. Then
just
run
the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or
specify
which
folder
I want to sum the contents of cell A1? And do I need both
the
sub
and
the
function?

Sorry, but my ignorance is getting the best of me here!
Thanks
for
your
help!


"Bob Phillips" wrote in
message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet"
Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal +
.ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub





'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)


'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root
folder
=
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of
directory
to
Return
oDialog = SHBrowseForFolder(bInfo) 'display
the
dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path)
Then
GetFolder = Left(path, InStr(path, Chr$(0)) -
1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need to
add
the
contents
of
cell A1 in each of the workbooks within the folder, but
the
names
of
the
files within the folder will vary. Can I write a
function
that
will
add
the
contents of all files cell A1?

Much appreicated.

Bob Phillips[_6_]
Add contents of A1 in all workbooks within a folder
 
No probs Steph, after a couple of days it is sometimes a good idea to start
another. I answered the original before I saw the new one, just responded to
the latter in case you were monitoring the second.

Bob

"Steph" wrote in message
...
Beautiful!! Thanks SO much Bob! And sorry for the second thread - it's
been so long, the original was way down the bottom and I wasn't sure you'd
see it. Again, Thank you!

"Bob Phillips" wrote in message
...
Oops

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 Object
Dim 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(Folder) 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 Then Exit Function
End With
End If
Next file

End Function





--

HTH

RP

"Bob Phillips" wrote in message
...
Hi Steph,

Thought you had given up.

Sorry, my fault

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(Folder) 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

"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

"Steph" wrote in message
...
Hi Tom. EXCELLENT point. The question IS answered once I
encounter
a
file
with zero in A1. I assume that greatly simplifies things. I hate
to
even
ask, but is asking you to modify your code asking too much??

I can't believe I never thought of that!
"Tom Ogilvy" wrote in message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = "C:\evaluation"

Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal +
.ActiveSheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file


End Sub

or for the Function

If filecountOK("c:\evaluation") then


-------------
Function FileCountOK(pzFolder as Object)
Dim i As Long
Dim file As Object
Dim Files As Object
Fim nTotals as double

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
nTotal = nTotal +
.ACtivesheet.Range("A1").Value
.Close savechanges:=False
End With
End If
Next file

FileCountOK = nTotal = i

End Function


Although it appears to me that the question is answered as soon
as
you
hit
the first file with a 0

--
Regards,
Tom Ogilvy
"Steph" wrote in message
...
Hey Bob, one more question if I may. What if know the path of
the
folder
that I need evaluated? Can I eliminate a bunch of that code?
For
instance,
I know the folder I need evaluated is C:\evaluation\

Thanks!

"Bob Phillips" wrote in
message
...
Sorry Steph, forgot an important declaratives bit of code.

Put this bit of code before each of my poslast pwhichever of
my
posts
you
use

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


--

HTH

RP

"Steph" wrote in message
...
Hi Bob, also, I tried to execute as you stated earlier, and
got
a
User-defined type not defined on the line
Dim bInfo As BROWSEINFO in the function..

"Bob Phillips" wrote in
message
...
Steph,

This code is all inclusive, that is when you run it it
will
provide
a
dialog box that will allow you to navigate to the folder
of
your
choice.
When chosen, it goes through each workbook, and gets the
value
from
cell
A1.
You will need to add some code to process the sum, in
variable
nTotal,
else
it will just disappear at the end.

Create a new code module in VBA, and copy this into it.
Then
just
run
the
ProcessFile macro.

--

HTH

RP

"Steph" wrote in message
...
Hi Bob. Thanks for the code. But how do I call it, or
specify
which
folder
I want to sum the contents of cell A1? And do I need
both
the
sub
and
the
function?

Sorry, but my ignorance is getting the best of me here!
Thanks
for
your
help!


"Bob Phillips" wrote
in
message
...
Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object
Fim nTotals as double

Set FSO =
CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder < "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet"
Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
nTotal = nTotal +
.ACtivesheet.Range("AQ1").Value
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder < ""

End Sub





'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)


'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a
folder."
bInfo.pidlRoot = 0& 'Root
folder
=
Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type
of
directory
to
Return
oDialog = SHBrowseForFolder(bInfo)
'display
the
dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal
path)
Then
GetFolder = Left(path, InStr(path,
Chr$(0)) -
1)
End If

End Function


--

HTH

RP

"Steph" wrote in message
...
I have a bunch of workbooks within a folder. I need
to
add
the
contents
of
cell A1 in each of the workbooks within the folder,
but
the
names
of
the
files within the folder will vary. Can I write a
function
that
will
add
the
contents of all files cell A1?

Much appreicated.


All times are GMT +1. The time now is 04:45 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com