+ Reply to Thread
Results 1 to 3 of 3

Struggling to copy tabular data from excel and paste in a word file!!

Hybrid View

0Cool Struggling to copy tabular... 02-07-2021, 03:58 AM
mjr veverka Re: Struggling to copy... 02-07-2021, 11:04 AM
0Cool Re: Struggling to copy... 02-07-2021, 11:19 AM
  1. #1
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    69

    Struggling to copy tabular data from excel and paste in a word file!!

    I have a workbook with 3 worksheets (attached). I'm trying to copy data (A1:D13) from Sheet2 and paste it on desired word document while removing the rows whose column A values are 0.

    I have created a button and added some vba code to do the job. The problem I'm facing is each time I hit the button in Sheet2 the copied data is pasting on the very first page of the word document and not creating a new page with the same header as in Page 1 of the document and also the cells are not auto fitted in the word document.

    Please help !!!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    2,251

    Re: Struggling to copy tabular data from excel and paste in a word file!!

    Try, is it that what you mean ?
    Option Explicit
    
    Sub Button1_Click()
        
        Dim wtq As Long
        Dim cell As Object, exltbl As Object
        Dim wrdApp As Object, wrdDoc As Object
        
        On Error Resume Next
            Set wrdApp = GetObject(Class:="Word.Application")
            
            If wrdApp Is Nothing Then
                Err.Clear
                Set wrdApp = CreateObject(Class:="Word.Application")
                
                If wrdApp Is Nothing Then
                    MsgBox "Microsoft Word could not be found - Aborting"
                    Exit Sub
                End If
            End If
        On Error GoTo 0
        
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        With ThisWorkbook.Sheets("Sheet2")
            Set exltbl = .Range("A1:D13")
            
            For Each cell In .Range("A3:A12").Cells
                If cell.Value = 0 Then cell.EntireRow.Hidden = True
            Next
        End With
        
        With wrdApp
            .Application.ScreenUpdating = False
            
            Set wrdDoc = .Documents.Open(ThisWorkbook.Path & "\" & "vba.docx") '"F:\vba.docx"
            
            ' ".Visible = True" => ? Can it be perhaps/rather set to "False" ?
            'Then "wrdApp.Application.ScreenUpdating = False/True" is unnecessary
            
            .Visible = True
            .Activate
            
            With wrdDoc
                wtq = .Range.Tables.Count
                
                If wtq > 0 Then
                    With .ActiveWindow.Selection
                        .EndKey Unit:=6                 ' 6 = wdStory
                        .TypeParagraph
                        .InsertBreak Type:=7            ' 7 = wdPageBreak
                    End With
                End If
                
                exltbl.SpecialCells(xlCellTypeVisible).Copy
                
                .Paragraphs(.Paragraphs.Count).Range.PasteExcelTable LinkedToExcel:=False, WordFormatting:=False, RTF:=False
                
                Application.CutCopyMode = False
                
                .Tables(wtq + 1).AutoFitBehavior (2)    ' 2 = wdAutoFitWindow
                .Save
                .Close
            End With
            
            Set wrdDoc = Nothing
            
            .Visible = False
            .Application.ScreenUpdating = True
            .Quit
        End With
        
        Set wrdApp = Nothing
        
        With ThisWorkbook.Sheets("Sheet2")
            exltbl.Rows.Hidden = False
        End With
        
        Set exltbl = Nothing
        
        Application.EnableEvents = True
        Application.ScreenUpdating = True
        
    End Sub

  3. #3
    Registered User
    Join Date
    01-11-2019
    Location
    India
    MS-Off Ver
    2010
    Posts
    69

    Re: Struggling to copy tabular data from excel and paste in a word file!!

    Thanks, it is almost what I wanted, the only thing missing here is that the table cells in word document are not auto fitted.

    Also, can you explain your code a little?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Create text file using VBA from Excel Tabular data
    By ganeshinscribe in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-03-2018, 09:09 AM
  2. Copy the tabular data from mail body to an excel file(.xlsm) using vba
    By raushanaj5 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-07-2017, 01:57 AM
  3. Replies: 0
    Last Post: 04-07-2017, 01:57 AM
  4. Replies: 1
    Last Post: 01-16-2013, 05:36 AM
  5. Replies: 1
    Last Post: 10-01-2012, 11:11 PM
  6. 'copy&paste' web tabular data into excel
    By GottaRun in forum Excel General
    Replies: 1
    Last Post: 03-06-2006, 07:00 AM
  7. Replies: 1
    Last Post: 10-17-2005, 04:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1