CODEWITHSUNDEEP
excelvbatextimport
Natalie King
Natalie King

Reputation: 35

Import a specific Text File into Excel Spreadsheet using VBA

I have a text file which is created daily and placed in the same folder each day. The file path of the text file never changes. For now, let's say the path is : "\MyPath\test.csv\"

I want to create a report in MS Excel 2010 where the user can press a button, the text file is imported into a range, say, Worksheets("Sheet1").Range("A1"), and then analysis is performed upon this imported data.

I have been using VBA with MS Excel for a while now and understand how to create a script which will import data from various databases, etc but I can't seem to get my head around importing text files! All the examples I find online seem to open a dialog box for the user to select the text file, but I don't want my user to be able to do this. I want one specific text file to be imported each time, with no imput from the user. All they have to do is press a button to start the macro.

Does someone have a very simple code which will do the importing for me? Basically, I want a macro to import a chosen text file into Sheet1 cell A1. I also want to manually specify the text delimiter within the code.

Any help/advice would be appreciated.

Upvotes: 1

Views: 16898

Answers (1)

barryleajo
barryleajo

Reputation: 1952

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.

enter image description here

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

Upvotes: 2

Related Questions