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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chris - 26 Nov 2007 02:04 GMT

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

Reply to this Message

carlo - 26 Nov 2007 02:25 GMT

> 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
[quoted text clipped - 8 lines]
>
> 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

Reply to this Message

Narasimha - 26 Nov 2007 02:50 GMT

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

> > 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
[quoted text clipped - 42 lines]
>
> Carlo

Reply to this Message

carlo - 26 Nov 2007 03:12 GMT

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

Reply to this Message

Narasimha - 26 Nov 2007 03:50 GMT

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

> Hi Narasimha, i didn't find your other post.
> What you are looking for is probably the indirect function
[quoted text clipped - 33 lines]
>
> hth carlo

Reply to this Message

JLGWhiz - 26 Nov 2007 03:41 GMT

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

http://www.exceltip.com/st/Getting_Values_From_A_Closed_Workbook_using_VBA_in_Mi
crosoft_Excel/357.html

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

> Hi Carlo, could you please explain briefly because this could be useful to me
> or
[quoted text clipped - 61 lines]
> >
> > Carlo

Reply to this Message

Chris - 27 Nov 2007 02:02 GMT

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

Reply to this Message