asked on

replace text in textstream before improting

how could i replace just certain words out of only the first line in the text file?

i just want to convert for instance "PH" to "Tel" and "NA" to "Company" etc

my current code is

Dim fso As New FileSystemObject
Dim ts As TextStream
Dim rowNo As Integer
Dim i As Integer
Dim xlRow() As String
Dim xla As Excel.Application
Dim xlw As Excel.Workbook
Dim xls As Excel.Worksheet

Set xla = CreateObject("Excel.Application")
Set xlw = xla.Workbooks.Add
Set xls = xlw.Sheets("Sheet1")
rowNo = 1
xla.Visible = False

Set ts = fso.OpenTextFile("c:\test\file.txt")
Do While ts.AtEndOfStream <> True
xlRow = Split(ts.ReadLine, ",", -1, vbTextCompare)
For i = 0 To UBound(xlRow)
xlRow(i) = Replace(xlRow(i), Chr(34), "")
xls.Cells(rowNo, i + 1) = xlRow(i)
Next i%
rowNo = rowNo + 1
Loop
ts.Close

xlw.SaveAs ("c:\test\file.xls")
xla.Quit
Set xla = Nothing

iozturk

Set ts = fso.OpenTextFile("c:\test\file.txt")
ts.skipline
Do While ts.AtEndOfStream <> True
xlRow = Split(ts.ReadLine, ",", -1, vbTextCompare)
For i = 0 To UBound(xlRow)
xlRow(i) = Replace(xlRow(i), Chr(34), "")
xls.Cells(rowNo, i + 1) = xlRow(i)
Next i%
rowNo = rowNo + 1
Loop
ts.Close

iozturk

ASKER CERTIFIED SOLUTION

jyokum

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CleanupPing

Hi Marcusw,
This old question (QID 20567698) needs to be finalized -- accept an answer, split points, or get a refund. Please see http://www.cityofangels.com/Experts/Closing.htm for information and options.