Run macro and go to next cell

treeleaf20

Board Regular
Joined
Mar 17, 2009
Messages
150
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:
Code:
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hello and welcome to MrExcel.

I recognise that code :)

Try this instead:

Code:
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

Board Regular
Joined
Mar 17, 2009
Messages
150
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

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Board Regular
Joined
Mar 17, 2009
Messages
150

ADVERTISEMENT

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

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this

Code:
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

Board Regular
Joined
Mar 17, 2009
Messages
150
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top