Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from all .xls files which are open
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from all .xls files which are open
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 BZeyger wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from all .xls files which are open
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 "BZeyger" wrote: 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from all .xls files which are open
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 -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "BZeyger" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from all .xls files which are open
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. "Michael" wrote: 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 -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "BZeyger" wrote: 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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Getting info from all .xls files which are open
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 "BZeyger" wrote: 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. "Michael" wrote: 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 -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "BZeyger" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to change default Open/Files of Type to "Microsoft Excel Files | Excel Discussion (Misc queries) | |||
HOW TO GET OVER WITH THIS ANNOYING LINKED FILES MSG BOX WHEN I OPEN FILES WHICH HAS LINKS | Excel Programming | |||
Info on xml files | Excel Programming | |||
Macro to open *.dat files and save as .txt (comma delimited text files) | Excel Programming | |||
file open via IE hyperlink causes already open files to shrink and tile | Setting up and Configuration of Excel |