Getting info from all .xls files which are open

[BZeyger]

I want to open a .xls file named "INFO". This is a blank .xls worksheet. I
what to run a macro that will retreive information from all open .xls files,
(names not specified).

The user would open all the .xls files they want the information from. It
could be 6 or more different .xls files at one time. The macro would cycle
through each .xls file and retreive cell "a1", for example.
I would like to put the contents of all the "a1" cells into the "INFO" sheet.

Is this possible? To retreive info from all open .xls files without knowing
the names of the files. The user just has them open.

 

[JW]

Yes, it is possible. But, you need to provide more info. Which sheet
in all of the open workbooks do you want to get the values from? Only
visible workbooks? Tweak as needed:
This will cycle through all of the open, visible workbooks and popup a
message box with that workbooks name.
Sub tester()
Dim wb As Workbook
For Each wb In Workbooks
If Windows(wb.Name).Visible = True Then MsgBox wb.Name
Next wb
End Sub

 

[Keithlo]

Yes. There is a collection called the workbooks collection, which consists
of all open workbooks (Excel files). So you could have in your code
something like:

For each W in Workbooks
If W.Name <> "Name of file you're writing into.xls" then
W.activate
MyValue = Range("A1").value
'then code here to reselect the original workbook (file) and put in value
End if
Next W

You might be concerned with which sheet the value comes from, in which case
you would need to code for that. Or if it's just the active sheet in the
file, the above code would work for that. You will also want to have a
variable to contain the name of the workbook to put the data in, for example,
if the macro is launched from that workbook:

MyBook = activeworkbook.name

So that your macro would know which workbook to return to before it writes
in the value.

Also, some code to have the active cell advance down from it's present cell
after each write out of data, for example:

activecell.offset(1,0).select

One potential error to consider is if the macro is launched from the wrong
workbook. To avoid, you might hard code the filename to write into.

Hope this helps.

Keith

 

[Michael]

Try this:

Sub Test1()

Dim Wbk As Workbook
Dim wks As Worksheet

Set wks = Worksheets("INFO")
Set IP = wks.Range("A1")

For Each Wbk In Workbooks


If Wbk.Name <> "INFO.xls" Then
Wbk.Activate
IP.Value = Wbk.Sheets("Sheet1").Range("A1").Value
MsgBox (IP.Value)
Set IP = IP.Offset(1, 0)
End If

Next Wbk


End Sub

 

[BZeyger]

When I linked ths code to a macro, I received a runtime error 9...Script out
of range.

I have 2 excel files currently open with different names. They both contain
one worksheet named "ABC". I want to get feild A1 from both the open excel
files and place them into the open INFO.xls file. Info.xls is a blank excel
file that contains the the default worksheets (sheet1, sheet2, sheet3,
sheet4). I am tring to get the info from the A! fields of the other sheet to
fill down the row of the INFO.xls file.

I am having a hrad time getting around the error message.

 

[Keithlo]

I'm guessing that the reason you got that error is because you do not have a
sheet named "INFO". So the first thing I would try would be to change the
line

Set wks = Worksheets("INFO")

to

Set wks = Worksheets("Name of sheet you want to put your data into")

The original code is not mine, and I haven't reviewed or tested it, but the
error you're getting seems to indicate that the program can't find either a
sheet or a workbook that it is being told to look for.

Hope this helps.

Keith