Excel VBA Read Text File

So how can you import the contents of a csv or
text file into your Excel worksheet:

Find out:


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
'========================================================
'DESIGNED & CODED BY LOEBLCOM SERVICES 2013
'ERIK LOEBL(713)409-7041
'EMAIL: erik@loeblcomservices.com
'WEB:   http://loeblcomservices.com
'========================================================


Sub query_text_file()
   
    'PURPOSE: Query A Text File
   
    '*************************************
    'clear 'Text File Contents' worksheet
    '*************************************
    Worksheets("Text File Contents").Select
    Range("A2:K6500").Clear
   
    '*************************************
    'now populate copy the orders over
    '*************************************
   
    Dim strPath As String
   
    'Need to reference the:
    '   Microsoft ActiveX Data Objects 2.5 Library
    Dim s_rst As ADODB.Recordset
    Dim s_cnn As ADODB.Connection 's for sub connection
    Dim intRow As Integer
   
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = &H1
   
    Set s_cnn = New ADODB.Connection

    strToolWkbk = "read_file.txt"
    strPath = ThisWorkbook.Path & "\read_file"

    Debug.Print strPath
   
   
    'For a text file, Data Source is the folder, not the file
    s_cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";" _
    & "Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
   
    s_cnn.Open
    Set s_rst = New ADODB.Recordset
   

    strSQL = "SELECT * FROM " & strToolWkbk

       
    s_rst.Open strSQL, _
        s_cnn, adOpenStatic, adLockOptimistic, adCmdText

    intRow = 2
   
    s_rst.MoveFirst
   

    Do Until s_rst.EOF
        Range("A" & intRow) = s_rst(0)
        Range("B" & intRow) = s_rst(1)
        Range("C" & intRow) = s_rst(2)
        Range("D" & intRow) = s_rst(3)
        Range("E" & intRow) = s_rst(4)
        Range("F" & intRow) = s_rst(5)
        Range("G" & intRow) = s_rst(6)
        Range("H" & intRow) = s_rst(7)
        Range("I" & intRow) = s_rst(8)
        Range("J" & intRow) = s_rst(9)

        intRow = intRow + 1
        s_rst.MoveNext
    Loop
   
    Range("A:C").Select
    Selection.NumberFormat = "General"
   
    Range("D:D").Select
    Selection.NumberFormat = "m/d/yyyy"
   
    Range("E:K").Select
    Selection.NumberFormat = "General"
   
    Range("A1").Select
    MsgBox "DONE."
   
    s_rst.Close
    s_cnn.Close
   
    Set s_rst = Nothing
    Set s_cnn = Nothing
       

End Sub


Click here for the file:
VBA Read Excel File.zip

Offsite Related Information:

“Go Fund Me” Page


($5 suggested amount)

(…10% of your gift amount will go to charity)

Free! Subscribe To Our YouTube Channel!

Free MS Access VBA Programming Course

Facebooktwitterredditpinterestlinkedinmailby feather
Tags: , , , , ,