Code for importing word data into next blank row

[rondon]

I have code that allows me to import data from cells in a word table. My code imports the data into the first row of my excel worksheet. As I have to import from many different word.docx, I would like to use the following logic.

If first row contains data then find and insert into the next blank row. Currently I am using

RowOutputNo =1

how do I add if this row = not null then go to next blank row

'nominate which row to begin inserting the data from. In this example we are inserting the data from row 1
RowOutputNo = 1

Thanks Ron

 

[Macropod]

Your single line of code lacks anything that might be construed as a meaningful context.

For some code to get you started, see: Help with VBA to extract data from Word to Excel

 

[rondon]

Your single line of code lacks anything that might be construed as a meaningful context.

For some code to get you started, see: Help with VBA to extract data from Word to Excel

Sorry Paul

here is the code, it imports the data from table 3 of my word.docx templates. I think it is the code in red below that forces the data imput to the same row each time overiding any exsisting data from previous inputs. As stated I need to imput data from many different word docx always using the same template consisting of table 3, the tables are desined so visualy impaired user can complete the tables in word. Each time the code is invoked I would like the data to look for the next blank row in the excel worksheet everytime I import from a new word doc. Hopes this adds some context.

regards Ron

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim WordFilename As Variant
Dim Filter As String
Dim WordDoc As Object
Dim tbNo As Long
Dim RowOutputNo As Long
Dim RowNo As Long
Dim ColNo As Integer
Dim tbBegin As Integer

    Set ws = Worksheets("Analysis")

      Filter = "Word File New (*.docx), *.docx," & _

        "Word File Old (*.doc), *.doc,"

          ws.Cells.ClearContents

WordFilename = Application.GetOpenFilename(Filter, , "Select Word file")

        If WordFilename = False Then Exit Sub

          Set WordDoc = GetObject(WordFilename)

          With WordDoc

         tbNo = WordDoc.Tables.Count

         If tbNo = 0 Then

          MsgBox "This document contains no tables"

End If

[COLOR=rgb(97, 189, 109)]'nominate which row to begin inserting the data from. In this example we are inserting the data from row 1[/COLOR]

[COLOR=rgb(184, 49, 47)]RowOutputNo = 1[/COLOR]

     For tbBegin = 3 To tbNo

     With .Tables(tbBegin)

      For RowNo = 1 To .Rows.Count

           For ColNo = 1 To .Columns.Count

     ws.Cells(RowOutputNo, ColNo) = Application.WorksheetFunction.Clean(.cell(RowNo, ColNo).Range.Text)

     Next ColNo

  [COLOR=rgb(0, 0, 0)]   RowOutputNo = RowOutputNo + 1[/COLOR]

      Next RowNo

End With
     RowOutputNo = RowOutputNo

Next tbBegin
End With
End Sub

 

[rondon]

Sorry Paul

here is the code, it imports the data from table 3 of my word.docx templates. I think it is the code in red below that forces the data imput to the same row each time overiding any exsisting data from previous inputs. As stated I need to imput data from many different word docx always using the same template consisting of table 3, the tables are desined so visualy impaired user can complete the tables in word. Each time the code is invoked I would like the data to look for the next blank row in the excel worksheet everytime I import from a new word doc. Hopes this adds some context.

regards Ron

Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim WordFilename As Variant
Dim Filter As String
Dim WordDoc As Object
Dim tbNo As Long
Dim RowOutputNo As Long
Dim RowNo As Long
Dim ColNo As Integer
Dim tbBegin As Integer

    Set ws = Worksheets("Analysis")

      Filter = "Word File New (*.docx), *.docx," & _

        "Word File Old (*.doc), *.doc,"

          ws.Cells.ClearContents

WordFilename = Application.GetOpenFilename(Filter, , "Select Word file")

        If WordFilename = False Then Exit Sub

          Set WordDoc = GetObject(WordFilename)

          With WordDoc

         tbNo = WordDoc.Tables.Count

         If tbNo = 0 Then

          MsgBox "This document contains no tables"

End If

[COLOR=rgb(97, 189, 109)]'nominate which row to begin inserting the data from. In this example we are inserting the data from row 1[/COLOR]

[COLOR=rgb(184, 49, 47)]RowOutputNo = 1[/COLOR]

     For tbBegin = 3 To tbNo

     With .Tables(tbBegin)

      For RowNo = 1 To .Rows.Count

           For ColNo = 1 To .Columns.Count

     ws.Cells(RowOutputNo, ColNo) = Application.WorksheetFunction.Clean(.cell(RowNo, ColNo).Range.Text)

     Next ColNo

  [COLOR=rgb(0, 0, 0)]   RowOutputNo = RowOutputNo + 1[/COLOR]

      Next RowNo

End With
     RowOutputNo = RowOutputNo

Next tbBegin
End With
End Sub

 

[Macropod]

You need to delete:
ws.Cells.ClearContents
and replace:
RowOutputNo = 1
with:
RowOutputNo = ws.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Amongst other things, this will result in your data starting at row 2 if the worksheet is otherwise empty, allowing a row for column headings.

You may as well also delete:
RowOutputNo = RowOutputNo
as it does nothing useful. Alternatively, if you're trying to insert an empty row between tables, change that row to:
RowOutputNo = RowOutputNo + 1

 

[rondon]

You need to delete:
ws.Cells.ClearContents
and replace:
RowOutputNo = 1
with:
RowOutputNo = ws.UsedRange.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
Amongst other things, this will result in your data starting at row 2 if the worksheet is otherwise empty, allowing a row for column headings.

You may as well also delete:
RowOutputNo = RowOutputNo
as it does nothing useful. Alternatively, if you're trying to insert an empty row between tables, change that row to:
RowOutputNo = RowOutputNo + 1

many thanks Paul

yes this works, it has help me immensely in solving this coding struggle, plus I have learnt more along the way

Thanks Ron