?
Solved

replace text in textstream before improting

Posted on 2003-03-29
4
Medium Priority
?
322 Views
Last Modified: 2011-09-20
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
0
Comment
Question by:Marcusw
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 4

Expert Comment

by:iozturk
ID: 8232452
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
0
 
LVL 4

Expert Comment

by:iozturk
ID: 8232460
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
0
 
LVL 12

Accepted Solution

by:
jyokum earned 1000 total points
ID: 8232689
dim strLine as String
dim blnFirstLine as Boolean
blnFirstLine = True

Set ts = fso.OpenTextFile("c:\test\file.txt")
Do While ts.AtEndOfStream <> True
   strLine = ts.ReadLine
   If blnFirstLine then
      strLine = Replace(strLine,"PH","Tel")
      strLine = Replace(strLine,"NA","Company")
      blnFirstLine = False
   End If
   xlRow = Split(strLine, ",", -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
0
 

Expert Comment

by:CleanupPing
ID: 8531344
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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses
Course of the Month8 days, 12 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question