Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Data Extract
I'm not even sure this can be done, but I think is should
be possible. Can you open a mutiple sheet/tab workbook and search each sheet for rows with a specific value in column A, and extract each of these rows to a text file? What if you don't know don't know how many sheets there are in the workbook, or if the names of the sheets are not standardized? Any assistance would be greatly appreciated... Thanks, Sean |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Data Extract
for each sh in Thisworkbook.worksheets
set rng = sh.Cells.Find(What:="Target") faddr = rng.Address if not rng is nothing then ' write to text file set rng = sh.Cells.FindNext(rng) Loop until rng.Address = faddr Next I doubt you want to write the target string in a text file N times, so I can't say what you should do once the cell is found in each case. See help on the Find Method for more arguments that might affect your search. See this article for help on writting to a text file (or you could write the results to a worksheet in a new workbook and then do a SaveAs with a text fileformat. http://support.microsoft.com/support...eio/fileio.asp File Access with Visual Basic® for Applications -- Regards, Tom Ogilvy "Sean Evanovich" wrote in message ... I'm not even sure this can be done, but I think is should be possible. Can you open a mutiple sheet/tab workbook and search each sheet for rows with a specific value in column A, and extract each of these rows to a text file? What if you don't know don't know how many sheets there are in the workbook, or if the names of the sheets are not standardized? Any assistance would be greatly appreciated... Thanks, Sean |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Data Extract
Thanks Tom...that seemed to do the trick...I modified it
slightly to look like this...WriteRecord sends my row to a text file... Public Sub macro1() For Each sh In ThisWorkbook.Worksheets With sh.Range("a1:a999") Set rng = .Find("A ") If rng Is Nothing Then GoTo None_Found Else faddr = rng.Address Do WriteRecord Set rng = sh.Cells.FindNext(rng) Loop Until rng.Address = faddr End If End With None_Found: Next -----Original Message----- for each sh in Thisworkbook.worksheets set rng = sh.Cells.Find(What:="Target") faddr = rng.Address if not rng is nothing then ' write to text file set rng = sh.Cells.FindNext(rng) Loop until rng.Address = faddr Next I doubt you want to write the target string in a text file N times, so I can't say what you should do once the cell is found in each case. See help on the Find Method for more arguments that might affect your search. See this article for help on writting to a text file (or you could write the results to a worksheet in a new workbook and then do a SaveAs with a text fileformat. http://support.microsoft.com/support...content/fileio /fileio.asp File Access with Visual Basic® for Applications -- Regards, Tom Ogilvy "Sean Evanovich" wrote in message ... I'm not even sure this can be done, but I think is should be possible. Can you open a mutiple sheet/tab workbook and search each sheet for rows with a specific value in column A, and extract each of these rows to a text file? What if you don't know don't know how many sheets there are in the workbook, or if the names of the sheets are not standardized? Any assistance would be greatly appreciated... Thanks, Sean . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data extract | Excel Worksheet Functions | |||
Tying to extract all data from a pivot pulling from external data | Excel Discussion (Misc queries) | |||
data extract | Excel Worksheet Functions | |||
How to extract the data | Excel Worksheet Functions |