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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Peter

Reply | Quote

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

Reply | Quote

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.

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

Reply | Quote

Try these small adaptations:

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.

Reply | Quote

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

Reply | Quote