Home » Blogs » Nick's blog

20. VBA Tips - Import A Text File

Nick's picture
Submitted by Nick on 13 May, 2009 - 09:30


A lot of VBA code is written to import files into spreadsheets.

  • Today, we'll look at how to import a text file

Here's our text file, and we want to import it onto sheet1 of our spreadsheet starting at Range B7.

From this:

import-a-text-file

To this:

import-a-text-file

Here's the code we use:

import-a-text-file

 

Explanation:

  1. If you want to replicate this, download TextFile.TXT and save it to your C drive
  2. Download the example file
  3. Pressing the button will run the sub procedure: ImportTextFile
  4. The first thing we do is to set an object myTextFile equal to the opened text file
  5. The second line copies the data from the text file onto the spreadsheet
    • The Copy method requires a source and a destination
    • The Source is: myTextFile.Sheets(1).Range("A1").CurrentRegion
    • This means: copy all the data that is linked to A1
    • The destination is: ThisWorkbook.Sheets(1).Range("B7")
  6. After copying, we close the text file and the (FALSE) means don't save any changes.

Training Video on how to Import A Text File in Excel:

AttachmentSize
import-a-text-file.xls39.5 KB
TextFile.TXT60 bytes
»

Help please

Submitted by luhlacom on 23 February, 2015 - 08:59.

Can you help me please ?

Can I add dialog box to your code ? I would like use code to import file but i need change absolute path to dialog box.

Thanks you for answer.

Regards Lukas

»

Importing Multiple files into 1 excel worksheet

Submitted by Yaseer on 20 February, 2013 - 12:29.

Hi

I have large text files that I want to copy into excel. They are about 6687 rows down and the columns end at JDY.

I tried to modify your code but it gives an error telling me the file can't be found. Yet it works when I paste the whole path.

Here is the code:

Sub ImportTextFile()

Dim path As String
Dim fname As String
Dim row As Integer
Dim col As Integer

row = 1
col = 1

path = "C:\Exported Results\"
fname = Dir(path & "*.txt")
Set myTextFile = Workbooks.Open(fname)
myTextFile.Sheets(1).Cells(row, col).CurrentRegion.Copy _
ThisWorkbook.Sheets(1).Range("A1")
myTextFile.Close (False)
fname = Dir
End Sub

I tried it this way before I begin to loop it. Can u tell me what I'm doing wrong?

»

Search within opened text then copy part of it

Submitted by Anonymous on 8 November, 2010 - 01:10.

How we can specify part of the text to be copied only?
for example Can we search to a keyword then imported on line only

»

search for piece of text...

Submitted by Anonymous on 8 November, 2010 - 11:31.
a simple way to accomplish what you want to achieve is to import run the procedure as it is, then look for the data you want using the FIND function in VBA, and clear the other rows. somthing like this: 
set FoundIt = cells.find(ThingToLookFor)
if not FoundIt is nothing then
' you've found it
msgbox "Found in row:" & FoundIt.row
foundRow = FoundIt.row
else
' you didn't find it
end if
 
for i = 2 to cells(1,1).usedrange.rows.count
if i<>foundRow then
rows(i).clearcontents
end if
next
 
' then sort the UsedRange...
»

It works, but when the *csv

Submitted by Anonymous on 31 July, 2012 - 15:59.

It works, but when the *csv contains more than 50,000 rows, this code will take too long time to "open", "copy" and close.

You can use csv file as a database, "transferspreadsheet" to get the data without opening it

»

how to import excel files using button

Submitted by brijendraydv on 23 October, 2012 - 10:13.

Hi Experts,

I want to know how to import excel file using button in a sheet.

Thanks,

»
Almir's picture

how to import excel files using button

Submitted by Almir on 23 October, 2012 - 10:25.

You can import it once and save it as a macro.
Then assign that macro to a button.

»