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.

 

[Cindy Ellis]

The following works for me on a simple text file with information separated by tabs, with differing amounts of data in each row:

Sub Open_tab_separated_file()Dim MyFile As String
MyFile = "C:\Users\CELLIS\Desktop\test.txt"
        Workbooks.OpenText FileName:=MyFile _
        , Origin:=xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier _
        :=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:= _
        False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array _
        (1, 1))
End Sub

 

[Comfy]

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.

Some more appropriate answer have been provided but the reason this isn't working as expected is because you aren't using the code that I posted.

You are deleting the connection but you are not also deleting the Query Table.

ThisWorkbook.Connections(1).Delete
ActiveSheet.QueryTables("AllDevicesAndOwners").Delete

If both are deleted then no warning message is displayed.

 

[rpaulson]

Sorry if i'm hijacking this post.


Cindy Ellis I love your method. Is there a way to have the data dump into the existing worksheet.? instead of opening a new file?

Ross

 

[nataliek92]

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,

Cindy, thank you for your help however the code does not do anything.

The runs fine with no errors however there is no output pasted into my workbook. Am I missing something?

Sub ImportTextFile()
    Dim FileToOpen As String
    FileToOpen = "myfile"
    
    Workbooks.OpenText Filename:=FileToOpen, _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, FieldInfo:=Array(1, 1)
        
End Sub

 

[Momentman]

Cindy, thank you for your help however the code does not do anything.

The runs fine with no errors however there is no output pasted into my workbook. Am I missing something?

Sub ImportTextFile()
    Dim FileToOpen As String
    FileToOpen = "myfile"
    
    Workbooks.OpenText Filename:=FileToOpen, _
        Origin:=437, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, FieldInfo:=Array(1, 1)
        
End Sub

I think "filetoOpen" should contain the full path to your file not just the file name
e.g

filetoopen="C:\Users\Momentum\Documents\Bluetooth Exchange Folder\myfile.xls"

 

[nataliek92]

Some more appropriate answer have been provided but the reason this isn't working as expected is because you aren't using the code that I posted.

You are deleting the connection but you are not also deleting the Query Table.

ThisWorkbook.Connections(1).Delete
ActiveSheet.QueryTables("AllDevicesAndOwners").Delete

If both are deleted then no warning message is displayed.

Sorry I forgot to mention I did not use that part as it brought back error messages.

I have tried both the following lines :

ActiveSheet.QueryTables("AllDevicesAndOwners").Delete
    Worksheets("Prov_Data").QueryTables("Prov_Data").Delete

But neither work. They both bring back error "Subscript out of range".

 

[nataliek92]

I think "filetoOpen" should contain the full path to your file not just the file name
e.g

filetoopen="C:\Users\Momentum\Documents\Bluetooth Exchange Folder\myfile.xls"

Momentman, I have used a full path. I just renamed it for this post for security purposes. Sorry for any confusion.

 

[Cindy Ellis]

The runs fine with no errors however there is no output pasted into my workbook. Am I missing something?

I think I may have missed something in the original request. The code above opens the text file as an Excel workbook, but it doesn't paste it into an Excel workbook that is already open, which might be the case if you have formatting or formulas, etc, already established that you want to use with the new data.
In most cases, the newly opened file will become the active workbook, but in the context of a larger macro there may be any number of things that could be happening.
Just to check the logic on your system, could you try it on its own first as a separate macro to make sure that the file opens correctly?
Once you've confirmed that, the next step(s) to get it into the target workbook would involve something like Select All, Copy, Paste as Values, into the other workbook, then close the text file. I can help with that if that's the direction you want to go.
Hope that helps,

 

[Comfy]

ThisWorkbook.Connections(1).Delete
ActiveSheet.QueryTables("Prov_Data").Delete

Did you try this?

 

[nataliek92]

Did you try this?

I used your exact code and got a "Subscript out of range" error on the Activesheet.Querytables... line.