reutrn a value from a cell in each workbook filename from another
 

hi guys. i was able to make the first step, thanks to the discussion
groups... i was able to make a list of all files located in a folder in a
single sheet (column A). now, i need to find, in each file from column A, a
certain cell within the file...

like this... in each file, i have a cell that i write as "GRAND TOTAL", and
i need to get that value, which is the one right beside the grand total cell.
i'm trying to make a summary of all computation files in that folder for
reports, wherein i just open the summary file, and everything will be
there....

how to do this?i can't figure this out...

reutrn a value from a cell in each workbook filename from another
 

use this code. Filenames are in column A of Sheet1. GRAND TOTAL is also in
sheet1. change tthe sheet names if necessary.

Sub Gettotals()

With ThisWorkbook.Sheets("Sheet1")
LastRow = .Cells(Rows.Count).End(xlUp).Row
Set FileNames = .Range("A1:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="GRAND TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
End If
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
ActiveWorkbook.Close
Next Cell

End Sub


"Chris" wrote:

hi guys. i was able to make the first step, thanks to the discussion
groups... i was able to make a list of all files located in a folder in a
single sheet (column A). now, i need to find, in each file from column A, a
certain cell within the file...

like this... in each file, i have a cell that i write as "GRAND TOTAL", and
i need to get that value, which is the one right beside the grand total cell.
i'm trying to make a summary of all computation files in that folder for
reports, wherein i just open the summary file, and everything will be
there....

how to do this?i can't figure this out...

reutrn a value from a cell in each workbook filename from anot
 

i copied the code to the sheet module, but nothing happened. i tried running
the second code, and an error returned. "application error:1004,
application-defined or object-defined error"

my actual code now looks like this:

Sub myDIR()
myFolder = Range("A1").Value
x = 1
y = 1
Range("A2").Select
Selection = Dir(myFolder)
Do While y < ""
y = Dir
Selection.Offset(x, 0).Value = y
x = x + 1
Loop
End Sub

Sub Gettotals()

With ThisWorkbook.Sheets("Sheet1")
LastRow = .Cells(Rows.Count).End(xlUp).Row
Set FileNames = .Range("A1:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="GRAND TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
End If
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
ActiveWorkbook.Close
Next Cell

End Sub

what now?

"Joel" wrote:

use this code. Filenames are in column A of Sheet1. GRAND TOTAL is also in
sheet1. change tthe sheet names if necessary.

Sub Gettotals()

With ThisWorkbook.Sheets("Sheet1")
LastRow = .Cells(Rows.Count).End(xlUp).Row
Set FileNames = .Range("A1:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="GRAND TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
End If
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
ActiveWorkbook.Close
Next Cell

End Sub

reutrn a value from a cell in each workbook filename from anot
 

I made some minor changes. The code needed "myfolder" to open the workbooks.
Your code only had the filename and not the path. From you code it looks
like in row 1 your have something like c:\temp\test. there is no slash at
the end of the folder name.

Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="GRAND TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
Next Cell

End Sub


"Chris" wrote:

i copied the code to the sheet module, but nothing happened. i tried running
the second code, and an error returned. "application error:1004,
application-defined or object-defined error"

my actual code now looks like this:

Sub myDIR()
myFolder = Range("A1").Value
x = 1
y = 1
Range("A2").Select
Selection = Dir(myFolder)
Do While y < ""
y = Dir
Selection.Offset(x, 0).Value = y
x = x + 1
Loop
End Sub

Sub Gettotals()

With ThisWorkbook.Sheets("Sheet1")
LastRow = .Cells(Rows.Count).End(xlUp).Row
Set FileNames = .Range("A1:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="GRAND TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
End If
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
ActiveWorkbook.Close
Next Cell

End Sub

what now?

"Joel" wrote:

use this code. Filenames are in column A of Sheet1. GRAND TOTAL is also in
sheet1. change tthe sheet names if necessary.

Sub Gettotals()

With ThisWorkbook.Sheets("Sheet1")
LastRow = .Cells(Rows.Count).End(xlUp).Row
Set FileNames = .Range("A1:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="GRAND TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
End If
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
ActiveWorkbook.Close
Next Cell

End Sub

reutrn a value from a cell in each workbook filename from anot
 

nothing happened... i changed the code a bit though, since i was using
"total" instead of "grand total", and "sheet1" i changed to "production
cost", which is my sheet name...

nothing happened, the only comforting difference right now is that i don't
have to bother putting a "\" after the A1 cell for the first macro to run...?
my code now looks like this....

Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
Next Cell

End Sub


"Joel" wrote:

I made some minor changes. The code needed "myfolder" to open the workbooks.
Your code only had the filename and not the path. From you code it looks
like in row 1 your have something like c:\temp\test. there is no slash at
the end of the folder name.

Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="GRAND TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
Next Cell

End Sub

reutrn a value from a cell in each workbook filename from anot
 

the two subroutines are independant which means myDIR doesn't call Gettotal.
Get total must be run by itself. This seems to be the only logically reason
for the code not to run except if "grand total" is not on the worksheet. Any
other problem would of created an error. Do you see any workbooks openning
and closing when the code is running?

The default mode for "find" is to ignore case so this isn't the problem.
One other possiblity if there are leading or trailing blank in the cell with
"grand total". You may want to make the following change.

from:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
to:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues,LookAt:=xlPart)

You colud add a break point by click on the different lines in the code and
then hitting F9. You can also single step through the code by pressing F8.



"Chris" wrote:

nothing happened... i changed the code a bit though, since i was using
"total" instead of "grand total", and "sheet1" i changed to "production
cost", which is my sheet name...

nothing happened, the only comforting difference right now is that i don't
have to bother putting a "\" after the A1 cell for the first macro to run...?
my code now looks like this....

Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
Next Cell

End Sub


"Joel" wrote:

I made some minor changes. The code needed "myfolder" to open the workbooks.
Your code only had the filename and not the path. From you code it looks
like in row 1 your have something like c:\temp\test. there is no slash at
the end of the folder name.

Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="GRAND TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
Next Cell

End Sub

reutrn a value from a cell in each workbook filename from anot
 

i used F8, and found some strange things... first of all, i keep being asked
that the summary.xls file is already open(which is the file i opened for the
list), so what i did was put the summary.xls file into a folder, that makes
the macro unable to list that filename... then, the master file, the one
with no total value, i made into a hidden attribute.ok. after that, i tried
running F8, and it so happens that the first macro runs, but the second,
which is the one that's supposed to get the values, doesn't... so i jumped to
that line, and started the F8, and it works... so now my problem is, why
doesn't the macro run from the first code, to the second code when i open the
file?

"Joel" wrote:

the two subroutines are independant which means myDIR doesn't call Gettotal.
Get total must be run by itself. This seems to be the only logically reason
for the code not to run except if "grand total" is not on the worksheet. Any
other problem would of created an error. Do you see any workbooks openning
and closing when the code is running?

The default mode for "find" is to ignore case so this isn't the problem.
One other possiblity if there are leading or trailing blank in the cell with
"grand total". You may want to make the following change.

from:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
to:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues,LookAt:=xlPart)

You colud add a break point by click on the different lines in the code and
then hitting F9. You can also single step through the code by pressing F8.



"Chris" wrote:

nothing happened... i changed the code a bit though, since i was using
"total" instead of "grand total", and "sheet1" i changed to "production
cost", which is my sheet name...

nothing happened, the only comforting difference right now is that i don't
have to bother putting a "\" after the A1 cell for the first macro to run...?
my code now looks like this....

Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
Next Cell

End Sub

reutrn a value from a cell in each workbook filename from anot
 

i used F8, and found some strange things... first of all, i keep being asked
that the summary.xls file is already open(which is the file i opened for the
list), so what i did was put the summary.xls file into a folder, that makes
the macro unable to list that filename... then, the master file, the one
with no total value, i made into a hidden attribute.ok. after that, i tried
running F8, and it so happens that the first macro runs, but the second,
which is the one that's supposed to get the values, doesn't... so i jumped to
that line, and started the F8, and it works... so now my problem is, why
doesn't the macro run from the first code, to the second code when i open the
file?

"Joel" wrote:

the two subroutines are independant which means myDIR doesn't call Gettotal.
Get total must be run by itself. This seems to be the only logically reason
for the code not to run except if "grand total" is not on the worksheet. Any
other problem would of created an error. Do you see any workbooks openning
and closing when the code is running?

The default mode for "find" is to ignore case so this isn't the problem.
One other possiblity if there are leading or trailing blank in the cell with
"grand total". You may want to make the following change.

from:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
to:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues,LookAt:=xlPart)

You colud add a break point by click on the different lines in the code and
then hitting F9. You can also single step through the code by pressing F8.



"Chris" wrote:

nothing happened... i changed the code a bit though, since i was using
"total" instead of "grand total", and "sheet1" i changed to "production
cost", which is my sheet name...

nothing happened, the only comforting difference right now is that i don't
have to bother putting a "\" after the A1 cell for the first macro to run...?
my code now looks like this....

Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
Next Cell

End Sub

reutrn a value from a cell in each workbook filename from anot
 

i used F8, and found some strange things... first of all, i keep being asked
that the summary.xls file is already open(which is the file i opened for the
list), so what i did was put the summary.xls file into a folder, that makes
the macro unable to list that filename... then, the master file, the one
with no total value, i made into a hidden attribute.ok. after that, i tried
running F8, and it so happens that the first macro runs, but the second,
which is the one that's supposed to get the values, doesn't... so i jumped to
that line, and started the F8, and it works... so now my problem is, why
doesn't the macro run from the first code, to the second code when i open the
file?

"Joel" wrote:

the two subroutines are independant which means myDIR doesn't call Gettotal.
Get total must be run by itself. This seems to be the only logically reason
for the code not to run except if "grand total" is not on the worksheet. Any
other problem would of created an error. Do you see any workbooks openning
and closing when the code is running?

The default mode for "find" is to ignore case so this isn't the problem.
One other possiblity if there are leading or trailing blank in the cell with
"grand total". You may want to make the following change.

from:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
to:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues,LookAt:=xlPart)

You colud add a break point by click on the different lines in the code and
then hitting F9. You can also single step through the code by pressing F8.



"Chris" wrote:

nothing happened... i changed the code a bit though, since i was using
"total" instead of "grand total", and "sheet1" i changed to "production
cost", which is my sheet name...

nothing happened, the only comforting difference right now is that i don't
have to bother putting a "\" after the A1 cell for the first macro to run...?
my code now looks like this....

Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
Next Cell

End Sub

reutrn a value from a cell in each workbook filename from anot
 

i used F8, and found some strange things... first of all, i keep being asked
that the summary.xls file is already open(which is the file i opened for the
list), so what i did was put the summary.xls file into a folder, that makes
the macro unable to list that filename... then, the master file, the one
with no total value, i made into a hidden attribute.ok. after that, i tried
running F8, and it so happens that the first macro runs, but the second,
which is the one that's supposed to get the values, doesn't... so i jumped to
that line, and started the F8, and it works... so now my problem is, why
doesn't the macro run from the first code, to the second code when i open the
file?

"Joel" wrote:

the two subroutines are independant which means myDIR doesn't call Gettotal.
Get total must be run by itself. This seems to be the only logically reason
for the code not to run except if "grand total" is not on the worksheet. Any
other problem would of created an error. Do you see any workbooks openning
and closing when the code is running?

The default mode for "find" is to ignore case so this isn't the problem.
One other possiblity if there are leading or trailing blank in the cell with
"grand total". You may want to make the following change.

from:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
to:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues,LookAt:=xlPart)

