Extracting data from workbooks. Help.

hello
I have a situation where there are several workbooks, of variable name,
contained within a folder on my hard drive. I have a master workbook in a
folder higher up the same directory tree that I want to use to gather
information from the other workbooks of variable names. The cell, A1, that I
am interested in is on sheet "OnCall" in each of the workbooks of variable
name. Is there any way that I can open the master workbook and then
automatically extract the data from cell A1 in each of the other variable
name workbooks? I hope that as clearer than mud?

hopefully

Tom



0
1/15/2004 1:26:45 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
527 Views

Similar Articles

[PageSpeed] 10

Here's a macro that will show you the File|Open dialog.  Go to your directory
and select as many (or as few workbooks) that you want to consolidate.

Option Explicit
Sub testme01()

    Application.ScreenUpdating = False
  
    Dim curWks As Worksheet
    Dim testWks As Worksheet
    Dim myFileNames As Variant
    Dim fCtr As Long
    Dim nextWkbk As Workbook
    Dim destCell As Range
        
    Set curWks = Workbooks.Add(1).Worksheets(1)
  
    myFileNames = Application.GetOpenFilename _
                        (FileFilter:="Excel files, *.xls", _
                        MultiSelect:=True)

    If IsArray(myFileNames) Then
        Application.ScreenUpdating = False
        With curWks
            Application.EnableEvents = False
            Set destCell = .Range("a1")
            For fCtr = LBound(myFileNames) To UBound(myFileNames)
                Set nextWkbk = Workbooks.Open(Filename:=myFileNames(fCtr), _
                        ReadOnly:=True, UpdateLinks:=0)
                        
                destCell.Value = nextWkbk.FullName
                Set testWks = Nothing
                On Error Resume Next
                Set testWks = nextWkbk.Worksheets("onCall")
                On Error GoTo 0
                
                If testWks Is Nothing Then
                    destCell.Offset(0, 1).Value = "Missing OnCall Worksheet!"
                Else
                    destCell.Offset(0, 1).Value = testWks.Range("a1").Value
                End If
                Set destCell = destCell.Offset(1, 0)
                
                nextWkbk.Close savechanges:=False
            Next fCtr
        End With
        Application.EnableEvents = True
    Else
        MsgBox "Ok, Quitting"
    End If
    
    Application.ScreenUpdating = True
    
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Tom Weston wrote:
> 
> hello
> I have a situation where there are several workbooks, of variable name,
> contained within a folder on my hard drive. I have a master workbook in a
> folder higher up the same directory tree that I want to use to gather
> information from the other workbooks of variable names. The cell, A1, that I
> am interested in is on sheet "OnCall" in each of the workbooks of variable
> name. Is there any way that I can open the master workbook and then
> automatically extract the data from cell A1 in each of the other variable
> name workbooks? I hope that as clearer than mud?
> 
> hopefully
> 
> Tom

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10082)
1/16/2004 1:58:05 AM
Dave
Thanks for your help, it works really well.

Tom


"Dave Peterson" <ec35720@msn.com> wrote in message
news:4007452D.1A443A7A@msn.com...
> Here's a macro that will show you the File|Open dialog.  Go to your
directory
> and select as many (or as few workbooks) that you want to consolidate.
>
> Option Explicit
> Sub testme01()
>
>     Application.ScreenUpdating = False
>
>     Dim curWks As Worksheet
>     Dim testWks As Worksheet
>     Dim myFileNames As Variant
>     Dim fCtr As Long
>     Dim nextWkbk As Workbook
>     Dim destCell As Range
>
>     Set curWks = Workbooks.Add(1).Worksheets(1)
>
>     myFileNames = Application.GetOpenFilename _
>                         (FileFilter:="Excel files, *.xls", _
>                         MultiSelect:=True)
>
>     If IsArray(myFileNames) Then
>         Application.ScreenUpdating = False
>         With curWks
>             Application.EnableEvents = False
>             Set destCell = .Range("a1")
>             For fCtr = LBound(myFileNames) To UBound(myFileNames)
>                 Set nextWkbk = Workbooks.Open(Filename:=myFileNames(fCtr),
_
>                         ReadOnly:=True, UpdateLinks:=0)
>
>                 destCell.Value = nextWkbk.FullName
>                 Set testWks = Nothing
>                 On Error Resume Next
>                 Set testWks = nextWkbk.Worksheets("onCall")
>                 On Error GoTo 0
>
>                 If testWks Is Nothing Then
>                     destCell.Offset(0, 1).Value = "Missing OnCall
Worksheet!"
>                 Else
>                     destCell.Offset(0, 1).Value =
testWks.Range("a1").Value
>                 End If
>                 Set destCell = destCell.Offset(1, 0)
>
>                 nextWkbk.Close savechanges:=False
>             Next fCtr
>         End With
>         Application.EnableEvents = True
>     Else
>         MsgBox "Ok, Quitting"
>     End If
>
>     Application.ScreenUpdating = True
>
> End Sub
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Tom Weston wrote:
> >
> > hello
> > I have a situation where there are several workbooks, of variable name,
> > contained within a folder on my hard drive. I have a master workbook in
a
> > folder higher up the same directory tree that I want to use to gather
> > information from the other workbooks of variable names. The cell, A1,
that I
> > am interested in is on sheet "OnCall" in each of the workbooks of
variable
> > name. Is there any way that I can open the master workbook and then
> > automatically extract the data from cell A1 in each of the other
variable
> > name workbooks? I hope that as clearer than mud?
> >
> > hopefully
> >
> > Tom
>
> --
>
> Dave Peterson
> ec35720@msn.com


