Total sum based on file name
 

I have one "TOTALS" worksheet where I need to display a total. I need
to cycle through a folder full of excel files, and if the name of the
file begins with "Branch" (as in "Branch19Sales.xls") I need to sum up
cell C6 on those files in my "TOTALS" worksheet.

TIA
-Mike

*** Sent via Developersdex http://www.developersdex.com ***

Total sum based on file name
 

Dim fName as String, wkbk as Workbook
Dim rng as Range, tot as Double
fName = dir("C:\myfolder\*.xls")
do while fname < ""
if left(lcase(fName,6)) = "branch" then
set wkbk = Workbooks.Open("c:\myfolder\" & fname)
set rng = wkbk.Worksheets(1).Range("C6)
if isnumeric(rng) then
tot = tot + rng.value
end if
wkbk.Close Savechanges:=False
end if
fName = Dir()
Loop

activesheet.Range("B9").Value = tot

--
Regards,
Tom Ogilvy


"Michael Smith" wrote in message
...
I have one "TOTALS" worksheet where I need to display a total. I need
to cycle through a folder full of excel files, and if the name of the
file begins with "Branch" (as in "Branch19Sales.xls") I need to sum up
cell C6 on those files in my "TOTALS" worksheet.

TIA
-Mike

*** Sent via Developersdex http://www.developersdex.com ***

Total sum based on file name
 

Tom Thank you. You are the guru.



*** Sent via Developersdex http://www.developersdex.com ***

Total sum based on file name
 

Doh, for some reason its is giving me an invalid property assignment
error on this line...it doesn't like the LCase formula I believe. What
am I missing?

If Left(LCase(fName, 6)) = "branch" Then


*** Sent via Developersdex http://www.developersdex.com ***