Obtaining Data From a .txt File

OldFella · Mar 31st 2016

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
Copy
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

**pike** · Apr 1st 2016

Re: Obtaining Data From a .txt File

hello ,
what is the code?

**pike** · Apr 1st 2016

Re: Obtaining Data From a .txt File

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

Code

Sub Sample()
    Dim MyData As String, strData() As String, myFile As String


    myFile = Application.GetOpenFilename()


    Open myFile For Binary As #1
    MyData = Space$(LOF(1))
    Get #1, , MyData
    Close #1
  
End Sub

Display More

to read the txt file then organise the data into columns

OldFella · Apr 2nd 2016

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.

**pike** · Apr 2nd 2016

Re: Obtaining Data From a .txt File

nice

Code

Option Explicit
Sub Test()
    Dim strLine As String
    Dim strFile As String
    Dim iRow As Integer
    Dim iColumn As Integer


    strFile = Application.GetOpenFilename()
    iRow = 0
    iColumn = 1
    Open strFile For Input As #1
    Do While Not EOF(1)
        iRow = iRow + 1
        Line Input #1, strLine
        Cells(iRow, iColumn).Value = strLine
        If iRow = 100 Then
            iRow = 0
            iColumn = iColumn + 1
        End If
    Loop
    Close #1
End Sub

Display More

OldFella · Apr 9th 2016

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'.

Code

Sub Trial()
'
' Trial Macro
'
    Dim FilePath As String
    Dim strLine As String
    Dim strFile As String
    Dim iRow As Integer
    Dim iColumn As Integer
'
    Range("A3:A103").Select
    Selection.ClearContents
    FilePath = "F:\Data\M400.txt"
    Open FilePath For Input As #1
    strFile = Application.GetOpenFilename()
    iRow = 0
    iColumn = 1
    Open strFile For Input As #1
    Do While Not EOF(1)
        iRow = iRow + 1
        Line Input #1, strLine
        Cells(iRow, iColumn).Value = strLine
        If iRow = 100 Then
            iRow = 0
            iColumn = iColumn + 1
        End If
    Loop
    Close #1
    Windows("SM_Performance.xlsm").Activate
    Sheets("Lists").Select
    Range("A3").Select
    ActiveSheet.Paste
    Range("C3:C105").Select
    Selection.Copy
    Windows("M400AD.xlsm").Activate
    Range("A3").Select
    ActiveSheet.Paste
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

Display More

(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!)

**pike** · Apr 9th 2016

Re: Obtaining Data From a .txt File

Hi
Its a simple fix.. but first go to your last post and click the edit button then highlight/select the code and then click the # in the tool bar.
then save this will add the code tags to the vba syntax

OldFella · Apr 9th 2016

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!

**pike** · Apr 9th 2016

Re: Obtaining Data From a .txt File

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

Code

Option Explicit 
Sub Test() 
    Dim strLine As String 
    Dim strFile As String 
    Dim iRow As Integer 
    Dim iColumn As Integer 
     
    strFile =  "F:\Data\M400.txt"
    iRow = 0 
    iColumn = 1 
    Open strFile For Input As #1 
    Do While Not EOF(1) 
        iRow = iRow + 1 
        Line Input #1, strLine 
        Cells(iRow, iColumn).Value = strLine 
        If iRow = 100 Then 
            iRow = 0 
            iColumn = iColumn + 1 
        End If 
    Loop 
    Close #1 
End Sub

Display More

OldFella · Apr 9th 2016

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).

**pike** · Apr 9th 2016

Re: Obtaining Data From a .txt File

sure
change both

Code

iRow = 0

to

Code

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?

OldFella · Apr 9th 2016

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.

OldFella · Apr 9th 2016

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.

**pike** · Apr 9th 2016

Re: Obtaining Data From a .txt File

sure try this...

Code

Option Explicit
Sub Test()
    Dim strLine As String
    Dim strFile As String
    Dim iRow As Integer
    Dim iColumn As Integer
     
    strFile = "F:\Data\M400.txt"
    iRow = 3
    iColumn = 1
    Open strFile For Input As #1
    Do While Not EOF(1)
        iRow = iRow + 1
        Line Input #1, strLine
        Cells(iRow, iColumn).Value = strLine
        If iRow = 100 And iColumn < 3 Then 'changes to this line
            iRow = 3
            iColumn = iColumn + 1
        End If
    Loop
    Close #1
End Sub

Display More

OldFella · Apr 9th 2016

Re: Obtaining Data From a .txt File

Perfect! Thank you once again. Another part of the jigsaw is now complete.

**pike** · Apr 9th 2016

Re: Obtaining Data From a .txt File

what the next bit?
maybe
Option Explicit

Code

Sub Test()
    Dim strLine As String
    Dim strFile As String
    Dim iRow As Integer
    Dim iColumn As Integer
    Set wb1 = SM_Performance.xlsm
    Set wb2 = M400AD.xlsm
 
    strFile = "F:\Data\M400.txt"
    iRow = 3
    iColumn = 1
    Open strFile For Input As #1
    Do While Not EOF(1)
     With wb1.Sheet("Lists")
     iRow = iRow + 1
        Line Input #1, strLine
        .Cells(iRow, iColumn).Value = strLine
        If iRow = 100 And iColumn < 3 Then
            iRow = 3
            iColumn = iColumn + 1
        End If
   End With
   Loop
    Close #1
    wb1.Sheets("List").Copy wb2.Sheet("Sheet1").Range("A1")
    Set wb1 = Nothing
    Set wb2 = Nothing
    
End Sub

Display More

Obtaining Data From a .txt File

Participate now!

Share