Macros to Browse for Files

I am trying to summarise information held in various excel files.

I want to browse for a file by clicking an object 'button' - th
various pieces of information held in the file I've selected are to b
summarised in the sheet.

Ultimately, I am trying to collate information from various excel file
and summarise them in a new file.  Can this be done?

E.G  I have twenty different excel files.  Each of the files is setu
the same way so that the "Bill total" is in the same cell.  So, th
twenty files all have $10 as a total - the summary file (which I a
trying to create) would read $200.

Can this be done?

--
Message posted from http://www.ExcelForum.com

0
8/2/2004 3:46:28 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
346 Views

Similar Articles

[PageSpeed] 0

Chris,

Of course. There are a number of ways of doing this, and the best depends on
your situation.

1) If the files are all in the same folder (with no other files): you could
run a simple macro to open every file and read the data and write it into
the appropriate summary table.

2)All the files are in the same folder (with other files that you aren't
interested in): you run a similar simple macro that asks you which files to
open, or base it on some aspect of the filename or other file property.

3)You have a list of files that are in a number of different folders: you
write a macro that creates a link to the appropriate cell as a formula, and
do the table creation that way.

Examples of all these types of macros exist in the archives: try googling
first, or post back with more specifics.

HTH,
Bernie
MS Excel MVP

"chrisandrews >" <<chrisandrews.1adbje@excelforum-nospam.com> wrote in
message news:chrisandrews.1adbje@excelforum-nospam.com...
> I am trying to summarise information held in various excel files.
>
> I want to browse for a file by clicking an object 'button' - the
> various pieces of information held in the file I've selected are to be
> summarised in the sheet.
>
> Ultimately, I am trying to collate information from various excel files
> and summarise them in a new file.  Can this be done?
>
> E.G  I have twenty different excel files.  Each of the files is setup
> the same way so that the "Bill total" is in the same cell.  So, the
> twenty files all have $10 as a total - the summary file (which I am
> trying to create) would read $200.
>
> Can this be done??
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Bernie
8/2/2004 4:35:42 PM
Bernie,

Thanks very much for your help.  I'm not great with this aspect o
excel and you assistance is greatly appreciated.  However,  I need t
perform the second option you suggested.

All the files I need are in 1 folder, but with different filetypes 
don't need.

The source files have the values in the same cells (e.g cell A2 hold
the cost, cell D6 holds the total metres of material etc).

Can u direct me to the routine I am looking for which will make thi
easier?

Thanks for your help

--
Message posted from http://www.ExcelForum.com

0
8/3/2004 7:38:03 AM
Chris,

IF you need ALLl the Excel files in the folder, you can do something like
the first macro below, which will process ONLY Excel files, but will process
ALL the Excel files in the folder. If you want to select the files to
process, use the second macro below.  The first needs to have the folder
changed to reflect which folder you actually want to process, but the second
will allow you to browse to your folder.

Both will call the third macro "MakeSummary", which shows how to copy
specific cell values to create a table, which will be made on the
activesheet of the file that contains the code.

If you need help modifying these, let me know.

HTH,
Bernie
MS Excel MVP

Sub RunMacroOnAllFilesInFolder()
With Application.FileSearch
    .NewSearch
    .LookIn = "C:\Excel"
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute > 0 Then
        For i = 1 To .FoundFiles.Count
            Workbooks.Open .FoundFiles(i)
            MakeSummary
            ActiveWorkbook.Close False
        Next i
    End If
End With
End Sub

Sub OpenMultipleUserSelectedFiles()
Dim filearray As Variant
Dim i As Integer

filearray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(filearray) Then
    For i = LBound(filearray) To UBound(filearray)
        Workbooks.Open filearray(i)
            MakeSummary
       ActiveWorkbook.Close False
    Next i
End If
End Sub

Sub MakeSummary()
ThisWorkbook.Worksheets(1).Range("A65536").End(xlUp)(2).Value = _
    ActiveWorkbook.Name
ThisWorkbook.Worksheets(1).Range("B65536").End(xlUp)(2).Value = _
    ActiveWorkbook.Worksheets("Sheet1").Range("B2").Value
ThisWorkbook.Worksheets(1).Range("C65536").End(xlUp)(2).Value = _
    ActiveWorkbook.Worksheets("Sheet1").Range("C2").Value
End Sub


