Import Text File To Excel

nataliek92

New Member
Joined
Oct 29, 2014
Messages
40
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.
 
Last edited:

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Simply record a macro while you import the text file and you will have a code that you can cleanup and use
 
Upvote 0
Which version of Excel are you using?

I would setup a connection to the text file, "Get External Data - From Text"

then edit the connection settings and untick the, "Prompt for filename on refresh" box.

Any time you wanted to update the info you click the "Refresh All" button on the ribbon (2007 +)
 
Last edited:
Upvote 0
Hi both, thanks for your replies. Sorry I should have specified that I don't want there to be a live connection between the excel document and the text file. If I import the text file via Get External Data -> From Text, a live connection is set up. This will slow down my vba code significantly.

When importing from a recordset, I would use ".Copyfromrecordset Recordsetname" in VBA. This then pastes the values into a specified cell. Ideally I would like a ".Copyfromtext" property, but of course this doesn't exist.
 
Upvote 0
you can use this as a template and adjust accordingly as you pick thru the options, it currently adds a new sheet and returns its name, but you can remove that bit
Code:
Sub LoadLDData(LDSheetName As String)
Sheets.Add After:=Sheets(Sheets.Count)
With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;U:\AllDevicesAndOwners.csv", Destination:=Range("$A$1"))
    .Name = "AllDevicesAndOwners"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
ActiveSheet.Name = LDSheetName
End Sub

you can also create a SQL query to the text file if you want to be a bit more selective and return a record set, I have examples of that but its a bit more involved in setting it up
 
Upvote 0
Thanks jimrward, I'm sure your code works fine however that would Open a connection to the file.

I would just like the values to be imported, if possible.
 
Upvote 0
how many columns are in your text file, and what are data types of each column, I will dig out some ADO/SQL query stuff to match
 
Upvote 0
how many columns are in your text file, and what are data types of each column, I will dig out some ADO/SQL query stuff to match

could we say that the number of columns can vary? and it shouldn't matter what data types are in each column (surely?), as I just want the data copied and pasted as values into Sheet1....

I'm starting to think it may be impossible, but it just seems so simple in my head! haha

thanks
 
Upvote 0
Just remove the connections after you import the text... I use this all the time, sometimes I have over 100k lines. Hope this helps

Code:
'************************************************************
' Opening the Report file (as Text)
'************************************************************

fName = Application.GetOpenFilename("Text Files (*.txt), *.txt") 'Get User to provide file and path of the imported text file
If fName = "False" Then Exit Sub
    
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, Destination:=Range("$A$1"))
        .Name = "MyReport"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2, 1, 1, 1, 1, 1, 2, 2, 2, 1, 2, 2, 2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

    
'************************************************************
' Removing Connections to the Text File we just opened
'************************************************************
    
Dim i As Long

For i = 1 To ActiveWorkbook.Connections.Count
If ActiveWorkbook.Connections.Count = 0 Then GoTo NoConnections
    ActiveWorkbook.Connections.Item(i).Delete
i = i - 1
Next i

NoConnections:
 
Upvote 0
Use Jim's code to import the data then delete the connection and QueryTable

Code:
Sub LoadLDData()
Sheets.Add After:=Sheets(Sheets.Count)
With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;C:\New Text Document.txt", Destination:=Range("$A$1"))
    .Name = "AllDevicesAndOwners"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
    .TextFileTrailingMinusNumbers = True
    .Refresh BackgroundQuery:=False
End With
On Error Resume Next
ThisWorkbook.Connections(1).Delete
ActiveSheet.QueryTables("AllDevicesAndOwners").Delete
On Error GoTo 0
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top