Create a word report with excel data from inside Excel (Without opening word)

[Asiatico]

Excel Data:-

Data A	Data B	Data C
Xya	Dsa	Hta
Hya	Jsa	Rtf

<tbody>
</tbody>

Word Output:-

Data A	Xya
Data B	Dsa
Data C	Hta

<tbody>
</tbody>

Data A	Hya
Data B	Jsa
Data C	Rtf

<tbody>
</tbody>

I have illustrated my question with an example. My source data is the excel table (the first table) that has data in a specific format with three columns and three rows. The first row is the heading row for the data in the corresponding columns. For the data in each row, I want to create separate tables in word document as illustrated in the "Word Output". I want to create the word output without any need to open the word application manually. Also the headings in the word document would get a specific font/ color as indicated by the red color in this example (If possible, the cells in the word output would get background fill for the headings "Data A", "Data B", "Data C"). Can I get some idea ?

 

[AOB]

If you want to create a Word document from within Excel, you're going to have to open Word at some point.

That said, it is possible to hide the Word application, perform your task, save it / e-mail it / whatever it is you need to do with it, and then close the (hidden) Word application so that it appears that Word never opens. Would that suffice?

 

[Asiatico]

@AOB I mean, the user should not be prompted to open the word application himself. But there can be a single trigger within the excel, upon pressing which would the embedded macro in the excel sheet would go and do its task of generating the word report in the particular format. It is okay if the word opens on its own for processing this, but I only mean to say that the onus of opening the word application should not be left to the user but the macro trigger which would do this by itself.

 

[AOB]

This should get you started...

Option Explicit
Sub Asiatico()
    
    ' Excel variables
    
    Dim wbk As Workbook
    
    Dim shtOriginal As Worksheet
    Dim shtTransposed As Worksheet
    
    Dim rngOriginal As Range
    Dim rngTransposed As Range
    Dim rngToCopy As Range
    
    Dim i As Long
    
    ' Word variables
    
    Dim wrdApp As Object
    Dim wrdDoc As Object
    
    ' Turn off screen updates and alerts
    
    With Application
    
        .ScreenUpdating = False
        .DisplayAlerts = False
    
    End With
    
    ' Initialise Excel variables
    
    Set wbk = ThisWorkbook
    Set shtOriginal = wbk.Sheets(1)
    Set rngOriginal = shtOriginal.UsedRange
    
    ' Check for previous 'Transposed' sheets (remove if present)
    
    For Each shtTransposed In wbk.Sheets
    
        If shtTransposed.Name = "Transposed" Then
        
            shtTransposed.Delete
            Exit For
            
        End If
    
    Next shtTransposed
    
    ' Create temporary 'Transposed' sheet
    
    Set shtTransposed = wbk.Sheets.Add
    
    With shtTransposed
    
        .Name = "Transposed"
        .Move After:=shtOriginal
        
        Set rngTransposed = .Range("A1")
    
    End With
    
    ' Make a transposed copy of the original data in the 'Transposed' sheet
    
    rngOriginal.Copy
    rngTransposed.PasteSpecial xlPasteValues, , , True
    
    Set rngTransposed = shtTransposed.UsedRange
    
    ' Initialise Word
    
    Set wrdApp = CreateObject("Word.Application")
    Set wrdDoc = wrdApp.Documents.Add
    
    ' Loop through transposed data and create one table for each column (excluding leftmost column, which will be common to all)
    
    For i = 1 To rngTransposed.Columns.Count - 1
    
        Set rngToCopy = rngTransposed.Offset(rngTransposed.Rows.Count + 5).Resize(1, 1)
    
        Union(rngTransposed.Resize(, 1), rngTransposed.Offset(, i).Resize(, 1)).Copy rngToCopy
    
        With rngToCopy.CurrentRegion
        
            ' Add borders
        
            .Borders(xlInsideHorizontal).Color = RGB(192, 192, 192)
            .Borders(xlInsideHorizontal).Weight = xlThin
        
            .Borders(xlInsideVertical).Color = RGB(192, 192, 192)
            .Borders(xlInsideVertical).Weight = xlThin
    
            .Borders(xlEdgeTop).Color = RGB(0, 0, 0)
            .Borders(xlEdgeTop).Weight = xlThick
        
            .Borders(xlEdgeBottom).Color = RGB(0, 0, 0)
            .Borders(xlEdgeBottom).Weight = xlThick
        
            .Borders(xlEdgeLeft).Color = RGB(0, 0, 0)
            .Borders(xlEdgeLeft).Weight = xlThick
        
            .Borders(xlEdgeRight).Color = RGB(0, 0, 0)
            .Borders(xlEdgeRight).Weight = xlThick
    
            ' Change the font colour of the first column to red
        
            .Resize(, 1).Font.Color = RGB(255, 0, 0)
        
            ' Other formatting as necessary...
        
            ' Copy to clipboard
            
            .Copy
        
        End With
        
        wrdDoc.Select
        
        With wrdApp.Selection
        
            .InsertBreak 3      ' wdSectionBreakContinuous
            .TypeParagraph
            .TypeParagraph
            .PasteExcelTable False, False, False
            
        End With
    
        rngToCopy.Delete
        
    Next i
    
    ' Delete the temporary 'Transposed' sheet
    
    shtTransposed.Delete
    
    ' Show the finished Word document
    
    wrdApp.Visible = True
    
    ' Unload the objects
    
    Set wrdDoc = Nothing
    Set wrdApp = Nothing
    
    ' Turn screen updates and alerts back on again
    
    With Application
    
        .ScreenUpdating = True
        .DisplayAlerts = True
    
    End With
    
End Sub

 

[Asiatico]

This code works like a wonder! I am trying to understand this code for the past few days. I am very new to VBA programming. Thanks for this help. Do you suggest any way I can use to understand VBA language ? I am going through Lynda.com tutorials.

 

[AOB]

There are plenty of good references on the internet and some decent books as well. This is a nice site to get you started from the beginning, although Lynda.com is also helpful.

To be honest, the way I learned was by experience. Start with a small, basic problem and try to write the code to solve it. Keep it simple at first, introduce yourself to basic concepts and apply them until you understand them. When you have those 'mastered', move on to more complex aspects like objects, properties and methods. Build up your knowledge base (it will start slow and advance rapidly as you become more and more comfortable with the language) Make use of intellisense when you get to the object stage.

I cannot emphasise enough the need to understand and appreciate the power of debugging. It will make or break any project. If you can't debug, you won't be able to build anything and you won't be able to learn anything. Chip Pearson has some good starter tips here

The code I provided may seem a little confusing if you are new to VBA. I would advise you debug it, and set watches to all the relevant variables to see how their values are set or updated. Step through it line by line, see what it 'does' in each step and try to understand how and why. This may seem slightly advanced if you are coming at this having never written anything in VBA before (in the grand scheme of things, it's not...)