0
1/16/2004 1:00:18 PM
Reply:

Similar Artilces:

extracting icons
Is there any way to extract an icon from an application? Take a look at the ExtractIcon[Ex]() API Cheers Check Abdoul --------------------- <davegreb@gpxinc.com> wrote in message news:c6c54da6-574c-4c8e-8ec9-3b77b99a5523@d70g2000hsb.googlegroups.com... > Is there any way to extract an icon from an application? ...

sort data
i am trying, unsuccessfullty, to sort some data (a small example is below) so I can graph it. I want to put it into a pivot table format but don't want calculations, just the actual data. any ideas??/ Location DATE Result WAT002WA ALBANY BOTTOM 19-Jul-05 0.83 WAT002WA ALBANY BOTTOM 23-Jun-05 0.8 WAT002WA ALBANY BOTTOM 28-May-05 1.16 WAT002WA ALBANY BOTTOM 02-May-05 0.93 WAT002WA ALBANY BOTTOM 06-Apr-05 0.66 WAT002WA ALBANY BOTTOM 11-Mar-05 0.59 WAT002WA ALBANY BOTTOM 13-Feb-05 0.54 WAT002WA ALBANY BOTTOM 18-Jan-05 0.62 WAT002WA ALBANY TOP 09-Mar-07 0.73 ...

Matching call data based on date, time and number called to give c
I have the following two tables on different sheets, both containing other information within them that is irrelevant in my question. SHEET 1 Date - Time - Number Called Duration Cost 01/02/10 09:05:21 01234123456 02:16 0.50 SHEET 2 Date - Time Number Called - Duration Extension 01/02/10 09:07:56 01234123456 02:15 1234 01/02/10 15:30:45 01234123456 01:59 1234 Sheet 2 HAS A LOT MORE RECORDS THAN SHEET 1 AND THERE ARE A NUMBER OF CALLS TO THE SAME NUM...

extracting totals from within a spreadsheet
I need to know how to take different information from within a spreadsheet --where two conditions/catogories must apply so that a third column where i have inputed hours, will total for those conditions only... can anyone help? Please spell out what you want we are not mind readers -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Jovetta" <Jovetta@discussions.microsoft.com> wrote in message news:91442C6D-03DF-4FC1-A069-AAB43FA57485@microsoft.com... >I need to know how to take different information from within a spreadsheet > --where two condi...

Excel: extract and sum numerals from mixed text/numeral cell range
I have a large (30x20) grid of cells with data, and I want to extract and then sum up certain numerals from this entire range. The catch is that the data is mixed numerals and text, as you'll see below. Here's an abbreviated 3x3 example, with a value in each of the nine cells: V7.1 T H P1 A T B V3 P4.5 If I just wanted to sum up the instances of "T" appearing, I could use COUNTIF() for the entire range to come up with answer ("T" appears 2 times). Easy enough. But, what I'm trying to accomplish is to sum up the numerals associated w...

Help with design structure
i want to create a database for my makeup shop. I know i would have a table for all the products but i want to also track customers who buys what product so that i can always inform them of new itmes. i want to now if i need just one table or i need more than than. my table will have fields for prices and total amount. this will be and invetory for the shop that should show what we have as orders and what is Purchase orders. Hi Can I make a suggestion. If you go to the microft site you can download (free) databases that will just what you want. Much better than spending a...

help needed, pls, with trusted sources
Hi - Using Outlook 2000 and XP Home- When going to print dymo labels from Outlook, I keep getting the window: There is a program trying to access, etc.. allow Y, N, x minutes...' Is there a way to set this as a trusted source? The latest version of this software does not supply a COM add-in, as it's supposedly not needed.... but, I guess something is needed.... Thank you! -- B'rgds, Vinnie Uncle Vinnie, you wrote on Tue, 27 Feb 2007 13:53:19 -0500: > When going to print dymo labels from Outlook, I keep getting the window: > There is a program trying to acce...