You colud add a break point by click on the different lines in the code and
then hitting F9. You can also single step through the code by pressing F8.



"Chris" wrote:

nothing happened... i changed the code a bit though, since i was using
"total" instead of "grand total", and "sheet1" i changed to "production
cost", which is my sheet name...

nothing happened, the only comforting difference right now is that i don't
have to bother putting a "\" after the A1 cell for the first macro to run...?
my code now looks like this....

Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
Next Cell

End Sub

reutrn a value from a cell in each workbook filename from anot
 

i used F8, and found some strange things... first of all, i keep being asked
that the summary.xls file is already open(which is the file i opened for the
list), so what i did was put the summary.xls file into a folder, that makes
the macro unable to list that filename... then, the master file, the one
with no total value, i made into a hidden attribute.ok. after that, i tried
running F8, and it so happens that the first macro runs, but the second,
which is the one that's supposed to get the values, doesn't... so i jumped to
that line, and started the F8, and it works... so now my problem is, why
doesn't the macro run from the first code, to the second code when i open the
file?

"Joel" wrote:

the two subroutines are independant which means myDIR doesn't call Gettotal.
Get total must be run by itself. This seems to be the only logically reason
for the code not to run except if "grand total" is not on the worksheet. Any
other problem would of created an error. Do you see any workbooks openning
and closing when the code is running?

