Code to open an .rtf file in Word

[JW]

Hi, I'm automating a process that includes transferring data from an .rtf file into Excel. Doing this manually seems easiest by opening it in Word, selecting all and then copy/pasting back into Excel. I'd like to do this in a macro - can anyone give me some code to open a certain file (say \\server\Share\file.rtf) in Word and then select all data and copy, or suggest a way to do this all in Excel (each way I've opened it in Excel has brought in all kinds of code too). I've tried around ShellExecute but can't get this to work either.
Thanks in advance

 

[Andrew Poulsom]

Try something like this:

Sub Test()
    Dim WordApp As Object
    Set WordApp = CreateObject("Word.Application")
    With WordApp
'       Change file name to suit
        .Documents.Open Filename:="P:\TEMP\MrExcel\RTFFile.rtf"
        .ActiveDocument.Select
        .Selection.Copy
    End With
    ActiveSheet.Range("A1").Select
    ActiveSheet.Paste
    WordApp.Quit
    Set WordApp = Nothing
End Sub

 

[JW]

Andrew, that's worked a treat
Many thanks

 

[iknowu99]

This is a great treat indeed! How would one search for a word...say "Monkey" and return the line instead of the whole document?

 

[Andrew Poulsom]

Try:

Sub Test()
    Const wdLine As Integer = 5
    Const wdExtend As Integer = 1
    Dim WordApp As Object
    Set WordApp = CreateObject("Word.Application")
    With WordApp
'       Change file name to suit
        .Documents.Open Filename:="P:\TEMP\MrExcel\RTFFile1.rtf"
        .Visible = False
        With .Selection.Find
            .Text = "Monkey"
            .Execute
        End With
        With .Selection
            .HomeKey Unit:=wdLine
            .EndKey Unit:=wdLine, Extend:=wdExtend
        End With
        .Selection.Copy
    End With
    ActiveSheet.Range("A1").Select
    ActiveSheet.Paste
    WordApp.Quit
    Set WordApp = Nothing
End Sub

 

[iknowu99]

this is a neat stunt, thankyou, have you seen anything like this with pdf files?

 

[Andrew Poulsom]

have you seen anything like this with pdf files?

Sorry no.

 

[iknowu99]

Hey Andrew, this post really helped me. I am wondering if you know how to incorporate Outlook.

The goal is to
-check for emails with specific Subject line every 5 minutes
-when a new one arrives, open it and download the .rtf / .doc attachment to hard drive
-then launch the WordApp module


EDIT:
I started a new topic
http://www.mrexcel.com/board2/viewtopic.php?t=254808
thanks.