Obtaining Data From a .txt File

  • I would appreciate guidance on the following. I've tried to do it by recording a macro and then adding in Open F:\Data\M400.txt For Input As #1 (which I learned from a previous post), however I still can't get my coding to perform as needed.

    I have an open workbook - F:\Files\SM\M400AD.xlsm. Sheet 1 (CSV_List) and sheet 2 (Summary) are permanent sheets; the remaining worksheets hold temporary data and are replaced every week or so.

    M400AD\Summary is the active worksheet. Column A contains the names of the remaining worksheets. The names are obtained from a .txt file; the .txt file contains a list of about 400 names in column A. I want to copy that list of 400 names to another workbook. That workbook contains a worksheet that is set up to split the 400 names into 4 groups of 100.

    So - stepwise - I want to:
    M400AD\Summary - delete Range("A3:A105")
    Open F:\Data\M400.txt
    Select All
    Close that file
    Open F:\Files\SM\SM_Performance.xlsm
    Make worksheet 'Lists' the active sheet
    Paste the data to Range("A3")
    Copy Range("C3:C105") (from worksheet 'Lists')
    Close SM_Performance.xlsm and return to M400AD\Summary
    Paste the data to Range("A3")

    Many thanks in anticipation

  • Re: Obtaining Data From a .txt File

    also it will depend on the txt file set up
    maybe code like

    to read the txt file then organise the data into columns

  • Re: Obtaining Data From a .txt File

    Sorry - I'm in the Pilbara for a couple of days and am working from my phone. The data in the .txt file is just a single column - col A. I want to copy that entire column of data.

  • Re: Obtaining Data From a .txt File


  • Re: Obtaining Data From a .txt File

    Sorry for the delayed response - I got badly side-tracked.

    The following is the macro with the suggested coding added in.

    It's getting stuck at the point where I try to open the .txt file. Instead of actually opening the text file it opens the Data folder. When I manually select M400.txt, then Open, I get an error pop-up showing 'File already open'.

    (PS - Sorry - the code lost all its formatting. This is the first time I've posted code. There's obviously a better way - I didn't expect it to misbehave like that!)

  • Re: Obtaining Data From a .txt File

    Well - I've progressed a little.

    I just realised that we're both trying to open the same file.

    I commented out the duplication and can now progress to the copy/paste area but it's going astray here too. Four columns of data that appear to have come from SM_Performance\Lists have been pasted into columns A-D of M400AD\Summary.

    A tidy up of my muddled up coding would be greatly appreciated - hope it really is "a simple fix".

    Thanks for the guidance on formatting the code - I'm slowly learning!

  • Re: Obtaining Data From a .txt File

    To start with just run this code below from SM_Performance.xlsm workbook

  • Re: Obtaining Data From a .txt File

    Ahh - OK - I think I can see what you're doing. If I read it right, you're copying the list and then splitting it up for me into the four smaller lists. To modify that slightly, can they be pasted starting at row 3? That leaves rows 1 & 2 for headers (which are currently being over-written by the new data).

  • Re: Obtaining Data From a .txt File

    change both

    iRow = 0


    iRow = 2

    I don't know whats in the txt file so it only a guess
    but I'm unsure what exactly what you are doing
    possibly you are working in workbook M400AD.xlsm
    you down the txt file to M400AD.xlsm and then open and copy to SM_Performance.xlsm?

  • Re: Obtaining Data From a .txt File

    Right, that's got that part of the copy/paste working perfectly.

    Re the contents of the txt file, it's simply a single column of around 400 labels. The reason I break them up into smaller groups is that each batch of 100 then gets its own workbook and each label has its own worksheet; I then perform calcs on the complete workbook. I've built a fairly high end computer and even with only 100 worksheets there's a very noticeable change in the speed of the cooling fans when I hit the GO button.

    She who must be obeyed now wants to go shopping so I'll take a break for a couple of hours. I'll attack the composite macro, with the new modifications, when I get back.

  • Re: Obtaining Data From a .txt File

    One last query/modification, to cope with a scenario that occurs occasionally:
    Once every few weeks the txt file contains slightly more than 400 labels; I've never seen it exceed 405. I'd like to let the last column contain everything from 301 onwards, so that the 100 row limitation that applies to the first three columns does not apply to the final column. I want to avoid having a fifth column with only 2 or 3 entries.
    Thanks for all your guidance - very much appreciated.

  • Re: Obtaining Data From a .txt File

    sure try this...

  • Re: Obtaining Data From a .txt File

    what the next bit?
    Option Explicit

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!