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

[elaineada75]

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.

[Aussiebear]

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?


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?


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?

[elaineada75]

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.

[elaineada75]

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.



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

[georgiboy]

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


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