Macro to sum sheets in a folder
 

Hi everyone,

I am really running this past people before I start writing code to
ensure I can actually do this one way or another.

I am wanting to write a macro in a sheet that finds a certain cell in
every excel file in a folder and sums them up. Sounds simple but am I
kidding myself?

Macro to sum sheets in a folder
 

Hi Keri

Look here
http://www.rondebruin.nl/summary2.htm

This example create a formule link to each file.
You can use a simple Sum formula now to get what you want


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"keri" wrote in message ups.com...
Hi everyone,

I am really running this past people before I start writing code to
ensure I can actually do this one way or another.

I am wanting to write a macro in a sheet that finds a certain cell in
every excel file in a folder and sums them up. Sounds simple but am I
kidding myself?

Macro to sum sheets in a folder
 

Super thanks.

I think my main problem is going to be that I want the macro to
automatically search for new files in the specified folder every time
the work book is opened. I didn't explain this very clearly.

At the moment I store order form in a folder called orders. I currently
have to take the total from each order workbook and manually enter it
into a tracker. I save a new order workbook approx once a week.

The code to get a sum from a destination I can specify is simple. But I
am unsure how to sum from all files in the folder.


Ron de Bruin wrote:
Hi Keri

Look here
http://www.rondebruin.nl/summary2.htm

This example create a formule link to each file.
You can use a simple Sum formula now to get what you want


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"keri" wrote in message ups.com...
Hi everyone,

I am really running this past people before I start writing code to
ensure I can actually do this one way or another.

I am wanting to write a macro in a sheet that finds a certain cell in
every excel file in a folder and sums them up. Sounds simple but am I
kidding myself?

Macro to sum sheets in a folder
 

Ron,

I know I am being a bit dim but please bear with me - i'm a newbie.
I've read the code numerous times and i'm struggling.

Sub Summary_cells_from_Different_Workbooks_1()
Dim FileNameXls As Variant
Dim SummWks As Worksheet
Dim ColNum As Integer
Dim myCell As Range, Rng As Range
Dim RwNum As Long, FNum As Long, FinalSlash As Long
Dim ShName As String, PathStr As String
Dim SheetCheck As String, JustFileName As String
Dim JustFolder As String

ShName = "Sheet1" '<---- Change
Set Rng = Range("A1,D5:E5,Z10") '<---- Change

'Select the files with GetOpenFilename
FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files,
*.xls", _
MultiSelect:=True)

If IsArray(FileNameXls) = False Then
'do nothing
Else
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Add a new workbook with one sheet for the Summary
Set SummWks = Workbooks.Add(1).Worksheets(1)

'The links to the first workbook will start in row 2
RwNum = 1

