CODEWITHSUNDEEP
vbaexcelms-word
Simon Yo
Simon Yo

Reputation: 101

Name new sheet after page table was on

I'm using this VBA script to insert all the tables from a word document into separate excel worksheets. Because none of these tables are named, I want to label the sheets by the Page number you find the table on.

The error is that it doesn't know what the variable wdActiveEndPageNumber is. If I change tableNo to PageNo, it also doesn't rename the sheets. It just uses the default Sheet1, Sheet2, Sheet3, etc.

A better name for each sheet would be to use the "Header 3" value at the top of each of the pages the tables are on if that's possible.

Here is my code:

Option Explicit
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
Dim resultRow As Long
Dim tableStart As Integer
Dim tableTot As Integer

Dim sheet_i As Worksheet
Dim PageNo As Integer

On Error Resume Next

ActiveSheet.Range("A:AZ").ClearContents

wdFileName = Application.GetOpenFilename("Word files (*.docx),*.docx", , _
"Browse for file containing table to be imported")

If wdFileName = False Then Exit Sub

Set wdDoc = GetObject(wdFileName) 'open Word file

With wdDoc
    tableNo = wdDoc.Tables.Count
    tableTot = 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 the table to start from", "Import Word Table", "1")
    End If

    For tableStart = tableNo To tableTot

        resultRow = 1
        With .Tables(tableStart)

        PageNo = .Range.Information(wdActiveEndPageNumber)
        Set sheet_i = Sheets.Add(after:=Sheets(Worksheets.Count)).Name = "Page_No_" & CStr(PageNo)

        sheet_i.Activate
            'copy cell contents from Word table cells to Excel cells
            For iRow = 1 To .Rows.Count
                For iCol = 1 To .Columns.Count
                    Cells(resultRow, iCol) = WorksheetFunction.Clean(.cell(iRow, iCol).Range.Text)
                Next iCol
                resultRow = resultRow + 1
            Next iRow
        End With
    Next tableStart
End With


End Sub

Thanks

Upvotes: 0

Views: 63

Answers (1)

Tim Williams
Tim Williams

Reputation: 166885

Set sheet_i = Sheets.Add(after:=Sheets(Worksheets.Count)).Name = "Page_No_" & CStr(PageNo)

The Name property doesn't return the sheet, so you need to do this in two steps:

With ThisWorkbook
    Set sheet_i = .Sheets.Add(after:=.Sheets(.Worksheets.Count))
    sheet_i.Name = "Page_No_" & CStr(PageNo)
End With

Upvotes: 1

Related Questions