Run macro and go to next cell

[treeleaf20]

All,
I have the following code, this works great as it pulls in all the information from some of my word documents and cycles through all the tables I have in my word document. The problem is it stays in the original cell and it keeps overwriting the previous data. If the table has 2 columns and nine rows. I'd like each row to populate and then when it's done move to row 10. Here is the code. Any suggestions would be very helpful. Thanks in advance:

Sub ImportWordTable1()
'Import one table to current sheet
Dim wdDoc As Object
Dim WS As Worksheet
Dim wdFileName As Variant
Dim TableNo As Integer 'table number in Word
Dim currentTable As Integer
Dim d As Integer
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
    currentTable = 1
    For d = 0 To TableNo
    With .Tables(currentTable)
'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
                Cells(iRow, iCol) = WorksheetFunction.Clean(.Cell(iRow, iCol).Range.Text)
                'Sheet2.Range("a" & .Rows.Count).End(xlUp).Offset(1).Resize(, 1) = _
Array(wdDoc.Tables(currentTable).Cell(1, 2))
                On Error GoTo 0
            Next iCol
        Next iRow
        'Set WS = Sheets.Add
    End With
    currentTable = currentTable + 1
    Next d
End With
Set wdDoc = Nothing
End Sub

 

[VoG]

Hello and welcome to MrExcel.

I recognise that code

Try this instead:

Sub ImportWordTable3()
'Import all tables to a single sheet
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

 

[treeleaf20]

That works great, I really appreciate the quick response. Haha I'm glad you reconginze the code since I was searching on here first. One more question, I have a directory of files. Is there anyway to alter this code to open up the next file in the directory and continue to add it the spreadsheet? Thanks again in advance for your help!

 

[VoG]

One more question, I have a directory of files. Is there anyway to alter this code to open up the next file in the directory and continue to add it the spreadsheet?

I don't think that is something I can help with, sorry.

 

[treeleaf20]

OK well I appreciate the help. For the new sheet that gets added, is there a way to make this worksheet name the word file that got opened?

Thanks in advance.

 

[VoG]

Try this

Sub ImportWordTable3()
'Import all tables to a single sheet
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
        Sheets.Add after:=Sheets(Worksheets.Count)
        On Error Resume Next
        ActiveSheet.Name = FName(wdFileName)
        On Error GoTo 0
        jRow = 0
        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

Function FName(FullPath As Variant) As String
Dim i As Integer
For i = Len(FullPath) To 1 Step -1
    If Mid(FullPath, i, 1) = "\" Then Exit For
Next i
FName = Right(FullPath, Len(FullPath) - i)
End Function

 

[treeleaf20]

Ok, I'd like to make a change to the macro. For the first two rows in my table only, I'd like the macro to do the following:

1) For row 1, I'd like the contents of column 2 in the table to be displayed in cell A1 (for example) of the spreadsheet
2) For row 2, I'd like the contents of column 2 in the table to be displayed in cell B1 (for example) of the spreadsheet

The rest of the macro can continue to function as is. Thanks again for the help!

 

[treeleaf20]

Any ideas? Sorry I'm stuck.