The default mode for "find" is to ignore case so this isn't the problem.
One other possiblity if there are leading or trailing blank in the cell with
"grand total". You may want to make the following change.

from:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
to:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues,LookAt:=xlPart)

You colud add a break point by click on the different lines in the code and
then hitting F9. You can also single step through the code by pressing F8.



"Chris" wrote:

nothing happened... i changed the code a bit though, since i was using
"total" instead of "grand total", and "sheet1" i changed to "production
cost", which is my sheet name...

nothing happened, the only comforting difference right now is that i don't
have to bother putting a "\" after the A1 cell for the first macro to run...?
my code now looks like this....

Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
Next Cell

End Sub

reutrn a value from a cell in each workbook filename from anot
 

i used F8, and found some strange things... first of all, i keep being asked
that the summary.xls file is already open(which is the file i opened for the
list), so what i did was put the summary.xls file into a folder, that makes
the macro unable to list that filename... then, the master file, the one
with no total value, i made into a hidden attribute.ok. after that, i tried
running F8, and it so happens that the first macro runs, but the second,
which is the one that's supposed to get the values, doesn't... so i jumped to
that line, and started the F8, and it works... so now my problem is, why
doesn't the macro run from the first code, to the second code when i open the
file?

"Joel" wrote:

the two subroutines are independant which means myDIR doesn't call Gettotal.
Get total must be run by itself. This seems to be the only logically reason
for the code not to run except if "grand total" is not on the worksheet. Any
other problem would of created an error. Do you see any workbooks openning
and closing when the code is running?

The default mode for "find" is to ignore case so this isn't the problem.
One other possiblity if there are leading or trailing blank in the cell with
"grand total". You may want to make the following change.

from:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
to:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues,LookAt:=xlPart)

You colud add a break point by click on the different lines in the code and
then hitting F9. You can also single step through the code by pressing F8.



"Chris" wrote:

nothing happened... i changed the code a bit though, since i was using
"total" instead of "grand total", and "sheet1" i changed to "production
cost", which is my sheet name...

nothing happened, the only comforting difference right now is that i don't
have to bother putting a "\" after the A1 cell for the first macro to run...?
my code now looks like this....

Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
Next Cell

End Sub

reutrn a value from a cell in each workbook filename from anot
 

I made a few changes to the code. Like I said yeasterday the 1st macro
didn't call the 2nd.

1) add call from 1st macro to second
2) changed grand total to total
3) add if conndition to ingore the file summary.xls (Thisworkbook.name)



Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" and _
Filename < Thisworkbook.name Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
call Gettotals
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
if Cell < Thisworkbook.name then
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="total", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
end if
Next Cell
End Sub

"Chris" wrote:

i used F8, and found some strange things... first of all, i keep being asked
that the summary.xls file is already open(which is the file i opened for the
list), so what i did was put the summary.xls file into a folder, that makes
the macro unable to list that filename... then, the master file, the one
with no total value, i made into a hidden attribute.ok. after that, i tried
running F8, and it so happens that the first macro runs, but the second,
which is the one that's supposed to get the values, doesn't... so i jumped to
that line, and started the F8, and it works... so now my problem is, why
doesn't the macro run from the first code, to the second code when i open the
file?

"Joel" wrote:

