Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    .RTF to Excel (Excel 2k and Word 2k)

    What is the best way to get data from a table in an RTF file into Excel? at the moment I use code to:-
    Open Word in the background
    Copy the data
    Close Word
    Use send keys to paste the data to a range in Excel.

    This works but is not very stable or elegant.

    I have to use Send keys to paste as the .Paste Method ignores regional settings and pastes values as texts!


    Peter

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .RTF to Excel (Excel 2k and Word 2k)

    As far as I know, copy the table in Word and then position your cursor in a cell on the spreadsheet and click the paste icon.

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .RTF to Excel (Excel 2k and Word 2k)

    I can copy and paste OK doing things by hand but I am setting up an automatic system, Click a button in Excel to import the data sort it and report.
    It is the copy/paste in code that is causing the problem.
    I was hoping that there would be a way of importing/linking to the table directly to avoid the copy/paste bit <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Peter

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: .RTF to Excel (Excel 2k and Word 2k)

    What does the Word document look like. Is the table a Word table or just text? Is it the first table? How can Excel automatically recognize it? Can you save the word doc as HTML and then use Excel's Data | GetExternalData | NewWebQuery | Table 1 menu? --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .RTF to Excel (Excel 2k and Word 2k)

    The Word document is a RTF file with a single table in it. It is a propper table not just a formatted list of texrt, with a Header row and aprrox 1500 rows of data.
    It is generated as a report from a "Bought in" database that has no other output options.
    I currently use the following code but is is unreliable. <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>

    Set WordApp = CreateObject("word.application")
    ' Use WordApp to access Words objects
    With WordApp
    .Documents.Open FileName:=strReportPath
    .ActiveDocument.Tables(1).Select
    .Selection.Copy
    End With

    Range("b4").Select
    'ActiveSheet.Paste
    SendKeys "^v", True
    ' sends ctr-V to paste, as ActiveSheet.Paste pasted differently to pasting by hand
    With WordApp
    .ActiveDocument.Close
    .Quit
    End With
    Set WordApp = Nothing ' clear the reference.

    It will work most of the time, but will occasionally paste the document back onitself instead of in Excell!

    Not sure about using HTML but I will have a look, I am sure there is no output in that format from the DB but it might be more reliable to automate Word to do it than the copy/paste routine

    Thanks

    Peter

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .RTF to Excel (Excel 2k and Word 2k)

    Try these small adaptations:

    <pre>Set WordApp = CreateObject("word.application")
    ' Use WordApp to access Words objects
    With WordApp
    .Documents.Open FileName:=strReportPath
    .ActiveDocument.Tables(1).Select
    .Selection.Copy
    End With
    ActiveSheet.Range("B4").Select
    ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
    False
    With WordApp
    .ActiveDocument.Close
    .Quit
    End With
    Set WordApp = Nothing ' clear the reference.

    </pre>


  7. #7
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: .RTF to Excel (Excel 2k and Word 2k)

    I have the same problem with Paste Special as I did with the normal paste method in code.
    Excel ignores regional setting when you paste in code so that Currency

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •