Results 1 to 1 of 1
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    La Jolla, CA
    Thanked 76 Times in 71 Posts

    Macro to adjust text file and create a 2nd text file

    I have an Excel sheet with data that begins in row 11. The A column contains an employee number and column B contains the employee name.

    I need to have the user navigate and select a MASTER TEXT file. I've done this before w/the help of others here (Maud and RG) and have the following code for starters:

    Public Sub EmployeeAdjust()
    Application.ScreenUpdating = False
    Dim Filter  As String
    Dim Caption As String
    Dim Fname   As String
    Dim CurPath As String
    Dim Header  As Boolean
    Filter = "Text files (*.txt),*.txt"
    Caption = "Please Select the source TEXT file "
    CurPath = CurDir     'GET CURRENT DIRECTORY
    ChDir ("C:\Users")
    Fname = Application.GetOpenFilename(Filter, , Caption)
    If (Fname = "False") Then Exit Sub
    FilePath = Left(Fname, Len(Fname) - 4) & "_RevisedEmp.txt"
    The MASTER txt file's rows look like this:

    The first field in the master is the employee number (which could and will occur a number of times).

    I need to create TWO new text files: 1. The Modified Master without those rows that contain employee numbers from column A of the sheet and
    2. Revised Text file that contains the employee name (column B) and all the other data for each employee that matches the Master file. So, if John Smith is emp #6082, I need all 6082s in the revised changed to John Smith (and remove all employee numbers NOT in the sheet)

    I think I can do the work directly in Excel, but the client/user insists that a macro be used to open the master and recreate the two new text files.

    I hope someone can help on this one.
    Last edited by kweaver; 2016-09-12 at 15:50.

Posting Permissions

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