+ Reply to Thread
Results 1 to 15 of 15

Extract from multiple .out files to one Excel worksheet

  1. #1
    Registered User
    Join Date
    04-24-2014
    Location
    Glendale, Colorado
    MS-Off Ver
    Excel 2013
    Posts
    49

    Extract from multiple .out files to one Excel worksheet

    I am looking for a macro that can extract data from multiple .out files and compile into one master Excel worksheet. I recorded a macro of myself doing an import of one .out file which is below.

    In doing this, the number of rows is always different but the last populated column is always 'DM'. The folder containing my .out files contains all the out files I need imported and none that I don't. It doesn't matter what order they are brought in to Excel either as long as all are imported once.

    Importing one text file is easy enough, but then I must scroll to the bottom and from there manually import the next file. If the macro I recorded was able to go to the next file without prompting that would be ideal.

    Here is the macro of one import- starting with Get Data From text ending with pasting to a location in spreadsheet.

    Please Login or Register  to view this content.
    Any help would be greatly appreciated!
    Last edited by Leith Ross; 11-01-2014 at 05:45 PM. Reason: Added Code Tags

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Extract from multiple .out files to one Excel worksheet

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    I have added the tags for you this time. Please do so in the future posts.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,820

    Re: Extract from multiple .out files to one Excel worksheet

    Please Login or Register  to view this content.
    Then amend your Sub to receive and use the file name

  4. #4
    Registered User
    Join Date
    04-24-2014
    Location
    Glendale, Colorado
    MS-Off Ver
    Excel 2013
    Posts
    49

    Re: Extract from multiple .out files to one Excel worksheet

    Thanks, i can't test it till Monday. How do I amend my sub to receive??

    Thanks

  5. #5
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,820

    Re: Extract from multiple .out files to one Excel worksheet

    Ive highlighted the amendments made in bold (if you copy and paste then be sure to remove the [B] formatting bits!

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-24-2014
    Location
    Glendale, Colorado
    MS-Off Ver
    Excel 2013
    Posts
    49

    Re: Extract from multiple .out files to one Excel worksheet

    I guess I need steps from the beginning to use Macro you've made, as it is not seeming to do the trick.

    My problem is pretty basic-I recorded a very simple macro, (hit record, selected a cell, stop recording) then entered VBA and pasted above into the module, clicked the save button, saved the macro enabled worksheet and exited, back to blank xcel sheet. Then I click Macros and there are none to choose from to run. I seem to get this result each time.

    I also am not sure what changes to make to above for my use. I don't see any formatting?? Do I need to enter a path or filename anywhere??

  7. #7
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,820

    Re: Extract from multiple .out files to one Excel worksheet

    Have you got the two Subs in a Module of your workbook (doesnt matter if theyre in the same module or not)?

    If so then putting your cursor on the Sub LoopFiles() line and clicking the Play button on the ribbon or Run from the menu will run it.

  8. #8
    Registered User
    Join Date
    04-24-2014
    Location
    Glendale, Colorado
    MS-Off Ver
    Excel 2013
    Posts
    49

    Re: Extract from multiple .out files to one Excel worksheet

    It gives me Syntax Error message and highlights this line from the 1st macro

    " call Macro2 objFile.Name 'run your code passing the file name to it"
    Last edited by Melissa Camp; 11-04-2014 at 02:18 PM. Reason: More helpful to give exact detail of what is not working

  9. #9
    Registered User
    Join Date
    04-24-2014
    Location
    Glendale, Colorado
    MS-Off Ver
    Excel 2013
    Posts
    49

    Re: Extract from multiple .out files to one Excel worksheet

    I've attached my WB.
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,820

    Re: Extract from multiple .out files to one Excel worksheet

    try this, you werent actually calling your macro, Ive commented the code thats been changed.
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    04-24-2014
    Location
    Glendale, Colorado
    MS-Off Ver
    Excel 2013
    Posts
    49

    Re: Extract from multiple .out files to one Excel worksheet

    Holy crap, thank you so much.

  12. #12
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,820

    Re: Extract from multiple .out files to one Excel worksheet

    Can I assume that worked?

  13. #13
    Registered User
    Join Date
    04-24-2014
    Location
    Glendale, Colorado
    MS-Off Ver
    Excel 2013
    Posts
    49

    Re: Extract from multiple .out files to one Excel worksheet

    It worked!! This will save me a ton of time. Thank you!!

  14. #14
    Registered User
    Join Date
    08-20-2012
    Location
    Londonish, England
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: Extract from multiple .out files to one Excel worksheet

    Just purely out of interest, maybe you could you use the old copy method from command prompt to merge all of your files together? Maybe:

    Please Login or Register  to view this content.
    Might be barking up the wrong tree though - not sure if would even work with .out files.

    Thanks

    Chriz

  15. #15
    Registered User
    Join Date
    04-24-2014
    Location
    Glendale, Colorado
    MS-Off Ver
    Excel 2013
    Posts
    49

    Re: Extract from multiple .out files to one Excel worksheet

    Yes, this works. I didn't think it did till I went into my Daily Run Files and found an 'all.out' file. I just imported into excel, everything is there. So now there are options..This is just great!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extract from multiple .out files to one Excel worksheet
    By Melissa Camp in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-01-2014, 04:41 PM
  2. Extract Data from Multiple Closed Excel Files
    By Gittelman in forum Excel General
    Replies: 2
    Last Post: 07-29-2014, 12:09 AM
  3. [SOLVED] Extract Data From Multiple Excel Files -> One File
    By Michael via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-24-2012, 01:02 PM
  4. extract key words/data from multiple files -dump in new worksheet
    By MikeR-Oz in forum Excel - New Users/Basics
    Replies: 10
    Last Post: 03-20-2006, 04:15 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1