Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-06-2017, 02:56 PM
Hefty Hefty is offline Importing number data from an Excel spreadsheet into a Word table Windows 8 Importing number data from an Excel spreadsheet into a Word table Office 2016
Novice
Importing number data from an Excel spreadsheet into a Word table
 
Join Date: Jun 2017
Posts: 1
Hefty is on a distinguished road
Default Importing number data from an Excel spreadsheet into a Word table

I've been searching for quite some time to find a good solution to this problem since I'm not much of an advanced user. I have an excel spreadsheet containing numbers that are to be printed out in sheets. They are laid out in 2 sections in the word document that they printed from. I find this hard to explain so I've included screen shots.

This is what an example card should look like


And here is the section of the spreadsheet that the numbers have come from




Each column in the spreadsheet has a label.
Is there are a way I can import the data from the spreadsheet into a sort of word template in order to avoid having to type each card out manually?
Reply With Quote
  #2  
Old 06-07-2017, 04:08 AM
gmayor's Avatar
gmayor gmayor is offline Importing number data from an Excel spreadsheet into a Word table Windows 10 Importing number data from an Excel spreadsheet into a Word table Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

These look like Bingo Cards?

I guess there are several ways to do this. One is to create an Excel macro (code below) which works in conjunction with a template that matches your table (attached). Edit the macro to show the path where you store this template. The macro runs through each row in the worksheet (starting at Row 1) and adds the values from columns C to R to the appropriate cells of the document, then adds the formatted table to the end of a second document based on the first. You should get two tables on each page of the document. If you want to change the size/format of the table/cells then do it in the attached document.

Code:
Option Explicit

Sub CreateCardDoc()
'Run this macro from Excel
'Graham Mayor - http://www.gmayor.com - Last updated - 07 Jun 2017 
Dim i As Integer, j As Integer
Dim k As Integer, m As Integer
Dim LastRow As Long
Dim xlSheet As Worksheet
Dim wdApp As Object
Dim wdDoc As Object
Dim wdCardDoc As Object
Dim oTable As Object
Const strPath As String = "C:\Path\Forums\"    'The path where the template is stored
Dim oRng As Object, oNewRng As Object
    Set xlSheet = ActiveSheet
    On Error Resume Next
    Set wdApp = GetObject(, "Word.Application")
    If Err Then
        Set wdApp = CreateObject("Word.Application")
    End If
    On Error GoTo 0

    Set wdCardDoc = wdApp.Documents.Open(FileName:=strPath & "CardTable.docx", AddToRecentFiles:=False)
    Set wdDoc = wdApp.Documents.Add(Template:=strPath & "CardTable.docx")
    wdDoc.Range.Text = ""

    Set oTable = wdCardDoc.Tables(1)
    With xlSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        For i = 1 To LastRow    'If header row make that i = 2
            m = 3    'assumes the numbers start in column 'C'
            For k = 1 To 4
                For j = 1 To 7 Step 2
                    Set oRng = oTable.Cell(k, j).Range
                    oRng.End = oRng.End - 1
                    oRng.Text = .Cells(i, m)
                    m = m + 1
                Next j
            Next k
            Set oNewRng = wdDoc.Range
            oNewRng.collapse 0
            oNewRng.formattedtext = oTable.Range.formattedtext
            DoEvents
        Next i
    End With
    wdCardDoc.Close 0
End Sub
Attached Files
File Type: docx CardTable.docx (22.9 KB, 8 views)
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel spreadsheet as a data source stored within a word document slaycock Word VBA 0 03-19-2017 11:36 AM
Importing number data from an Excel spreadsheet into a Word table Exporting specific data fields from MS Word 2013 to a MS Excel 2013 spreadsheet Labyrinth Word 7 07-19-2016 01:35 PM
Importing number data from an Excel spreadsheet into a Word table Importing Data from Excel - Format of table messed up in Word Table epsulliv Word Tables 6 08-24-2015 07:29 AM
I have a spreadsheet importing live data SteveZ Excel 1 11-06-2013 09:42 PM
Importing number data from an Excel spreadsheet into a Word table Importing data from excel using a macro soma104 Word 1 04-14-2011 05:10 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:07 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft