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

[Chris]

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

 

[carlo]

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

Hi Chris

here's a short code, maybe it helps you figure out a way that works
for you:

Sub copy_cell()

Dim main_wb As Workbook
Dim xl_wb As Workbook

Set main_wb = ActiveWorkbook

For i = 1 To Cells(65536, 1).End(xlUp).Row

Set xl_wb = Workbooks.Open(main_wb.Path & "\" &
main_wb.Sheets(1).Cells(i, 1))
main_wb.Sheets(1).Cells(i, 2).Value = xl_wb.Sheets(1).Cells(8,
2).Value
xl_wb.Close (False)

Next i

End Sub

Cells(8,2) is where your grand total is located, you can also
substitute
this with Range("B8") or Range(yourdefinedRangeName).

Be careful when copying the code, it will maybe split up some lines!

hth

Carlo

 

[Narasimha]

Hi Carlo, could you please explain briefly because this could be useful to me
or
please go through the question which I was posted few hours ago in excel
general questions at 4.21pm subjected reference
"Hi all ,
I am basic excel user.
I have some excel files, say Book1,Book2 etc in C:\Temp folder.
In another excel file say Book3 , When I enter Book1 or Book2 in cell A1 ,
the cell value of Book1 or Book2 respectively should come in cell which the
formula has.
I tried with concatenate but of no use (path of excel in Column A and file
name in Column B)
Could anyone please help me ?
Thanks in advance.
Narasimha"

thank you in advance
Narasimha

 

[carlo]

Hi Narasimha, i didn't find your other post.
What you are looking for is probably the indirect function
=INDIRECT(A1 & ".xls!B1")

In A1 you put your bookname for example "test"
then excel goes and looks in test.xls!B1 and returns you
the value of that cell.

That only works, if the other book is open, otherwise you
have to do it by VBA.
You may want to do this everytime the cell A1 changes:
right click on the worksheet-tab and "view code"
there you enter following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Err_WB

If Target.Address = "$A$1" Then
Dim main_wb As Workbook
Dim xl_wb As Workbook

Set main_wb = ActiveWorkbook

Set xl_wb = Workbooks.Open("C:\temp\" &
main_wb.Sheets(1).Range("A1"))
main_wb.Sheets(1).Range("B1").Value =
xl_wb.Sheets(1).Range("A1").Value
xl_wb.Close (False)
End If

Exit Sub

Err_WB:
MsgBox "Couldn't find the Workbook, check your spelling"
End Sub

hth carlo

 

[JLGWhiz]

Hi Narasimha, take a look at this site and see if this is what you want to do.

http://www.exceltip.com/st/Getting_...orkbook_using_VBA_in_Microsoft_Excel/357.html

Be sure you get the whole address, including line wrap.

 

[Narasimha]

Thank you very much for VBA code. It is working for only cell A1 , I will be
greatful to you if you help me for range of cells and more than one file
because I have files book1,book2 etc
once again thank you very much

 

[Chris]

i copied the code from joel 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 11/26/2007 3:37 AM PST

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