Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to find Cell Range in Files in Folder, return value
A specific workbook or are you talking about opening all workbooks and
looking for the value. If the latter, you should be able to modify the code you already have. Again, you have talked about a cell address in a workbook, but haven't said what sheet to look on. -- Regards, Tom Ogilvy "JavyD" wrote in message ... Hello guys, Is it possible to create a VBA for my existing file, look at the Target in say CEll A1, then look into work books in a folder, find that Target in a work book, and bring back a value that would be in cell B25? Is this possible? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA to find Cell Range in Files in Folder, return value
Sub Quote()
Dim mybook As Workbook Dim FNames As String Dim MyPath As String Dim SaveDriveDir As String Dim sTarget as String Dim rng as Range Dim sh as worksheet set sh = Workbooks("June.xls").Worksheets(1) sTarget = sh.Range("A1").Value SaveDriveDir = CurDir MyPath = "C:\Documents and Settings\Javier\My Documents\Test\" ChDrive MyPath ChDir MyPath FNames = Dir("*.xls") If Len(FNames) = 0 Then MsgBox "No files in the Directory" ChDrive SaveDriveDir ChDir SaveDriveDir Exit Sub End If Application.ScreenUpdating = False Do While FNames < "" Set mybook = Workbooks.Open(Filename:=FNames, _ UpdateLinks:=0) set rng = myBook.Worksheets(1).Cells.Find(sTarget) if not rng is nothing then sh.Range("B1").Value = rng.Parent.Range("B25").Value mybook.close SaveChanges:=False exit do end if mybook.Close False FNames = Dir() Loop ChDrive SaveDriveDir ChDir SaveDriveDir Application.ScreenUpdating = True End Sub -- Regards, Tom Ogilvy "JavyD" wrote in message ... Hey Tom, long time no see. LOL. No specific file, all .xls in a folder, say my docs. But this time, I want it to target a specific value. Say I want the VBA to look at my file, say June. I want it to see A1 in June Sheet 1, look at its value, look for that value in a folder in all xls, once it finds a work book with sheet 1 with that value, which always is in the same range if that helps, I want it to bring back a value that is in another range in the found work book, and imported back to cell B1 in June "Tom Ogilvy" wrote: A specific workbook or are you talking about opening all workbooks and looking for the value. If the latter, you should be able to modify the code you already have. Again, you have talked about a cell address in a workbook, but haven't said what sheet to look on. -- Regards, Tom Ogilvy "JavyD" wrote in message ... Hello guys, Is it possible to create a VBA for my existing file, look at the Target in say CEll A1, then look into work books in a folder, find that Target in a work book, and bring back a value that would be in cell B25? Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find data in Range, Return Cell Reference | Excel Discussion (Misc queries) | |||
How to find all folder and/or files in a specific location | Excel Discussion (Misc queries) | |||
find the first blank cell in a range and return me it's position | Links and Linking in Excel | |||
Copy several range from all files in folder into several worksheets | Excel Programming | |||
how to step thru only one subfolder of a folder to find like files | Excel Programming |