Monday, May 2, 2011

Reading/importing tab delimited text file using VBA–Excel


One of my friend, who is a mechanical engineer, had to import a tab delimited text file in Excel. He gets this (these) file(s) as input for God Knows What Smile. He asked for my help and then I realized it is worth blogging about as may be helpful for many of my civil/mech/chem…. friends.
So first thing first:-
How to create a tab delimited text file
1. Open notepad –> Write text in first column –> press tab –>write in second column and so on. –> save the file with .tab extension.
2. Open excel-> enter values in different column-> Open the File menu and select the Save as... command.-> In the Save as type drop-down box, select the Text (tab delimited) (*.txt) option.->Select the Save button. If you see warning messages pop up, select the OK or Yes button.
Here I am going to write a macro to read all the values
Sub ReadTextFile()
 
  Dim iRow      As Long
  Dim Fname     As Variant
  Dim Record    As String
  Dim P         As Variant
  Dim iCol      As Integer
 
  Fname = Application.GetOpenFilename("Text Files (*.txt),*.txt", , _
           "Select Text Data File")
  If Fname = False Then Exit Sub
 
  Open Fname For Input As #1
  iRow = 1
  Line Input #1, Record
  Do Until EOF(1)
     P = Split(Record, vbTab)
    
     For iCol = 1 To 3
        Cells(iRow, iCol) = P(iCol - 1)
     Next iCol
    
     iRow = iRow + 1
     Line Input #1, Record
  Loop
  Close 1
End Sub
Here I am writing a macro to read value present in a specific cell.
We will get the information about cell from user. Supposing he has provide row and cell value in 15th column (under N).
Sub ReadCell()
    Dim iRow As Long
    Dim iCol As Integer
   
    iRow = CInt(Cells(2, 15))
    iCol = CInt(Cells(1, 15))
   
    Dim Fname     As Variant
    Dim Record    As String
    Dim P         As Variant
    Dim iTemp      As Integer
 
  Fname = Application.GetOpenFilename("Text Files (*.txt),*.txt", , _
           "Select Text Data File")
  If Fname = False Then Exit Sub
 
  Open Fname For Input As #1
  iTemp = 1
  Line Input #1, Record
  Do Until EOF(1)
     P = Split(Record, vbTab)
    
     If iTemp = iRow Then
     MsgBox (P(iCol - 1))
     Exit Sub
     End If
         
     iTemp = iTemp + 1
     Line Input #1, Record
  Loop
   
  Close 1

End Sub

Disclaimar
I have not applied validations and it is by no means the professional code which we would like. But this something you can really build upon. Also contact me if you need something specific.

No comments:

Post a Comment