none
Importing MS Word to MS Excel RRS feed

  • General discussion

  • Could any body please guide me as to importing MS Word text to MS Excel using Excel VBA codes.
    Tuesday, January 22, 2013 11:02 AM

All replies

  • Your request is not clear.

    Here is some code that reads a table from Word into the active worksheet of Excel. It prompts you for the word document as well as the table number if Word contains more than one table.

    Sub ImportWordTable()
    Dim wdDoc As Object
    Dim wdFileName As Variant
    Dim TableNo As Integer 'table number in Word
    Dim iRow As Long 'row index in Excel
    Dim iCol As Integer 'column index in Excel
    
    wdFileName = Application.GetOpenFilename("Word files (*.doc),*.doc", , _
    "Browse for file containing table to be imported")
    
    If wdFileName = False Then Exit Sub '(user cancelled import file browser)
    
    Set wdDoc = GetObject(wdFileName) 'open Word file
    
    With wdDoc
    TableNo = wdDoc.tables.Count
    If TableNo = 0 Then
    MsgBox "This document contains no tables", _
    vbExclamation, "Import Word Table"
    ElseIf TableNo > 1 Then
    TableNo = InputBox("This Word document contains " & TableNo & " tables." & vbCrLf & _
    "Enter table number of table to import", "Import Word Table", "1")
    End If
    With .tables(TableNo)
    'copy cell contents from Word table cells to Excel cells
    For iRow = 1 To .Rows.Count
    For iCol = 1 To .Columns.Count
    Cells(iRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
    Next iCol
    Next iRow
    End With
    End With
    
    Set wdDoc = Nothing
    
    End Sub

    This macro should be inserted into Excel (not Word) and put into a standard macro module rather than into the worksheet or workbook event code modules. To do this, go to the VBA (keyboard Alt-TMV), insert a macro module (Alt-IM), and paste the code into the code pane. Run the macro from the Excel interface as you would any other (Alt-TMM).

    If your document contains many tables, as would be the case if your 100+ page table is actually a separate table on each page, this code could easily be modified to read all the tables. But for now I am hoping it is all one continuous table and will not require any modification.

    Original page: http://stackoverflow.com/questions/4465212/macro-to-export-ms-word-tables-to-excel-sheets

    Regards,

    Wilson
    Tuesday, January 22, 2013 1:44 PM
  • Thanks Wilson,  working great.

    This code imports MS Word tables into MS Excel, however is it possible to import the free text which is in tabular form into excel.  I am grateful for your help.

    Thanks for support.

    Girish

    Friday, January 25, 2013 7:42 AM