Convert, save and change file extention.

[avmann]

Alright, I have an RTF file that I need to programatically open in wordpad, saved as a TXT file. After it is saved, I need to change the file extention of the file to a csv file which will give me an excel parse-able version. So far currently I am calling a batch file which is opening the file in wordpad and changing the file extention with no problem. But how do I get the file saved as a text file? I figure I can call another script that is capable, but I need direction. Here is the batch file I am using.

start write "ITEMS_EXPORT.rtf"
del "ITEMS_EXPORT.csv"
del "ITEMS_EXPORT.txt"
cls
echo "save RTF file as ITEMS_EXPORT.txt text file to continue."
pause
RENAME "ITEMS_EXPORT.txt" "ITEMS_EXPORT.csv"

I then go back to the excel code that parses the file. Any info would help. Thanks!

 

[Macropod]

Hi avmann,

I doubt you'll be able to get Wordpad to respind to any batch file input. All you're really achieving now is copying the file and saving it with a new extension - which can be done without the batch file calling any app. You could probably achieve what you're after with the Windows Scripting Host, but you'd need to post your query in an appropriate forum.

 

[BrianB]

Here is a method that uses Word as an intermediary.

'=============================================================================
'- OPEN A .RTF FILE IN EXCEL
'- ** This should work correctly with any .rtf file **
'- USES WORD AS AN INTERMEDIARY TO CONVERT TO A .CSV TEXT FILE
'- Brian Baulsom May 2010
'=============================================================================
'- Word
Dim WordApp As Object           ' Word application object
'- Excel
Dim RTFfile As String
Dim CSVfile As String           ' TARGET FILE same name as .RTF .CSV SUFFIX
Dim ExcelCSV As Worksheet
'=============================================================================
'- MAIN ROUTINE
'=============================================================================
Sub RTF_TO_WORKSHEET()
    '-------------------------------------------------------------------------
    '- INITIALISE VARIABLES
    RTFfile = "F:\Test.rtf"
    CSVfile = Left(RTFfile, Len(RTFfile) - 3) & "csv"   ' same name as RTF
    '-------------------------------------------------------------------------
    '- DELETE EXISTING .CSV
    On Error Resume Next    ' in case file does not exist
    Kill CSVfile
    On Error GoTo 0         ' reset normal error trapping
    '-------------------------------------------------------------------------
    '- WORD : RTF TO CSV
    Set WordApp = CreateObject("Word.Application")
    With WordApp
        .Visible = False        ' hide Word
        .Documents.Open Filename:=RTFfile
        .ActiveDocument.SaveAs Filename:=CSVfile, FileFormat:=wdFormatText
        .ActiveDocument.Close
        .Quit
    End With
    Set WordApp = Nothing
    '-------------------------------------------------------------------------
    '- EXCEL : OPEN THE .CSV FILE & PROCESS
    Workbooks.Open Filename:=CSVfile, Delimiter:=","
    Set ExcelCSV = ActiveSheet
    With ExcelCSV
        MsgBox ("CSV File Range A1 = " & .Range("a1").Value)
        ' more code here ......................
 
 
    End With
    '-------------------------------------------------------------------------
    MsgBox ("Done")
    '-------------------------------------------------------------------------
End Sub
'=============================================================================

 

[avmann]

Thanks for the code sample. I did actually have a version similar to this that I tried, however for some reason word did not convert the RTF to the TXT file properly. Or should I say, It didn't convert the file like wordpad does. It had extra formatting and when I found the way to get rid of the extra formatting, it had additional line spacing in between the lines that I wanted to parse that made it immpossible (difficult) to parse correctly.

I did end up coming up with a solution that worked for me. It usees some pretty ugly programming technique, but it works. I don't have the code with me now, but I will post it when I do.