Excel/data from Word to Excel
QUESTION: Dear Gulshan,
How are You doing? I hope You can help me a little with the subject above.
Manually it is quite simple to do (Ctrl+A and C in the Word rtf file and Ctrl+P in Excel workbook) and all look fine as I expected...
... but I have tome hundred files for repeating the procedure.
I have found a code (below) which works fine but I have some data out of the tables in the rtf files.
I would be glad if You can comment on how to change or what to add to the code below to pick up data outside of tables...
or if You have something universal (wholestory) which picks up everyting from the rtf file and puts it into excel book as it is that also would be fine.
I would really appreciate your kind assistance.
Dim myPath As String, myFile As String, myText As String
Dim xlRow As Long, xlCol As Long
Dim t As Table
Dim r As Row
Dim c As Cell
Dim xl As Object
Set xl = CreateObject("excel.application")
xl.Visible = True
'Here put your path where you have your documents to read:
myPath = "C:\Users\Documents\Test\
myFile = Dir(myPath & "*.rtf")
xlRow = 1
Do While myFile <> ""
Documents.Open myPath & myFile
For Each t In ActiveDocument.Tables
For Each r In t.Rows
xlCol = 1
For Each c In r.Range.Cells
myText = c.Range.Text
myText = Replace(myText, Chr(13), "")
myText = Replace(myText, Chr(7), "")
xl.ActiveWorkbook.ActiveSheet.Cells(xlRow, xlCol) = myText
xlCol = xlCol + 1
xl.ActiveWorkbook.ActiveSheet.Cells(xlRow, xlCol + 1) = myFile
xlRow = xlRow + 1
xlRow = xlRow + 1
myFile = Dir
ANSWER: Hello Attis,
Can you please clarify the below so I can find a solution:
1. Are you looking only for the tables in rtf files or are you looking for all the data which is there in the rtf files?
2. What if there are images or other data in the rtf files, do you want me to copy those as well?
The macro which you have shared above will work fine when there are tables, but the moment you have more data / images, this will not give the required results.
---------- FOLLOW-UP ----------
QUESTION: Hello Gulshan,
Thenk you for the reply!
Clarifying the situation:
1. I would like to have all the data (1 - 2 pages) copied to excel.
2. There are no images (there won't be) in the rtf files.
There are only some tables (the number of them are always the same
but the size of some of them changes) and some text around the tables that are necessary for me.
I am planning to process each rtf content one by one after pasting to excel with a different procedure (code) but I thing I can handle that part.
Thank you for your kind assistance in advance!
Is it possible for you to send me a sample of your rtf file and the excel output you get after running the macro?
If so, can you send it to email@example.com?
I'll confess that most of my expertise is around handling excels, rtfs and other formats give me a tough time.