"chrisandrews >" <<chrisandrews.1aejld@excelforum-nospam.com> wrote in
message news:chrisandrews.1aejld@excelforum-nospam.com...
> Bernie,
>
> Thanks very much for your help.  I'm not great with this aspect of
> excel and you assistance is greatly appreciated.  However,  I need to
> perform the second option you suggested.
>
> All the files I need are in 1 folder, but with different filetypes I
> don't need.
>
> The source files have the values in the same cells (e.g cell A2 holds
> the cost, cell D6 holds the total metres of material etc).
>
> Can u direct me to the routine I am looking for which will make this
> easier?
>
> Thanks for your help!
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
Bernie
8/4/2004 1:41:07 PM
Bernie,

Thank you very, very much for your help!  I have been able to do what 
wanted and my problems are solved (almost!).

I havent finished the summary template yet, so you may be hearing fro
me in the future.

Thanks again,
Chri

--
Message posted from http://www.ExcelForum.com

0
8/5/2004 7:16:50 AM
Reply:

Similar Artilces:

quick way to combine 2 files?
If you have two excel files, both of which have 3 or 4 worksheets that are all used in lookup routines etc etc, is there an easy way to combine the two into a single file, as I think it won't work just by copying and pasting individual worksheets over in their entirety tks in advance LeeH Lee, With both workbooks open, group the sheets of one workbook (using Ctrl clicks on the tabs) then right click and select Move or Copy and then move them into the other workbook. HTH, Bernie MS Excel MVP "Lee Harris web" <lee.harris4@virgin.net> wrote in message news:32c0bd8d.04012...

How do I convert .txt files into .xls in order to be able to sum.
I receive via e-mail a statement that I need to resort by date , purchase order and total by department, etc. The process I have been using is to copy and paste into Excel. However, when I try to sum the $ is will not sum. Is there some other way of converting this statement that I receive via e-mail into Excel, so that I can resort and sum? I am using Excel 2002 and my e-mail browser is Mozilla. You might be able to use File-->Open, and then let the Text Import Wizard help. Otherwise, you could use the "data isn't recognized" fix here: http://www.officearticles.co...

How to read .pst file?
Supposed I have a .pst file that contains mails in Inbox folder. How can I write a program to read the those mails? The program must read from the .pst file without Outlook start-up. Any clue? See my answer in the Exchange newsgroup thread. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Absolute Beginners Guide to Microsoft Office Outlook 2003 Reminder Manager, Extended Reminders, Attachment Options http://www.slovaktech.com/products.htm "Roger" <anonymous@discussions.microsoft.com> wrote in message news:315C7206-100C-416E-88BF-976949AEDE90@microsoft.com....

"a document with the name" error on opening file
I'm getting an error message that says "A documents with the name " & filename & " is already open" (where filename is the actual name of the file) when I try to open certain files in Excel 97, when I don't really have a copy of the file in question opens. This error comes up pretty consistantly on one file that lives on a server, but I also get it when I try to open some local files. Anyone have any idea what would cause this & how to fix it? Thanks in advance. Best Jim Hi Jim have a look at: http://www.contextures.com/xlfaqApp.html#AlreadyOpen ...

