Sorting data based in word into excel

[theegg1984]

hi.

Does anyone know if there is anyway of sorting data from a word document into an excel spreadheet?

<a href="http://s72.photobucket.com/albums/i174/patburstsintoflames/?action=view&current=imageforforum.jpg" target="_blank"><img src="http://i72.photobucket.com/albums/i174/patburstsintoflames/imageforforum.jpg" border="0" alt="Photobucket"></a>

I want to sort be able to export the data according to the column on the far left hand side (month), into excel.


ideally i would have liked to have all the data in excel as it would make things easier but in this instance i need to keep all the data input in word.

Is there anything i can do?

 

[VoG]

The following macro (I don't remember where I got this from) will import data from a Word table into Excel. Run it from a blank worksheet. It will prompt for the Word filename and the table number in the document. Once imported you can sort how you like in Excel.

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

 

[theegg1984]

hey

thanks a lot for that.

it only seems to select data from the alst page in the document, is there anyway i can get it so it selects all the data contained within the word document?

thanks

 

[VoG]

I've modified it to grab the data from every table in the Word document and place it on a separate sheet in Excel:

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
    If wdDoc.tables.Count = 0 Then
        MsgBox "This document contains no tables", _
            vbExclamation, "Import Word Table"
    Else
        For TableNo = 1 To wdDoc.tables.Count
            With .tables(TableNo)
                Sheets.Add after:=Sheets(Worksheets.Count)
'copy cell contents from Word table cells to Excel cells
                For iRow = 1 To .Rows.Count
                    For iCol = 1 To .Columns.Count
                        ActiveSheet.Cells(iRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
                    Next iCol
                Next iRow
            End With
        Next TableNo
    End If
End With
Set wdDoc = Nothing
End Sub

 

[theegg1984]

hey

i tried that but i have encountered a different problem... i get this message coming up

<a href="http://s72.photobucket.com/albums/i174/patburstsintoflames/?action=view&current=image2forforum.jpg" target="_blank"><img src="http://i72.photobucket.com/albums/i174/patburstsintoflames/image2forforum.jpg" border="0" alt="Photobucket"></a>

thanks a lot for your help!!

 

[VoG]

Quick fix but you need to check that it has actually imported everything

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
    If wdDoc.tables.Count = 0 Then
        MsgBox "This document contains no tables", _
            vbExclamation, "Import Word Table"
    Else
        For TableNo = 1 To wdDoc.tables.Count
            With .tables(TableNo)
                Sheets.Add after:=Sheets(Worksheets.Count)
'copy cell contents from Word table cells to Excel cells
                For iRow = 1 To .Rows.Count
                    For iCol = 1 To .Columns.Count
                        On Error Resume Next
                        ActiveSheet.Cells(iRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
                        On Error GoTo 0
                    Next iCol
                Next iRow
            End With
        Next TableNo
    End If
End With
Set wdDoc = Nothing
End Sub

 

[theegg1984]

amazing - it all transfers into the spreadsheet into different tabs according to the page they were on in the word file!

what would be great would be if i could get them to import out of the word document into excel in one tab. is that easy to do?!

thanks again!

 

[VoG]

Try this

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 Word
Dim jRow 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
    If wdDoc.tables.Count = 0 Then
        MsgBox "This document contains no tables", _
            vbExclamation, "Import Word Table"
    Else
        jRow = 0
        Sheets.Add after:=Sheets(Worksheets.Count)
        For TableNo = 1 To wdDoc.tables.Count
            With .tables(TableNo)
'copy cell contents from Word table cells to Excel cells
                For iRow = 1 To .Rows.Count
                    jRow = jRow + 1
                    For iCol = 1 To .Columns.Count
                        On Error Resume Next
                        ActiveSheet.Cells(jRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
                        On Error GoTo 0
                    Next iCol
                Next iRow
            End With
            jRow = jRow + 1
        Next TableNo
    End If
End With
Set wdDoc = Nothing
End Sub

 

[theegg1984]

brilliant! thats ideal, thanks a lot for your help! its much appreciated!

 

[theegg1984]

Is there anyway of doing the same action but for an excel file, i want to run another macro that will import data from another spreadsheet?

Thanks!!!