Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    2000 Text Files to Excel (Office 2000-2003)

    A colleague has received 2000 text files that he needs to import to Excel. First, is there a way to automate getting all those files into one Excel file? Second, the data is laid out horribly (see the attached file). I quickly looked at the one record and determined that IF all the extraneous data was eliminated (headers such as “Survey Answers”) he would have to copy/paste special to get the line descriptions into column headings. It would be ugly! I told him I would ask the experts for some ideas.

    Thanks.
    Attached Files Attached Files
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    If all files are stored in the same folder, you could write a macro to read each of the files in turn, and to write the data to an Excel sheet.
    If you would like help with that, it would be nice if you could give us an idea of the desired format of the worksheet and which of the data from the text files need to go where.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    The layout he is looking for is attached.

    Thanks Hans.
    Attached Files Attached Files
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    What is the layout of the text files?

    Steve

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    See the attachment in the first post.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    Steve

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    The attached text file contains a brute force approach. It could be made more compact but I don't have time for that now.
    Change the constant strPath at the beginning of the macro to the path of the folder containing the files. The path must end in a backslash .
    The target sheet must be open when you run the macro.
    Attached Files Attached Files

  8. #8
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    We will give it a try. Thanks Hans!!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    We had an error on the following line:
    lngRow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    We made 2 copies of the file I originally attached (he hasn't received all 2000 files yet) and put them in Cimensional and ran the macro. I attached the file with the macro.

    Thanks
    Attached Files Attached Files
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    That is because the worksheet is completely empty. I had assumed that you would have placed the column headers in row 1.
    For an empty sheet, you can replace the offending line with

    lngRow = 0

  11. #11
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    Duh! Sorry. We'll try again.

    Thanks
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  12. #12
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    Steve, we get an error message about not being able to find a file named *.txt (see attached).
    Attached Files Attached Files
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  13. #13
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    Hans, we fixed the headers <img src=/S/blush.gif border=0 alt=blush width=15 height=15> and it worked like a charm. You can't imagine the time you have saved us. Thank you so much!!
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    That suggests in the path there are no files with a TXT extension.

    Do the files you are importing have another extension or are they located in a different drive and path?

    Steve

  15. #15
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Michigan, USA
    Posts
    408
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 2000 Text Files to Excel (Office 2000-2003)

    That was the first thing I checked - see screen shot.
    Attached Images Attached Images
    <font face="Comic Sans MS"><big><font color=4682b4>Sherry</font color=4682b4></big></font face=comic>

Page 1 of 2 12 LastLast

Posting Permissions

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