Loading Linux Text File into Excel Using Vba

Import a specific Text File into Excel Spreadsheet using VBA

A couple of assumptions to give you some flexibility in customising this code example. My test data file is shown in the image below i.e. ten variable length records, each field delimited by a comma. The code reads the .csv file one record at a time and puts each record on a separate row. Within each row (i.e. for each record), each field is put into a separate cell. It uses a couple of arrays to achieve this. You can adjust the code to suit your context and there are some comments within, to help. Just add a button to call this macro.

Sub ReadTxtFile()

Dim ws As Worksheet
Dim rearr(), wrarr()
Dim fName As String
Dim rowno As Long, colno As Long, rec As Long
Dim cnt As Long, cnt2 As Long
Dim delim As String

'specify output sheet
Set ws = Worksheets("Sheet1")
'specify text file to read (.csv in this example)
fName = "C:\MyPath\test.csv"
'set text file delimiter
delim = ","  'for Tab delimiter use delim = Chr(9)
ifnum = FreeFile
'set start row/col for text data to be placed ("A1" in this example)
rowno = 1  'row 1
colno = 1  'col A

    With ws
        Open fName For Input Access Read As #ifnum
            rec = 0
                Do While Not EOF(ifnum)
                    Line Input #ifnum, tmpvar
                    rec = rec + 1
                    'Put whole record into array
                    ReDim Preserve rearr(1 To rec)
                    rearr(rec) = tmpvar
                    'Split fields into a second array
                    wrarr = Split(rearr(rec), delim)
                    cnt2 = UBound(wrarr)
                        'Write fields out to specified ws range, one row per record
                        For cnt = 0 To cnt2
                            ws.Cells(rowno, colno + cnt) = wrarr(cnt)
                        Next cnt
                     rowno = rowno + 1
                Loop
            Close #ifnum
    End With
End Sub

How to copy the whole txt file to Excel

This code will loop through all text files in a folder, and import one file into one cell, then import another files into another cell, and so on and so forth.

Sub Import_All_Text_Files()

    Application.ScreenUpdating = False

    Dim thisRow As Long
    Dim fileNum As Integer
    Dim strInput As String

    Const strPath As String = "C:\your_path_here\"  'Change as required
    Dim strFilename As String

    strFilename = Dir(strPath & "*.txt")

    With ActiveSheet
        thisRow = .Range("A" & .Rows.Count).End(xlUp).Row

        Do While strFilename <> ""

            fileNum = FreeFile()
            Open strPath & strFilename For Binary As #fileNum
            strInput = Space$(LOF(fileNum))
            Get #fileNum, , strInput
            Close #fileNum

            thisRow = thisRow + 1
            .Cells(thisRow, 1).Value = strInput

            strFilename = Dir
        Loop
    End With

    Application.ScreenUpdating = True

End Sub

If you have just one file that you need to import, it will import only that one.

How to identify if a text file is unix or windows using excel vba?

You could use something like this to count:

    Private Sub CountCRLF()
       Dim TmpStr$()
       TmpStr = Split(Text1.Text, Chr(13) & Chr(10))
       MsgBox "There are " & UBound(TmpStr()) & " CRLF's in string", vbInformation, "Count"
    End Sub

If that is more than a few, it should be a Windows file. If a file mixes both types of line breaks, a more complex approach would be needed.

But this worries me in terms of performance, unless you are sure the files are really small. And something in your question makes me think you already have a function to handle the lines one by one and you should probably try to only process these files once, detecting and handling as you go along...

How to create a text file when you click a button in Excel

Answering to your question:

How to create a text file when you click a button in Excel

Those are the steps i too:

1. Add a form to the project

2. Add a button to form

3. Add following code to click event of the form:

   Private Sub CommandButton1_Click()

   'object to use as folder

   Dim fld As Object
   Set fld = CreateObject("Scripting.FileSystemObject")

   'using create text file method

   Dim myFile As Object
   Set myFile = fld.CreateTextFile("C:\TESTES\myTextFile.txt", True)

   End Sub

As you can see the code is quite similar to your, I have only changed the folder were the file is saved.

I have run the code and the file is created, no problem with that.

Now, what happens is that the file has no contents. Depending on what you have on your excel file and what you want to send to your txt file, you can have very diferent aproaches. However to write lines to you txt file you can use the fso that is already initiated.

The code bellow will add a line to the txt file

Private Sub CommandButton1_Click()

'object to use as folder
Dim fld As Object
Set fld = CreateObject("Scripting.FileSystemObject")

'using create text file method
Dim myFile As Object
Set myFile = fld.CreateTextFile("C:\TESTES\myTextFile.txt", True)

myFile.WriteLine "test"

myFile.Close

Set fld = Nothing

Set myFile = Nothing

End Sub

---

Related Topics