For FNum = LBound(FileNameXls) To UBound(FileNameXls)
ColNum = 1
RwNum = RwNum + 1
FinalSlash = InStrRev(FileNameXls(FNum), "\")
JustFileName = Mid(FileNameXls(FNum), FinalSlash + 1)
JustFolder = Left(FileNameXls(FNum), FinalSlash - 1)

*****I really don't understand what this part (above) is doing

'copy the workbook name in column A
SummWks.Cells(RwNum, 1).Value = JustFileName

'build the formula string
PathStr = "'" & JustFolder & "\[" & JustFileName & "]" &
ShName & "'!"

****** Or this (above)

On Error Resume Next
SheetCheck = ExecuteExcel4Macro(PathStr &
Range("A1").Address(, , xlR1C1))
If Err.Number < 0 Then
'If the sheet name not exist in the workbook the row
color will be Yellow.
SummWks.Cells(RwNum, 1).Resize(1, Rng.Cells.Count +
1).Interior.Color = vbYellow
Else
For Each myCell In Rng.Cells
ColNum = ColNum + 1
SummWks.Cells(RwNum, ColNum).Formula = "=" &
PathStr & myCell.Address
Next myCell
End If
On Error GoTo 0
Next FNum

' Use AutoFit for setting the column width in the new workbook
SummWks.UsedRange.Columns.AutoFit

MsgBox "The Summary is ready, save the file if you want to keep
it"

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End If
End Sub

The parts I have commented ******* are the parts I know I need to
change to adapt this but I can't understand how they work. All I can
see is that they are returning the file name (but I am probably wrong).
What I want to do is add a part to the macro that finds the first cell
thats contents read "Total Value Of Order" and take the value from the
cell two to the right and put this into the second column in the
original worksheet.

Currently all this macro is doing is returning the file names.

Thanks again.

Macro to sum sheets in a folder
 

Sorry but I still am failing to get this to work. I have changed the
sheet name and cell range and all I get returned is the name - no value
from the range. I am stilll trying tho so will keep you updated.

Macro to sum sheets in a folder
 

My apologies - I now have the basics of this working.However the
solution is not as simple as I had hoped.

Where i specify the sheet - unfortunately not all of the sheets will
have the same name and the name may change later in the year. Can i
specify all sheets or any sheets instead of the sheet name?

Secondly - where I specify the range - again this is not simple as I
need the value of one cell, but this cell's location moves from sheet
to sheet. I need to be able to find the cell by looking for the cell
that read "total order value" and taking the cell 2 to the left of it.


Thanks,


keri wrote:
Sorry but I still am failing to get this to work. I have changed the
sheet name and cell range and all I get returned is the name - no value
from the range. I am stilll trying tho so will keep you updated.

Macro to sum sheets in a folder
 

Hi Keri

What you want is not simple <g

You are looking for a cell on every sheet in each workbook
that read "total order value" and taking the cell 2 to the left of it.

Am I correct

Let me know
I will create a code example for you today then


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"keri" wrote in message ps.com...
My apologies - I now have the basics of this working.However the
solution is not as simple as I had hoped.

Where i specify the sheet - unfortunately not all of the sheets will
have the same name and the name may change later in the year. Can i
specify all sheets or any sheets instead of the sheet name?

Secondly - where I specify the range - again this is not simple as I
need the value of one cell, but this cell's location moves from sheet
to sheet. I need to be able to find the cell by looking for the cell
that read "total order value" and taking the cell 2 to the left of it.


Thanks,


keri wrote:
Sorry but I still am failing to get this to work. I have changed the
sheet name and cell range and all I get returned is the name - no value
from the range. I am stilll trying tho so will keep you updated.

Macro to sum sheets in a folder
 

Correct. But also the workbook sheets may not have the same name each
time. (Although if it makes it easier they will each only ever have one
sheet).

There would be no need to select which workbooks as it would always be
every workbook in a certain folder. (Although the user would need to
give the folder path)

Thankyou so much for your help, I really do appreciate all the time the
experts on here put in to helping us beginners.

Macro to sum sheets in a folder
 

Hi Keri

Try this tester

Copy this macro in a module of a new workbook

MyPath = "C:\Users\Ron\test"
Change this to your folder

Be sure that the find value is correct
What:="total order value"


Save the file outside this folder and run the macro

Sub Example2_More_sheets()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long
Dim Fnum As Long
Dim mybook As Workbook
Dim basebook As Workbook
Dim sourceRange As Range
Dim rnum As Long
Dim sh As Worksheet

'Fill in the path\folder where the files are
MyPath = "C:\Users\Ron\test"

'Add a slash at the end if the user forget it
If Right(MyPath, 1) < "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.xls")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
'clear all cells on the first sheet
basebook.Worksheets(1).Cells.Clear
rnum = 1

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath < ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array(myFiles)
If Fnum 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
For Each sh In mybook.Worksheets
Set sourceRange = sh.Cells
With sourceRange
Set Rng = .Find(What:="total order value", _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
basebook.Worksheets(1).Cells(rnum, "A").Value = mybook.Name & " " & sh.Name
basebook.Worksheets(1).Cells(rnum, "B").Value = Rng.Offset(0, -2).Value
rnum = rnum + 1
Else
' do Nothing
End If
End With

Next sh

mybook.Close savechanges:=False
Next Fnum
End If
CleanUp:
Application.ScreenUpdating = True
End Sub




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"keri" wrote in message ups.com...
Correct. But also the workbook sheets may not have the same name each
time. (Although if it makes it easier they will each only ever have one
sheet).

There would be no need to select which workbooks as it would always be
every workbook in a certain folder. (Although the user would need to
give the folder path)

Thankyou so much for your help, I really do appreciate all the time the
experts on here put in to helping us beginners.

Macro to sum sheets in a folder
 

ABSOLUTELY AMAZING!
Thankyou ever so much for your time & effort - this now works like a
dream. I am just going to add in some bits (so user can point to the
folder where the orders are stored etc). But thankyou thankyou thankyou
thankyou.

(Ps Dim rng as Range was missing)

Really do appreciate this - you're a little excel angel in disguise!

Macro to sum sheets in a folder
 

ABSOLUTELY AMAZING!
Thankyou ever so much for your time & effort - this now works like a
dream. I am just going to add in some bits (so user can point to the
folder where the orders are stored etc). But thankyou thankyou thankyou
thankyou.

(Ps Dim rng as Range was missing)

Really do appreciate this - you're a little excel angel in disguise!

Macro to sum sheets in a folder
 

Hi Keri

You are welcome

Thanks for the feedback

(Ps Dim rng as Range was missing)
Sorry I not compile it

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"keri" wrote in message oups.com...
ABSOLUTELY AMAZING!
Thankyou ever so much for your time & effort - this now works like a
dream. I am just going to add in some bits (so user can point to the
folder where the orders are stored etc). But thankyou thankyou thankyou
thankyou.

(Ps Dim rng as Range was missing)

Really do appreciate this - you're a little excel angel in disguise!