Export a specific page with tables from word template to excel spreadsheet

Hi
I need help to write a macro to export the last page of my template to an excel worksheet and the document in a normal word document when user hit the command button.
The function can just open the spreadsheet with the converted info, no need to save the spreadsheet for the user, same as the word document.

I manage to write the macro that can allow user to open the save the doc, but have no idea on how to convert the last page to excel. Any assistance is appreciated. Thanks.

Quote:

---

I need help to write a macro to export the last page of my template to an excel worksheet

---

Have you considered saving the "last page of your template" as an Excel template in its own right and then join the two workbooks?

Quote:

---

and the document in a normal word document when user hit the command button.

---

Which program (Word or Excel) are you in when the command button is activated?

Quote:

---

I manage to write the macro that can allow user to open the save the doc, but have no idea on how to convert the last page to excel.

---

What exactly is the template you have constructed? I'm confident that the "allow user to open the save the doc" is a typo but can you reconstruct this bit as well please?

Hi
Sorry for not explaining clearly. I hv attached the template for easy reference. This file is meant to be on posted on the website. I wanted the user to be able to generate a copy of the form with the last page of the form as an excel spreadsheet in a separate file so that user can complete the spreadsheet when they have all the necessary information. The last page can be hidden when user complete the form. Thanks.

I tried writing a macro to print the last 2 page of the document of a template, but it cannot work. Any help is appreciated. Thanks.

Code:

---

Sub PrintAnnex()

Dim otherpage As Integer

With Application.FileDialog(msoFileDialogSaveAs)
.FilterIndex = 3
.Show

otherpage = ((ActiveDocument.ActiveWindow.Panes(1).Pages.Count) - 2)
ActiveDocument.SaveAs2 .otherpage(1), wdFormatDocument
End If
End With

End Sub

---

I mostly code with Excel but i have put this togther to pull the last two tables out into Excel from Word, I tried to make it work with your button.

You can copy the whole table out in one go but you end up with strange formatting on the table when it lands in Excel, this method allows you to format the cells as you wish when the table is being created in Excel.

Hope this helps

Code:

---

Private Sub CommandButton1_Click()   
    Dim objDoc
    Dim iTotalCols As Long
    Dim iTotalRows As Long
    Dim iRows, iCols As Long
    Dim txt As Variant
    Dim oXL As Object
    Dim xlWB As Workbook
    Dim xlWS As Worksheet
    Dim nRows As Long
   
    Application.ScreenUpdating = False
   
    Set oXL = CreateObject("Excel.Application")
    oXL.Visible = True
    Set xlWB = oXL.Workbooks.Add
    Set xlWS = xlWB.Sheets(1)
    Set objDoc = ThisDocument


    iTotalCols = objDoc.Tables(2).Columns.Count
    iTotalRows = objDoc.Tables(2).Rows.Count
    ' get table 2 headers
    For iCols = 1 To objDoc.Tables(2).Columns.Count
        txt = objDoc.Tables(2).Cell(1, iCols).Range.Text
        With xlWS
            .Cells(1, iCols) = Replace(txt, "", "")
            .Cells(1, iCols).Font.Bold = True
        End With
    Next iCols
    ' get table 2 data
    For iRows = 2 To iTotalRows
        For iCols = 1 To iTotalCols
            With xlWS
                txt = objDoc.Tables(2).Cell(iRows, iCols).Range.Text
                .Cells(iRows, iCols) = Replace(txt, "", "")
                .Cells(iRows, iCols).Borders.LineStyle = xlContinuous
            End With
        Next iCols
    Next iRows
    ' get table 3 headers
    iTotalCols = objDoc.Tables(3).Columns.Count
    iTotalRows = objDoc.Tables(3).Rows.Count
    nRows = iRows + 2
    For iCols = 1 To objDoc.Tables(3).Columns.Count
        txt = objDoc.Tables(3).Cell(1, iCols).Range.Text
        With xlWS
            .Cells(nRows + 1, iCols) = Replace(txt, "", "")
            .Cells(nRows + 1, iCols).Font.Bold = True
        End With
    Next iCols
    ' get table 3 data
    For iRows = 2 To iTotalRows
        For iCols = 1 To iTotalCols
            With xlWS
                txt = objDoc.Tables(3).Cell(iRows, iCols).Range.Text
                .Cells(iRows + nRows, iCols) = Replace(txt, "", "")
                .Cells(iRows + nRows, iCols).Borders.LineStyle = xlContinuous
            End With
        Next iCols
    Next iRows
    xlWS.Cells.EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

---