Command word from excel

**abousetta** · 04-19-2010, 08:26 AM

I have a text file (*.txt) that I want to modify before using in excel. I want to replace the return carriage with (Char 13) as long as the return carriage is not proceeded by a certain code. The code is fixed but there is a lot of different variations to the code, so if someone could help me with only one variation, I hope I will be able to do the rest.

So the macro should:

1. open the txt file via Word
2. replace the carriage breaks that are not proceeded by a code with (Char 13)
3. save the txt file

Example attached.

Thanks.

Abousetta

**Andy Pope** · 04-19-2010, 08:54 AM

No need for Word you can use file I/O commands instead.

Not 100% sure what the file output should have for those combined lines. If you use CHR(13) excel will convert to news rows again.

Sub RecodeText()

    Dim strFileIn As String
    Dim strFileOut As String
    Dim lngUnitIn As Long
    Dim lngUnitOut As Long
    Dim strBuf As String
    Dim strOut As String
    Dim blnAppend As Boolean
    
    strFileIn = "C:\temp\Before.txt"
    strFileOut = "C:\temp\After2.txt"
    
    lngUnitIn = FreeFile
    Open strFileIn For Input As lngUnitIn
    
    lngUnitOut = FreeFile
    Open strFileOut For Output As lngUnitOut
    
    Do While Not EOF(lngUnitIn)
        Line Input #lngUnitIn, strBuf
        
        If Left(strBuf, 8) = "Address:" Then
            strOut = ""
            blnAppend = True
        ElseIf Left(strBuf, 11) = "Occupation:" Then
            Print #lngUnitOut, strOut
            blnAppend = False
        End If
        
        If blnAppend Then
            ' not yet
            strOut = strOut & strBuf & Chr(13)
        Else
            Print #lngUnitOut, strBuf
        End If
    Loop
    Close lngUnitIn
    Close lngUnitOut
    
End Sub

**abousetta** · 04-19-2010, 01:43 PM

Hi Andy,

Thanks. And as you correctly pointed out, in Excel is sends each new carriage return to a new row. What I was hoping for was to allow the contents to all go into one cell. When I recorded what I wanted using the vb recorder, I got this code:

    ActiveCell.FormulaR1C1 = "Some Street" & Chr(10) & "Some City" & Chr(10) & "Some State" & Chr(10) & "Some Country"

Of course I typed this directly into excel but I think it will better illustrate what I am after.

Thanks for your help.

Abousetta

**Andy Pope** · 04-20-2010, 04:13 AM

Did you try changing the CHR(13) part of the code to CHR(10) ?

**abousetta** · 04-22-2010, 12:14 AM

Hi Andy,

Sorry I have not responded sooner but I was off the grid for a while. Char(10) seems to work as you suggested and I finally got it working and tweeked the code just a bit to fit with the rest of the project. Even so I still have a few questions I hope you can help me with:

Here is the code as it stands right now:

Option Explicit

Sub RecodeText()

    Dim strFileIn As String
    Dim strFileOut As String
    Dim lngUnitIn As Long
    Dim lngUnitOut As Long
    Dim strBuf As String
    Dim strOut As String
    Dim blnAppend As Boolean
    

    strFileIn = Application.GetOpenFilename("Text Files,*.txt")
    If strFileIn = "False" Then Exit Sub
    
    strFileOut = Application.GetSaveAsFilename(InitialFilename:=strFileIn, FileFilter:="Text Files (*.txt), *.txt")

    lngUnitIn = FreeFile
    Open strFileIn For Input As lngUnitIn
    
    lngUnitOut = FreeFile
    Open strFileOut For Output As lngUnitOut
    
    Do While Not EOF(lngUnitIn)
        Line Input #lngUnitIn, strBuf
        
        If Left(strBuf, 6) = "AB  - " Then
            strOut = ""
            blnAppend = True
        ElseIf Left(strBuf, 6) = "**  - " Then
            Print #lngUnitOut, strOut
            blnAppend = False
        End If
        
        If blnAppend Then
            ' not yet
            strOut = strOut & strBuf & Chr(10)
        Else
            Print #lngUnitOut, strBuf
        End If
    Loop
    Close lngUnitIn
    Close lngUnitOut
    
End Sub

The changes so far are minor as I just added Application.GetOpenFilename and Application.GetSaveAsFilename and changed the headers to represent what truly comes from the database. All the headers are a fixed length of six characters, spaces or numbers. In the example above I used "AB - " for the first header and "** - " for the second header. Is there any way for me to modify the code that I don't have to put down all the possibilities. The reason I ask this is because I noticed that the code will replace all the return carriages until it finds the stopping header. What I have found is that this stopping header (i.e. the next header) may be different from one file to the next.

The other question I have is when I try to save the new output file as the same name as the original file, it gives me error at this line of code:

Open strFileOut For Output As lngUnitOut

Is there anything I can do to allow me to overwrite the original file?

Thanks for all your help.

Abousetta

**Andy Pope** · 04-22-2010, 05:10 AM

First, you can not use the same file name for input and output. You need to output to a differently named file.
Once you complete the restructure of the file you can close both files and then use the KILL command to delete the original and the NAME to rename the revised output back to the original name.

Handling different delimiters is really down to find something consistent with the header that you can use to identify each part of the record.

Perhaps it is more simple in your case. Each record is say 7 rows long and you want to append rows 3, 4, 5, 6