Import Text File To Excel

[nataliek92]

Hello all,

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.

 

[nataliek92]

Thanks again for your replies. I thought they would work but they don't.

I recorded a macro which imports the data as recommended, then added the extra sections given by Comfy and Quazzart, but neither work. I know this because when I try to delete the data from sheet1, I get an error message which says: "The range you deleted is associated with a query that retrieves data from an external source.........."

Any further ideas?

 

[Qazzart]

Please share your code

 

[Comfy]

"The range you deleted is associated with a query that retrieves data from an external source.........."

I get no such message once the Querytable and Connection have been deleted (Win XP, XL2010)

Please share your code

^This and what version of Excel do you use?

 

[rpaulson]

Here is what I use: Modify ranges and path as needed.

Sub Get_data_from_ST_cvs_File()

Dim fName As String

fName = "C:\results\test.csv"

Range("A1:Z50000").ClearContents 'Clear any previuos data

    Open fName For Input As #1
    r = 1
    Do Until EOF(1)
    Line Input #1, LineFromFile
    lineitems = Split(LineFromFile, ",") 'Comma is the delimiter
    For c = 0 To UBound(lineitems)
        Cells(r, c + 1) = lineitems(c)
        Next c
        r = r + 1
        Loop
    Close #1
End Sub

Let me know how it works.

Ross

 

[nataliek92]

Here is what I use: Modify ranges and path as needed.

Sub Get_data_from_ST_cvs_File()

Dim fName As String

fName = "C:\results\test.csv"

Range("A1:Z50000").ClearContents 'Clear any previuos data

    Open fName For Input As #1
    r = 1
    Do Until EOF(1)
    Line Input #1, LineFromFile
    lineitems = Split(LineFromFile, ",") 'Comma is the delimiter
    For c = 0 To UBound(lineitems)
        Cells(r, c + 1) = lineitems(c)
        Next c
        r = r + 1
        Loop
    Close #1
End Sub

Let me know how it works.

Ross

Hi Ross this looks more like what I was expecting! However, if my file were a .txt and the delimiter is a tab, do you know how I would modify this?
Sorry, I know I stated I was using a .csv earlier.
Also, where would this paste the data exactly? I can't see any explicit references in your code.
Thank you

 

[Cindy Ellis]

There's shouldn't be a need to open a connection, or to open and read the file "as input".
Try a variation of the following to suit your needs:

fileToOpen = "your path and filename here"

    Workbooks.OpenText Filename:=fileToOpen, _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, FieldInfo:=Array(1, 1)

The "Tab:=True" part tells it that the data is separated by tabs.

Hope that helps,

 

[nataliek92]

I get no such message once the Querytable and Connection have been deleted (Win XP, XL2010)



^This and what version of Excel do you use?

I am using the following code :

Sub ImportTextFile()
 
    With Worksheets("Prov_Data").QueryTables.Add( _
        Connection:="\\FILE.txt", _
        Destination:=Worksheets("Prov_Data").Range("$A$1"))
        .Name = "Prov_Data"
        .FieldNames = True
        .TextFileTabDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
    End With

    ThisWorkbook.Connections("Prov_Data").Delete

When I manually enter Data -> Connections .. there are non there however I am still getting the error message when I try to delete the data.... hmm.

 

[nataliek92]

I'm sorry, it turns out I'm actually using a .txt file. Sorry again!

 

[rpaulson]

Untested

Sub Get_data_from_ST_cvs_File()
Dim fName As String

Dim rs  As Worksheet
Set rs = Worksheets("Sheet1")  'set the remote sheet to write the data to

fName = "C:\results\test.txt"


rs.Range("A1:Z50000").ClearContents 'Clear any previous data on remote sheet

    Open fName For Input As #1
    r = 1 'this is the row number you want to start writing the data to change as needed
    Do Until EOF(1)
    Line Input #1, LineFromFile
    lineitems = Split(LineFromFile, vbTab) 'set the delimiter
    For c = 0 To UBound(lineitems)
        rs.Cells(r, c + 1) = lineitems(c) 'this is the column number to start writing the data to (c+1) is coulmn 1, change as needed
    Next c
        r = r + 1 'write next line to the next row
        Loop
    Close #1

End Sub

 

[Cindy Ellis]

That shouldn't matter...did you try it? I can put some more effort into it if necessary, but I open text files frequently using vba, and I've never opened a "connection" to do it. I have on occasion used the "open file for read" approach, but only if there's no consistent delimiter.