Sumif() help #2
Gurus, I'm stuck with this formula. I have a spreadsheet with two tabs. On one is a list of Standard Window Types in different houses and the quantity of that type of window for that house. The Window type can be repeated many times with different quantities. On my second tab I have a list of all the unique windows listed only once. I want to know the frequency that a window is used in all these projects. I'm using =SUMIF(Quantity!$A$2:$A$1567,Usage!A2,Quantity!$B$2:$B$1567). The funny thing is I've got a total of 3,213 windows in these projects but the sum if returns a ...

Need help moving Outlook folder
I'm using Outlook 2002. My primary email account uses IMAP and I added it to Outlook using <Tools> <Email Accounts ...> <Add a new email account>. I noticed that Outlook created a file for this account in the directory: C:\Documents and Settings\User\Local Settings\Application Data\Outlook This file obviously contains the email that has been downloaded from my email account, since the file size grows and shrinks as mail is added or deleted. However, I would like to move this file from its default location to another hard drive. The reason is that the other ha...

Extract emails from cells with text
I have a row in column A which includes an email address in the text that I'd like to extract to column B. Is there a formula I can use to accomplish extracting the email address only to column B? Here's an example of different cells in column A: Please email example@law.ufl.edu to contact us...... OR Schedule an appointment for assistance, or email example@uga.edu with your questions... Thank you Try this... All on one line: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND (" ",A1&" ",FIND("@",A1))-1)," ", REPT(" ",...

data migration of cdf tables to crm
hi, i am trying to implement crm for an organisation. the cdf tables for both accounts and contacts are ready and data is present int he info tables.but the next step of migration is not happening. executing the migration.exe tool shows process completed in the log but verifying the migration_info table shows "not processed" against the corresponding rows.it is not showing any errors but still not migrating. please help. i can use all the help i could get. thank you. "Yamini.P.S" <Yamini.P.S@discussions.microsoft.com> wrote in message news:94FD56E3-3B28-43D...

Appending data in cells that utilize a Dropdown list.
Hello all, I would like to append entries in cells that utilize a dropdown list. Currently, when I select (in this case names) from my dropdown list I cannot append them if I want to add more than one to a cell. If I select another name from the dropdown, it erases the first entry. Is there some way to append entries within cells that use this form of validation so that I can enter more than one in a cell from the dd list? -- Thanks, Geek using Office XP Professional Hi Geek Debra Dalgleish has an example workbook showing how to do this http://www.contextures.com/excelfiles.html...

sending data to com port
Hello, I am using VC++ 6.0 (of Visual studio 6.0) How can I send data to com port via VC++ code ? Need sample code, please. Thanks :) See this article on codeproject..it covers up most of the stuff... http://www.codeproject.com/system/serial_com.asp - Parhar "Eitan" <no_spam_please@nospam.com> wrote in message news:uVpJFP87DHA.1804@TK2MSFTNGP12.phx.gbl... > Hello, > I am using VC++ 6.0 (of Visual studio 6.0) > How can I send data to com port via VC++ code ? > Need sample code, please. > > Thanks :) > > See the "CSerial" class from Tom...

queries which pull data in multiple "rows" in one table and compar
I have a project to complete where the end user wants me to write several queries which pull data in multiple "rows" in one table and compare them to data in another using access. He also needs a report written based on the findings of which data meets certain criteria within those queries. I think the report part will be pretty easy, but can someone give me some suggestions on what would be the best way to create the queries in access? Thanks Da Chosen One On Sun, 10 Jan 2010 19:22:01 -0800, Da Chosen One <Da Chosen One@discussions.microsoft.com> wrote: You...

Help with a code
I have these code and it works find: but i also want to use it for different culumn within the same sheet. how do i add Column D with different target? meaning "S" for Study and "B" for books and s on... and maybe adding a Msg box to it that will only allow the Letters specify.. thank you in advance.. Private Sub Worksheet_Change(ByVal Target As Range) If Target = "" Then Exit Sub If Target.Count > 1 Then Exit Sub If Not Intersect(Target, Range("c6:c50")) Is Nothing Then If UCase(Target) = "C" Then Target = "Contribution" El...

Data............Group
if I group columns and hide the columns with the + is it possible to give the diferent groups names where they are listed at the top left 1, 2, 3. Not quite sure what you want, but maybe.... View|Custom Views would allow you to hide/show the groups the way you want. Esrei wrote: > > if I group columns and hide the columns with the + is it > possible to give the diferent groups names where they are > listed at the top left 1, 2, 3. -- Dave Peterson ...

help please with simple formula
F4 i have 0 H4 i have 6 J4 i have 1.12 The answer in N4 is 6 (good enough but would like to round to nearest No which should be 7) Formula used is =H4-F4*J4 F7 i have 0 H7 i have 6 J7 i have 2.24 The answer in N7 is still 6 ( it should be 12, double the one above) Formula used is =H7-F7*J7 just can`t work this out. help please thanks Scudo wrote: > F4 i have 0 > H4 i have 6 > J4 i have 1.12 > The answer in N4 is 6 (good enough but would like to round to nearest No > which should be 7) > Formula used is =H4-F4*J4 > > F7 i have 0 > H7 i have 6 ...

Fill text boxes with data from another text box
I have a form that has two sections. Section 1 has orginator name, address, city state, zip. Section two has owner name, address, city, state, and zip. If orginator and owner are the same, I want to just hit a button or check mark to copy data from section one to section two. Can this be done, and how would you do it. Thanks for your help- Porkchop. Porkchop, To copy data from one control (and, hence, field, if the form control source is set to the name of a field in the form's underlying record source), put the following in the AfterUpdate event procedure of the checkbox: If ...

data points #3
I have a scatter chart with the following info : sales price, square footage, and subdivision. With the x and y axis being sales price and square footage. There are some entries where i will have several sales in one subdivision and i would like all those points to show up in the same color. How do i get several points to show up in the same color instead of excel assigning each entry a new label? Include all the related points in the same data series, rather than as a different data series for each point. -- David Biddulph "affordablegreen" <affordablegreen@discussion...

Compile data from several workbooks
I have multiple workbooks for different projects. they are all in separate sub folders. Within each wookbook is a worksheet I call Bug. The Bug sheet pulls the data from that workbook which I need in a separate master workbook. I want to pull the data from every Bug worksheet into my master workbook without having to open each file to force it to update. I can do it cell by cell using a direct link but that is a lot of work. I have compiled a list of each workbook including the path. I can use that path and get the data using =INDIRECT(ADDRESS(2,3,,,F3) where F3 is the path. ...

extracting individual numbers from "ranges"
I'm trying to get EXCEL to extract stock prices quoted in individual cells as 52-week ranges --e.g. 11.76-19.90-- and do the following calculation: (higher price - lower price)/lower price Obviously, this a problem because each cell appears to handle the range as text or something. Is there are way to get EXCEL to handle the "text" (or however else it is interpreting what's in the cell) as individual numbers? Great thanks for any help. Hi do your entries always look like lower_number-higher_number if yes you may try the following formula in the adjacent row (lets assum...

Query and Extract
Hello all, This seems basic, but I just cannot get it. While using SQL Server 2005 and the Microsoft SQL Server Management Studio, I created a database called AssetQuote. Inside I have on table called assetquotes. From there, I have three columns, (date, quote, author) The column type for date is datetime and the other two are just text. My Query is something this: INSERT INTO AssetQuote (date, quote, author) VALUES('052010','No act of kindness, no matter how small, is ever wasted.', 'Aesop'); When I run the query I get this result: Msg 208, ...

Recovering Data from MDBDATA
My Server is now not up... As My Global Catalogue Server just crashed. I tried level best to make another but whole effort just was a waste.... Problem is now I have made another server with New Domain. (New One) . How can I retrive mails from MDBData File of the Previus Server. -- Zeshan ------------------------------------------------------------------------ Posted via http://www.webservertalk.com ------------------------------------------------------------------------ View this thread: http://www.webservertalk.com/message1020038.html www.ontrack.com/powercontrols -- John Oliver, Jr...

extracting hours from a sum of time
I have a cell which sum a bunch of cells containing an elapsed time. The sum totals to more than 24 hours. I have to a cell with a cost per hour and I need to caclculate the total cost. The problem is that the hour function returns values in the range of 0-24. My current sum is 25:30 and the hour function return 1 and not 25. Any ideas how to bypass it? It "returns" the correct number, but doesn't show. Format Custom as [h]:mm and it will. To convert to a number that you can multiply with an hourly rate, first multiply by 24; times in Excel are fractions of a day and a day is s...

Repeat data from previous records in a form
I am creating a a form for data entry and want to know if there is a way to repeat data in fields from the previous record (or default to previous record). For example, I user has 100 entries - I would like to set up the form so they do not have to enter their ID each time they enter a record. When you hold the ctrl key and click the apostrophe key ' access copies the contents of the same field from the previous record. This assumes that the new record immediately follows the old record so you may have to sort the source table and requery it after each time a new record is inserted...