Word vba copy excel data into word tables

[FaizanRoshan88]

Hi,
I have vba code for word doc for copy data form excel sheet to word tables with different criteria, but it's not work correctly. I try to use 3 criteria to filter excel column"B" data, if Criteria 1 match copy col B and H cell data and paste in word doc table 1 and so on for other 2 criteria.
Criteria 1 = 07:00 to 15:00
Criteria 2 = 15:00 to 23:00
Criteria 3 = 23:00 to 07:00
I have upload file if any one want to check it.
Please help me to make correct this code

Sub InputExcel()

Set appExcel = CreateObject("Excel.Application")

Dim INP_File As Variant
Dim lngRows As Long
Dim LenMgs As Long
Dim NumberOfRows As String
Dim lnCountItems As Long

    INP_File = appExcel.GetOpenFilename("Excel files (*.xlsx;*.xls),*.xlsx;*.xls", 2)

    appExcel.Workbooks.Open INP_File

    If INP_File > 0 Then
        LenMgs = appExcel.Worksheets("Sheet1").Range("B2").CurrentRegion.Rows.Count
        'NumberOfRows = appExcel.Worksheets("Sheet1").Range("B42").End(xlUp).Row
    pasteRowIndex = 1
    lnCountItems = 1

    For r = 1 To LenMgs 'Loop through sheet1 and search for your criteria

        If Cell(r, Columns("B").Column).Value = "YourCriteria" Then 'Found

            'Copy the current row
            Rows(r).Select
            Selection.Copy

            'Switch to the word where you want to paste it & paste
            For Each wdCell In wdDoc.Tables(3).Columns(1).Cells
            wdCell.Range.Text = Selection(lnCountItems, r)
              lnCountItems = lnCountItems + 1
            Next wdCell

            'Next time you find a match, it will be pasted in a new row
            pasteRowIndex = pasteRowIndex + 1

          'Switch back to your table & continue to search for your criteria
            Sheets("Sheet1").Select
        End If
    Next r
    End If

appExcel.ActiveWorkbook.Close

appExcel.Quit

Set appExcel = Nothing

End Sub

Thank you for advance help.

 

[Marc L]

Hi !

This kind of code seems not to be located in an Excel workbook.
If it's in an Excel workbook, the crash may occur by the computer
ressources waste according to this code : to trash !
Why opening another Excel instance as Excel is yet opened ?‼

And it seems you forgot to initialize some object variable.
So when an error occurs, check message and its help
and variables values within Locals VBE windows …
​