Creating multiple word tables from excel data

[wizzard21]

Hello all,

I'm new here but hopefully I can still receive some help on my urgent problem. Right now, I'm working on a national survey with valuable data from students. The plan is to turn around, 100 different reports to the departments with their respective students.

The issue that I have is creating multiple tables from multiple sheets in excel into a word table, of course I can do it myself but I was wondering if anyone knows of a way of automating it.

What the tables look like in excel:

What I want them to look like in word, with a chart if possible:

I realize that I can just copy and paste easy table into word from excel and with a few clicks of a button, get what I want. But i'm looking for something to automate and simplify this process. There are about 300-400 variables that I need to crosstab with certain demographics.

If it matters any, I bought a software package (Q Market Research) but it doesn't make the tables look exactly how I want them to look. The data is from SPSS and for anyone that knows SPSS, those tables are flat out ugly.

Any help would be appreciated.

 

[NdNoviceHlp]

Here's a guess. I have no idea how the page formating will work. Dave

Option Explicit
 
Sub Data2Word()
     
     'Remember: this code requires a referece to the Word object model
     
     'dimension some local variables
    Dim rng As Range 'our source range
    Dim wdApp As New Word.Application 'a new instance of Word
    Dim wdDoc As Word.Document 'our new Word document
    Dim t As Word.Range 'the new table in Word as a range
    Dim myWordFile As String 'path to Word template
    Dim Sht As Worksheet, Cnt As Integer

     
     'initialize the Word template path
     'here, it's set to be in the same directory as our source workbook
    myWordFile = ThisWorkbook.Path & "\DocWithTableStyle.dot"
'open a new word document from the template
Set wdDoc = wdApp.Documents.Add(myWordFile)
 
For Each Sht In ThisWorkbook
 Cnt = Cnt + 1
 'get the range of the contiguous data from Cell A1
    Set rng = Sht.Range("A1").CurrentRegion
     'you can do some pre-formatting with the range here
    rng.HorizontalAlignment = xlCenter 'center align the data
    rng.Copy 'copy the range
    Set t = wdDoc.Content 'set the range in Word
    t.Paste 'paste in the table
    Application.CutCopyMode = False
    With t 'working with the table range
        .Style = "GreenBar" 'set the style created for the table
         'we can use the range object to do some more formatting
         'here, I'm matching the table with using the Excel range's properties
        .Tables(Cnt).Columns.SetWidth (rng.Width / rng.Columns.Count), wdAdjustSameWidth
    End With
Next Sht
'until now the Word app has been a background process
    wdApp.Visible = True
     'we could use the Word app object to finish off
     'you may also want to things like generate a filename and save the file
    wdApp.Activate
     
End Sub

ps. i really hate early binding.

 

[wizzard21]

thanks for the effort dave I found out online how to do it with this basic code

[code] Sub CopyWorksheetsToWord()
' requires a reference to the Word Object library:
' in the VBE select Tools, References and check the Microsoft Word X.X object library
Dim wdApp As Word.Application, wdDoc As Word.Document, ws As Worksheet
    Application.ScreenUpdating = False
    Application.StatusBar = "Creating new document..."
    Set wdApp = New Word.Application
    Set wdDoc = wdApp.Documents.Add
    For Each ws In ActiveWorkbook.Worksheets
        ws.UsedRange.Copy ' or edit to the range you want to copy
        wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
        wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.Paste
        Application.CutCopyMode = False
        wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range.InsertParagraphAfter
        ' insert page break after all worksheets except the last one
        If Not ws.Name = Worksheets(Worksheets.Count).Name Then
            With wdDoc.Paragraphs(wdDoc.Paragraphs.Count).Range
                .InsertParagraphBefore
                .Collapse Direction:=wdCollapseEnd
                .InsertBreak Type:=wdPageBreak
            End With
        End If
    Next ws
    Set ws = Nothing
    ' apply normal view
    With wdApp.ActiveWindow
        If .View.SplitSpecial = wdPaneNone Then
            .ActivePane.View.Type = wdNormalView
        Else
            .View.Type = wdNormalView
        End If
    End With
    Set wdDoc = Nothing
    wdApp.Visible = True
    Set wdApp = Nothing
    Application.StatusBar = False
End Sub

My problem now is that, I can't get what is put into word to format as an actual table. I have to manually go through and style the tables how I want, any clue on how to style tables within the VBA code that i just posted?

 

[NdNoviceHlp]

See my previous link re. autoformat styles. However, I don't think that you are pasting tables to Word. You are pasting a picture of an excel range. If you want to format tables in Word, there has to be tables in Word. You either need to make a template Word doc with formatted tables and insert excel data ...OR... from excel insert tables to Word and format them and add data. Dave

 

[bappakarmakar]

Have u solved it??