File in 2007 form
I was trying the Beta Office 2007 and made the mistake of saving one of you Excel file in the new 2007 form. I really need it back in the 2003 form. Problem: due to a variety of issues, I de-installed the Beta version and went back to 2003 but for got about the one file.. So; are there any utilities that anyone knows of the will convert this file back two 2003 form?? It is a simple Excel file, meaning that there are no clever or fancy formula or macros, just a collection of parts and part numbers gathered over MUCH time surfing various web sites. Any help or advise (beside the obvious....

word attachments on e-mail, docx files, recipient unable to open
I have been sending e-mails as I always have and attaching them in the way I always have, all of a sudden word documents are coming up as docx files and the recipients cannot open them, I have only recently gone onto Vista and microsoft offoce 2007. This has only happened recently with new docs that I have created on the new system. It is frustrating my customers and my hair is now falling out. Please help! That's because Word 2007 saves documents in a new format, .docx, unless told to do otherwise. There are two solutions: 1. Do a "Save As" and save documents in Word 97-...

My Account Settings, Icons, Files are "Missing"
Recently, I booted up and discovered that my personalized icons (under my account) had disappeared from the desktop. The Account Name is "Gilxxx" but my data is under "Curxxx" When I looked closer, I was able to see the directory structure in my "Documents and Settings" folders, but none of the files. The files are there, however, as I can play MP3s in Windows Media Player (and they are in my My Music directory). And, when I tried to add new desktop icons to "re-create" my old desktop, Windows tells me that a shortcut with that name alread...

Problem cleaning up temporary files.
This isn't MFC specific so I apologize. I just like the information provided by the regular members of this group. I have an application that has a Rich Edit control. Next to the Rich Edit control is a Print button. To avoid all the details of printing, I decided to save the contents of the control to a RTF file, then do ShellExecuteEx("blahblah.rtf", "print", ...). This works fine. But, I sure would like to remove these temporary files. But I am finding that to be a tricky problem to solve. At first, I thought I would wait for the process started to end. Bu...

Virus in .mmd file
Hello, I speek english not well....I know. My anti-virus (norton) find a virus in a .MMD file stored in following folder : C:\Program Files\Microsoft BackOffice\Connectivity\POP3 Connector\InForward\ But the antivirus cannot access to this file, how i can remove this file ??? Thanks Configure NAV to exclude any data directories of Exchange (and other server products), otherwise file-level antivirus might damage databases. Then, in order to protect yourself from viruses, get Exchange-aware antivirus. J�r�my taverne wrote: > Hello, > > I speek english not well....I k...

Publisher File formats
I need to send file in a tif or jpeg format but when I save the file I still get the pub extension after the .tif' Can I not save in anything but pub? Thanks Ray, It would help us to answer your question if you told us which version of Publisher you are using. -- JoAnn Two things are aesthetically perfect in the world - the clock and the cat. --Emile-August Chartier "Ray" <anonymous@discussions.microsoft.com> wrote in message news:006101c3dc62$7649ce40$a501280a@phx.gbl... > I need to send file in a tif or jpeg format but when I > save the f...

dc as a file server
is it bad practice to have your dc as a file server as well. 100 people or less. One dc is Exchange and the other is the file server. Just wondering if I should put a member server in and move the file server over to that one? any thoughts appreciated. Not necessarily. This is common for branch offices to use the DC as the office file server also hth DDS "joe schmo" <joeschmo@discussions.microsoft.com> wrote in message news:3332409F-9184-4A3F-9DB3-1E8B57591C08@microsoft.com... > is it bad practice to have your dc as a file server as well. 100 peo...

overlaping dates in two files
MS Money 2001 deluxe on Windows XP Pro I archived my 2004 accounts and never balanced them. I decided i needed to and now have two files, both .mny files with 2003Archive.mny is from 1/2002 to 12/2004 Active2005.mny is from 1/2004 to present. I'd like to combine them into one file so I can balance the accounts (three main accounts) because my statements are not ending nicely on the end of the month. Please recommend the best method so I can balance the accounts. I think I need to conbine the files or somehow get the months that are now balanced in the 2003Archive.mny file to give...

tail or other realtime monitors for SMTPSVC exyymmdd.log files?
I'm trying to find an inexpensive way to spot-check / monitor the various log files in realtime for my Exchange environment of 2000 and 2003. I have tried a handful of "tail" implementations and all of them seem to work to a certain degree but none of them are able to successfully keep monitoring the text files that Exchange updates as part of the SMTPSVC. I'm using the W3C format and when I "tail" these files, it won't keep updating properly as new data is appended to the end of this file. Is anyone else monitoring their logs in realtime and if so, what util...

Highlighting whole row in this macro
Can I add something to this so that when the A1:A100 field is changed, the whole row (A-L) is highlighted ColorIndex 6? Dim x, cl Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("A1:A100")) Is Nothing Then Exit Sub Target.Offset(0, 1) = x Target.Interior.ColorIndex = 6 End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) x = ActiveCell.Value End Sub target.entirerow.resize(1,12).interior.colorindex = 6 or since the target is already in column A: target.resize(1,12).interior.colorindex = 6 eastrivergraphics@gmail.com wrote: > &...

Changing button colours with a macro
Any advice on this one - I am very inexperienced with the writing of macros, but have managed to write a few and attach them to buttons, I would like the buttons to change colour each time they are clicked - any suggestions?? VBA code: Worksheets("Sheet1").Shapes(Button64).Select With Selection.Font .ColorIndex = 3 End With Jim "Rinativers" <anonymous@discussions.microsoft.com> wrote in message news:BD5CEBA6-884C-4576-A105-B2F4AF50B5EE@microsoft.com... > Any advice on this one - I am very inexperienced with the writing of macros, but have...

File Attachment Problem
I have got the file attachment problem since we implemented the dynamics CRM 4.0. So i will be glad if you advise me a way to solve this problem. sincerally ...

Shrinking a Money File
I just upgraded to Money 2006 and I archived all my records prior to 01/01/2004. Is there a way to now shrink my money file in size? It's still at 20 Megs, same as it was before the archive. I assume since a majority of the data has been archive that I can compress all the open space in the file? I tried repairing the file (Standard and quick) and that didn't shrink the file. Does anyone know of a way? Thanks.... This is a multi-part message in MIME format. ------=_NextPart_000_000F_01C58BE6.00FFF300 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-En...

[External Copy or SYLK] file format is not valid
I am moving to a new computer. I have WIN98SE on my old computer and WIN XP on the new one. I have Excel 97 and I am trying to transfer my data to the new computer. I get the above message when I try to open a file in XP. What is the problem? Thanks in advance, Bill. ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

Macro to extract in string
Any help with this is greatly appreciated. Bob was graceous enough to provide the vba script below, however I now need to make a minor change in it. It extracts part of a string in column A1 and puts the results after the last row in col B. I have approx 200 rows that do the same extraction. I would like to have the results placed in G1 thru however many rows. Each extraction goes on same row as it's orginal. I know to change the B to G for the column, but don't know what to change to have the extracted part go to G1 and so on, down the rows. Thank you, Calop Sub AREA_REPORT() Di...

Clicking Macros or Visual Basic Editor crashes Outlook
Hello, I am running Office 2003 on a HP computer with Windows Media Center XP. Every time I click on Tools/Macro/Macros, or Tools/Macro/Visual Basic Editor; Outlook completely crashes. There is no specific error message, and the contents of the data file that is sent to Microsoft will not copy to a text file so I can post it for help. I can click Tools/Macro/Security option just fine. I have the security set to Medium. The same options in Word and Excel work fine, bringing up the Visual Basic editor and a Macro list. I have tried "Detect and Repair" and it tells me ever...

How to access macros via tool buttons?
Excel v2007 When using v2003, I had many macros In my Personal.xls, which I accessed via custom tool buttons on many custom toolbars that I built. I now have version 2007, which includes my old Personal.xls with all my macros, but I want the easy access to those macros I used to have. Being the tool ribbon is a different system, what is now the strategy for fast button-clickable launching of macros? Are buttons built, menus, what? [I already know about the Quick Access Toolbar, but I have over a hundred macros I previously had divided onto several toolbars that I'd hide and s...

Custom Fileds
How do you add a drop down list (custom field) to the activities for an account and Is it possible to remove some of the buttons on the side under Sales like quotes, invoices, etc...? Robert, You add custom fields through the deployment manager. Once you have selected the table you want to add a field and choosen Add Schema Field, you will be presented with what kind of field you want to create. Among your choices is a picklist. -Jason "Robert" <Robert@discussions.microsoft.com> wrote in message news:0909FBA0-B107-48D2-AB3C-BA46FF131EF4@microsoft.com... > How do you ...

what control should I use to display a bmp file?
Hello everybody, Like in subject: what control should I use to display a bmp file? I've created a dialog with 'picture control', but can't seem to find any way to display a bitmap on it having path given. It's not one, special bitmap. It's user-defined, selected from 'Open file' dialog. What method should I use to display a bmp on a 'picture control'? Any ideas? The following tutorial shows one way to load a bitmap. http://functionx.com/MFCFundamentals/Lesson11.htm -- Cheers Check Abdoul [VC++ MVP] --------------------------------...

Help Files not displaying in 2004
Has anyone who has loaded 2004 seen this anomoly. The help guide comes up on the right of the screen, but when I click on topic, I get nothing. I can search for a topic, and the topic appear, but when I click on what I want to review, I get a "unable to load topic". I wrote a new FAQ last weekend that should be posted shortly: --- Q): Money Help keeps whining about script errors or similar nonsense. Why? What can I do about it? A): Nobody really knows what causes this. For most people, this issue has been resolved by going into IE Internet Options (from IE Tools or from the des...

Opening Publisher 98 files in Publisher 2003
I can open some Pub 98 files in 2003 but not others. Large files (~18,000KB) won't open. Small (~50KB) will. I don't have Norton Anti-Virus but do have McAfee Virusscan, Personal Firewall+ & Privacy Service. Any ideas? Did you turn off script blocking? -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.org/ news://msnews.microsoft.com "1CRSNC" <1CRSNC@discussions.microsoft.com> wrote in message news:3ABEAE44-7A7E-4692-A854-63B0F665ABF4@microsoft.com... >I can open some Pub 98 files in 2003 but not others. Large files (~18,000KB) > w...