the two subroutines are independant which means myDIR doesn't call Gettotal.
Get total must be run by itself. This seems to be the only logically reason
for the code not to run except if "grand total" is not on the worksheet. Any
other problem would of created an error. Do you see any workbooks openning
and closing when the code is running?

The default mode for "find" is to ignore case so this isn't the problem.
One other possiblity if there are leading or trailing blank in the cell with
"grand total". You may want to make the following change.

from:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
to:
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues,LookAt:=xlPart)

You colud add a break point by click on the different lines in the code and
then hitting F9. You can also single step through the code by pressing F8.



"Chris" wrote:

nothing happened... i changed the code a bit though, since i was using
"total" instead of "grand total", and "sheet1" i changed to "production
cost", which is my sheet name...

nothing happened, the only comforting difference right now is that i don't
have to bother putting a "\" after the A1 cell for the first macro to run...?
my code now looks like this....

Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="TOTAL", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
Next Cell

End Sub

reutrn a value from a cell in each workbook filename from anot
 

wow,it worked great!!! thanks joel. one question, if you don't mind, is it
possible that we make the file open silently? or at least not ask me if i
want to save the changes or not everytime each workbook is opened?

"Joel" wrote:

I made a few changes to the code. Like I said yeasterday the 1st macro
didn't call the 2nd.

1) add call from 1st macro to second
2) changed grand total to total
3) add if conndition to ingore the file summary.xls (Thisworkbook.name)



Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" and _
Filename < Thisworkbook.name Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
call Gettotals
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
if Cell < Thisworkbook.name then
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="total", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
end if
Next Cell
End Sub

reutrn a value from a cell in each workbook filename from anot
 

Most time I use this type code there is no problem in closing the worksheet
because nothing gets changed in the OPEN workbook. I your case there must be
either some links or a time/date function that automatically updates the
workbook. You need to add Savechanges:=False. If you need to save the
changes then change false to true.

from
ActiveWorkbook.Close
to
ActiveWorkbook.Close SaveChanges:=False


"Chris" wrote:

wow,it worked great!!! thanks joel. one question, if you don't mind, is it
possible that we make the file open silently? or at least not ask me if i
want to save the changes or not everytime each workbook is opened?

"Joel" wrote:

I made a few changes to the code. Like I said yeasterday the 1st macro
didn't call the 2nd.

1) add call from 1st macro to second
2) changed grand total to total
3) add if conndition to ingore the file summary.xls (Thisworkbook.name)



Sub myDIR()
myfolder = Range("A1").Value
RowCount = 2
First = True
Do
If First = True Then
Filename = Dir(myfolder & "\*.xls")
First = False
Else
Filename = Dir()
End If
If Filename < "" and _
Filename < Thisworkbook.name Then
Range("A" & RowCount) = Filename
RowCount = RowCount + 1
End If
Loop While Filename < ""
call Gettotals
End Sub

Sub Gettotals()
myfolder = Range("A1").Value
With ThisWorkbook.ActiveSheet
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
Set FileNames = .Range("A2:A" & LastRow)
End With
For Each Cell In FileNames
if Cell < Thisworkbook.name then
Workbooks.Open Filename:=myfolder & "\" & Cell
Set sht = ActiveWorkbook.Sheets("Sheet1").Cells
Set c = sht.Find(what:="total", LookIn:=xlValues)
If Not c Is Nothing Then
total = c.Offset(rowoffset:=0, columnoffset:=1)
Cell.Offset(rowoffset:=0, columnoffset:=1) = total
End If

ActiveWorkbook.Close
end if
Next Cell
End Sub

reutrn a value from a cell in each workbook filename from anot
 

yup... it works!!! great!... thank a lot joel. thank you so much for your
kindness and patience in helping me about this.

"Joel" wrote:

Most time I use this type code there is no problem in closing the worksheet
because nothing gets changed in the OPEN workbook. I your case there must be
either some links or a time/date function that automatically updates the
workbook. You need to add Savechanges:=False. If you need to save the
changes then change false to true.

from
ActiveWorkbook.Close
to
ActiveWorkbook.Close SaveChanges:=False