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
»

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...
»