Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract workbook information
I want to programmically extract certain information from all workbooks in a
folder, always in the same place on the same sheet in each workbook. The result will look like this: Bookname Sales Profit Book1 999 999 Book2 999 999 etc. assuming Sales and Profit are in cells c10 and g10 of the sheet named "P&L". |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract workbook information
One way. Set up formulas like this in col b and col c for an already known
file name. Then have a list of the files in col a and use the macro to change all. =[MENU.xls]Off2007!$a$2 Sub changefilenames() For Each c In Range("h6:h7") c.Replace "ok", c.Offset(, -1) Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Joe" wrote in message ... I want to programmically extract certain information from all workbooks in a folder, always in the same place on the same sheet in each workbook. The result will look like this: Bookname Sales Profit Book1 999 999 Book2 999 999 etc. assuming Sales and Profit are in cells c10 and g10 of the sheet named "P&L". |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract workbook information
Joe,
This assumes every file in the directory you choose will have a sheet called P&L. Sub LoopThroughDirectory() Application.DisplayAlerts = False 'Change this to your directory MyPath = "C:\" ActiveFile = Dir(MyPath & "*.xls") Do While ActiveFile < "" Workbooks.Open Filename:=MyPath & ActiveFile BkName = ActiveWorkbook.Name ActiveWorkbook.Sheets("P&L").Range("C10,G10").Copy ActiveWorkbook.Close False lastrow = ThisWorkbook.Sheets("Sheet1").Cells(Cells.Rows.Cou nt, "A").End(xlUp).Row + 1 ThisWorkbook.Sheets("Sheet1").Cells(lastrow, 1) = BkName ThisWorkbook.Sheets("Sheet1").Cells(lastrow, 1).Offset(, 1).PasteSpecial ActiveFile = Dir() Loop Application.DisplayAlerts = True End Sub -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Joe" wrote: I want to programmically extract certain information from all workbooks in a folder, always in the same place on the same sheet in each workbook. The result will look like this: Bookname Sales Profit Book1 999 999 Book2 999 999 etc. assuming Sales and Profit are in cells c10 and g10 of the sheet named "P&L". |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Extract workbook information
Try some code like the following:
Sub AAA() Dim Dest As Range Dim FName As String Dim Path As String Dim WB As Workbook Dim WS As Worksheet Set Dest = ThisWorkbook.Worksheets("Sheet1").Range("A1") '<<< CHANGE Path = "D:\Temp" '<<< CHANGE ChDrive Path ChDir Path FName = Dir("*.xls") Do Until FName = vbNullString Set WB = Workbooks.Open(Filename:=FName) Set WS = WB.Worksheets("P&L") Dest(1, 1).Value = WB.Name Dest(1, 2).Value = WS.Range("C10") Dest(1, 3).Value = WS.Range("G10") Set Dest = Dest(2, 1) WB.Close savechanges:=False FName = Dir() Loop End Sub Change the lines marked with '<<< to meet your needs. Dest is the cell at which the list of extracted values will begin. Path is the folder name that conatins the workbooks whose contents you want to extract. Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Thu, 20 May 2010 06:59:02 -0700, Joe wrote: I want to programmically extract certain information from all workbooks in a folder, always in the same place on the same sheet in each workbook. The result will look like this: Bookname Sales Profit Book1 999 999 Book2 999 999 etc. assuming Sales and Profit are in cells c10 and g10 of the sheet named "P&L". |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to extract information from one table to another one | Excel Discussion (Misc queries) | |||
Extract Information to another worksheet | Excel Worksheet Functions | |||
Extract information from the list | Excel Worksheet Functions | |||
Extract information from worksheet | Excel Discussion (Misc queries) | |||
Please help: Extract some information from a cell | Excel Discussion (Misc queries) |