+ Reply to Thread
Results 1 to 9 of 9

Copy data from txt file to excel file then delete text file

  1. #1
    Registered User
    Join Date
    03-28-2017
    Location
    New York, New York
    MS-Off Ver
    2010
    Posts
    20

    Copy data from txt file to excel file then delete text file

    Hi,

    I am looking for a vba code that will open a specific text file and look for specific data. Then I want it to place that data in specified column in excel worksheet based on criteria of another column in same worksheet.

    See below example data from text file:

    Data Report for Tom Gray
    January 31, 2018
    Report By: Portfolio
    Adjusted Market Underlying
    Total Market Value: 23,734 2,140
    04/19/18 05:18:10 AM Page 1 of 1

    Using above text example, I need code that will look for the line that begins with "Total Market Value", then return the number after the SECOND space. In this case, it would be 2,140.
    Then I want the code to place the number it found in column C of the excel sheet IF column A contains "Tom Gray".
    So if Tom Gray was found in A11, then the 2,140 would be placed in C11.
    Then I want the code to close and delete the text file.

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Copy data from txt file to excel file then delete text file


    Hi !

    Attach both workbook and text file …

  3. #3
    Registered User
    Join Date
    03-28-2017
    Location
    New York, New York
    MS-Off Ver
    2010
    Posts
    20

    Re: Copy data from txt file to excel file then delete text file

    I have added an excel file and a text with the proper extension name file to assist with my original post.
    Also as an addendum, would there be a way to make the macro pull data based on the file extension name, and then paste the data to the excel?
    All text files are saved in the same way in that the file extension contains the name to be found in Column A of the excel file.
    Just trying to avoid having to create this macro for each text file, which could be dozens, and then having to run each one.
    Attached Files Attached Files
    Last edited by gratedane8; 07-10-2018 at 09:46 PM. Reason: File name needed correction

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Re: Copy data from txt file to excel file then delete text file


    Detail « file extension name » as your attachment seems to not follow that …

  5. #5
    Registered User
    Join Date
    03-28-2017
    Location
    New York, New York
    MS-Off Ver
    2010
    Posts
    20

    Re: Copy data from txt file to excel file then delete text file

    I edited my previous post and have re-attached the txt file with the proper name.

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Question Re: Copy data from txt file to excel file then delete text file


    Ok it's not about the file extension (aka .txt) but just on the file name without its extension (aka Tom Gray) !
    But that's not really matter as this name is already in the first line of the text file …

    According to your attachment the place where must reside the result seems to be obvious
    but when reading again your original post I may be confused :

    Quote Originally Posted by gratedane8 View Post
    I want the code to place the number it found in column C of the excel sheet IF column A contains "Tom Gray".
    So if Tom Gray was found in A11, then the 2,140 would be placed in C11.
    So whatever the text file if the name is found within column A so the result must always stand in column C of the same row
    or maybe you had not very well explain your need ?

  7. #7
    Registered User
    Join Date
    03-28-2017
    Location
    New York, New York
    MS-Off Ver
    2010
    Posts
    20

    Re: Copy data from txt file to excel file then delete text file

    Marc L,

    Apologies for not explaining this well.
    The name is in the first line of the text file and in the file extension. I wasn't sure of the limitations of Excel, so I gave both as possible options to use for the code. If the extension does not matter and the name in the first line of the text file is sufficient for the code, then that's good.

    So whatever name you find in the first line of the text file, if you find that same name in Column A of the excel file, paste the corresponding number found in the text file for that name to Column C of the same row.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,465

    Re: Copy data from txt file to excel file then delete text file

    Try
    Please Login or Register  to view this content.
    Last edited by jindon; 07-11-2018 at 07:46 PM. Reason: Fixed a typo

  9. #9
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    9,831

    Arrow

    Removed as too late after Jindon …
    Last edited by Marc L; 07-11-2018 at 07:38 PM.

+ 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] Copy the data from Master file and paste into individual excel file as per criteria
    By Vikas_k in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-30-2023, 09:15 AM
  2. [SOLVED] Big Excel Data File ( 3 Deep columns ) To Text File To 3 Big VBA 1 Dimensional Arrays
    By Doc.AElstein in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-08-2017, 10:47 AM
  3. Replies: 1
    Last Post: 07-11-2015, 01:04 AM
  4. Looking for Excel file which can copy two text file in two diffrent spreadsheet
    By mobile_guru in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-02-2012, 09:28 PM
  5. Copy Excel to Text file and pass Text file to Access Table
    By robbie_xcell in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2011, 01:53 PM
  6. Copy data from new everyday csv file to next empty row in master excel file
    By hablu in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-18-2009, 02:06 AM
  7. Macro to copy a column data to flat file or text file
    By vinaynagasani in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-01-2009, 10:56 AM

Tags for this Thread

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