to read text tablimited file

[pol]

Hi all

I have a text tablimited file in c:\excel macros folder with the following
format. Please let me know how I can read that file from that folder and to
store in a vriable

1 Description1
2 Description2
3 Description3
4 Description4



Columns("P").Select
Range("P1").Activate

Selection.Replace What:="1", Replacement:="Description1",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Selection.Replace What:="2", Replacement:="Description2",
LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

With Regards

Pol

 

[pol]

if the excel file in P range , have the value any one of data in the
textfile 1 or 2, or I want to replace with corresponding description

 

[pol]

if the excel file in P range , have the value any one of data in the
textfile 1 or 2, or I want to replace with corresponding description

 

[Joel]

The code below uses a dailog box to open the tab delimited file. the code
creates a tempory workbook with the tab data and then replaces everyting in
column P according to the Tab file.


Sub Replacetext()

Set ModifySht = ActiveSheet

filetoopen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If filetoopen = False Then
MsgBox ("Cannot Open File - Exiting Macro")
Exit Sub
End If

Set newbk = Workbooks.Add
Set TmpSht = newbk.Sheets(1)

With TmpSht.QueryTables.Add( _
Connection:="TEXT;" & filetoopen, _
Destination:=Range("A1"))
.Name = "tabfile"
.SaveData = True
.AdjustColumnWidth = True
.TextFileParseType = xlDelimited
.TextFileTabDelimiter = True
.Refresh BackgroundQuery:=False
End With


With TmpSht
RowCount = 1
Do While .Range("A" & RowCount) <> ""
OldData = .Range("A" & RowCount)
NewsData = .Range("B" & RowCount)

ModifySht.Columns("P").Replace _
What:=OldData, _
Replacement:=NewData, _
LookAt:=xlWhole

RowCount = RowCount + 1
Loop

End With

newbk.Close savechanges:=False
End Sub

 

[Joel]

The code below uses a dailog box to open the tab delimited file. the code
creates a tempory workbook with the tab data and then replaces everyting in
column P according to the Tab file.


Sub Replacetext()

Set ModifySht = ActiveSheet

filetoopen = Application _
.GetOpenFilename("Text Files (*.txt), *.txt")
If filetoopen = False Then
MsgBox ("Cannot Open File - Exiting Macro")
Exit Sub
End If

Set newbk = Workbooks.Add
Set TmpSht = newbk.Sheets(1)

With TmpSht.QueryTables.Add( _
Connection:="TEXT;" & filetoopen, _
Destination:=Range("A1"))
.Name = "tabfile"
.SaveData = True
.AdjustColumnWidth = True
.TextFileParseType = xlDelimited
.TextFileTabDelimiter = True
.Refresh BackgroundQuery:=False
End With


With TmpSht
RowCount = 1
Do While .Range("A" & RowCount) <> ""
OldData = .Range("A" & RowCount)
NewsData = .Range("B" & RowCount)

ModifySht.Columns("P").Replace _
What:=OldData, _
Replacement:=NewData, _
LookAt:=xlWhole

RowCount = RowCount + 1
Loop

End With

newbk.Close savechanges:=False
End Sub