You are here:

Excel/data from Word to Excel

Advertisement


Question
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.

Best wishes
attis

Sub Word_tables_from_many_docx_to_Excel()
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.Workbooks.Add
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
       Next c
       xl.ActiveWorkbook.ActiveSheet.Cells(xlRow, xlCol + 1) = myFile
       xlRow = xlRow + 1
    Next r
    xlRow = xlRow + 1
 Next t

 ActiveWindow.Close False

myFile = Dir
Loop

End Sub

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.

Please advise.

Thanks,
Gulshan.

---------- 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!
Best regards,

attis

Answer
Hello Attis,

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 gulthemacroguy@gmail.com?

I'll confess that most of my expertise is around handling excels, rtfs and other formats give me a tough time.

Thanks,
Gulshan.
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


GULSHAN PURSWANI

Expertise

I can answer questions related to the following topics: 1. MS Excel - Creating and Linking Formulae, Running Pivot Tables, Vlookup etc. 2. Macros / VBA - Creating Macros to do specific jobs. Importing / Exporting / Validating Data in excel through Macros.

Experience

I've been working on excel for over 10 years and on VBA macros for over 3 years now.

Education/Credentials
Bachelor of Commerce, Chartered Accountant from The Institute of Chartered Accountants of India

©2016 About.com. All rights reserved.