Consulting

Results 1 to 5 of 5

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

  1. #1

    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.

  2. #2
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,239
    Location
    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?
    Last edited by Aussiebear; 05-15-2022 at 02:26 PM. Reason: Missed a question to the OP
    Remember To Do the Following....
    Use tags when posting code to the thread,
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    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.
    Attached Files Attached Files
    Last edited by elaineada75; 05-16-2022 at 12:16 AM.

  4. #4
    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

  5. #5
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    856
    Location
    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
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •