+ Reply to Thread
Results 1 to 6 of 6

Importing Multiple text documents into Different Tabs in Excel

  1. #1
    Registered User
    Join Date
    05-07-2014
    Location
    Connecticut, US
    MS-Off Ver
    Excel 2010
    Posts
    3

    Importing Multiple text documents into Different Tabs in Excel

    Hi everyone,

    I am a VBA novice, and any help here would be appreciated. I am looking to import several text files into excel. Each text file contains several fields of data that are each separated by a space. I would like each text file to be imported into a different tab in the excel workbook.

    Thanks in advance for all your help!!!!!

    John

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Importing Multiple text documents into Different Tabs in Excel

    This macro will probably do most of the work:

    CSVs To Sheets

    You can edit the part in red from .csv to .txt to work on text files.

    After running that macro on your folder full of text files, I'd suspect you'd have the unparsed data on separate sheets. If you turn on the macro recorder and do a TextToColumns split on the data on one of the sheets so that it looks the way you want, post that resulting code here and I can help you merge that into the given macro.
    Last edited by JBeaucaire; 12-27-2019 at 03:50 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    05-07-2014
    Location
    Connecticut, US
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Importing Multiple text documents into Different Tabs in Excel

    Hi JBeaucaire,

    Thanks for you helpful response! I was able to get the macro to work. Below are two macros generated by the Text to Columns conversion I did on two of the text files. As you can see, I had to use the fixed width option to parse the data, and this is true for all of the text files. Is there a way to variabllize this so that I can do it in all one go? Perhaps I can define some sort of array.

    Thanks,


    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 05-07-2014 at 10:28 PM. Reason: Added missing CODE tags. Please read and follow the Forum Rules, link above in the menu bar. Thanks.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Importing Multiple text documents into Different Tabs in Excel

    Not if the text files are really random lengths of the fields. Most programs, when exporting to text or csv files, use some sort of standards on those fields. Are you certain there's no consistent approach here? Can the source of the data be changed from text files to CSV files?

  5. #5
    Registered User
    Join Date
    05-07-2014
    Location
    Connecticut, US
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Importing Multiple text documents into Different Tabs in Excel

    Thanks JBeaucaire. Would you know of any way to convert general files that are fixed width to .csv?

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Importing Multiple text documents into Different Tabs in Excel

    No, this is the process for that, too. The program that is currently being used to create these txt files would be the place where this would take place, exporting that data as csv instead of CSV, if that option is available, which it often is. CSV is a much better format when there is no control of the field width.

    If you want to post a couple of those raw text files along with the TextToColumns result of what you did to split them, I can see if your data lends itself to being split another way.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

+ 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. [SOLVED] Importing Data from Multiple Excel Documents Into a Single File (take 2)
    By YvonH in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-24-2013, 08:02 PM
  2. Importing Data from Multiple Excel Documents Into a Single File MAC Issues
    By Kal_2013 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-04-2013, 04:16 PM
  3. Replies: 10
    Last Post: 11-27-2012, 11:08 AM
  4. Importing Data from Multiple Excel Documents Into a Single File
    By DinahMarcinko in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 09-07-2012, 04:41 PM
  5. Importing Data from Multiple Excel Documents Into a Single File
    By DinahMarcinko in forum Excel General
    Replies: 2
    Last Post: 09-06-2012, 04:11 PM

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