+ Reply to Thread
Results 1 to 1 of 1

Loop through excel data to create separate tables in one word document

  1. #1
    Registered User
    Join Date
    06-15-2012
    Location
    Groningen, the Netherlands
    MS-Off Ver
    Excel 2007
    Posts
    1

    Loop through excel data to create separate tables in one word document

    Hi,

    BACKGROUND:
    I'm managing a word document with tables with specifications and descriptions of csv files, generated by an application. These files are used by clients as input data.
    The number of files - and so their descriptions has increased uptill about 170 files so its hard to keep the data consistent, since Word is not really a tool to compare tablecells and so.
    Now I have imported al the data into Excel and I'm trying to generate the Word document needed by our clients (which needs to have an introduction, table of contents and all the neccesssary blabla etc. Its no problem to copy paste that stuff in later)

    WHAT I'M TRYING TO DO:
    I'm trying to find a solution to loop through the excel sheet and copy the data from one single file description as a table into Word, and then continues with the next file description and copy it to a new page to Word. It would be nice if columnwidth could be fixed per column to get a consistent look through all the pages. Also giving the headers a background color & bold font is a 'nice to have'

    ABOUT THE DATA:
    The data in Exel sheet "Data" are 'blocks' with 6 rows with general information about the csv file, followed by a grid of 8 columns to explain each field in the csv file. This grid has a header row and 4 - uptill 165 data rows.

    The excel sheet looks like this: (with test data)

    Indicator 1
    Filename 1aaa.txt
    Description 1bbb
    Selection 1ccc
    Order 1ddd
    Remark 1eee
    SEQNR FIELDNAME TYPE SIZE UNIT DESCRIPTION EXAMPLES REMARK
    1A1 1A2 1A3 1A4 1A5 1A5 1A7 1A8
    1B1 1B2 1B3 1B4 1B5 1B5 1B7 1B8
    1C1 1C2 1C3 1C4 1C5 1C5 1C7 1C8
    Indicator 2
    Filename 2aaa.txt
    Description 2bbb
    Selection 2ccc
    Order 2ddd
    Remark 2eee
    SEQNR FIELDNAME TYPE SIZE UNIT DESCRIPTION EXAMPLES REMARK
    2A1 2A2 2A3 2A4 2A5 2A5 2A7 2A8
    2B1 2B2 2B3 2B4 2B5 2B5 2B7 2B8
    2C1 2C2 2C3 2C4 2C5 2C5 2C7 2C8
    2D1 2D2 2D3 2D4 2D5 2D5 2D7 2D8
    2F1 2F2 2F3 2F4 2F5 2F5 2F7 2F8
    2F1 2G2 2G3 2G4 2G5 2G5 2G7 2G8

    A 'block' (= one single filedescription) starts in fact with the word INDICATOR and ends before the next empty line and it is 8 columns width.
    There are many of these blocks, in this example are 3 and 6 datarows, but they could be up to 165 data rows

    MACRO TILL NOW
    I'm quite unexperienced with vba script so what I've got is basically found on forums and modified a bit:

    Sub exportword()
    Worksheets("Data").Select

    Application.CutCopyMode = xlCut
    Application.DisplayAlerts = False
    On Error Resume Next

    Dim APPWD As Object

    Dim i As Integer

    Set APPWD = CreateObject("Word.Application")
    APPWD.Visible = True

    ' Tell Word to create a new document
    APPWD.Documents.Add

    ' Doc Setup
    With APPWD.ActiveDocument.PageSetup
    .LineNumbering.Active = False
    .Orientation = 1
    '.TopMargin = CentimetersToPoints(2.54)
    '.BottomMargin = CentimetersToPoints(2.54)
    '.LeftMargin = CentimetersToPoints(2)
    '.RightMargin = CentimetersToPoints(2)
    '.Gutter = CentimetersToPoints(0)
    '.HeaderDistance = CentimetersToPoints(1.25)
    '.FooterDistance = CentimetersToPoints(1.25)
    '.PageWidth = Application.InchesToPoints(21)
    '.PageHeight = Application.InchesToPoints(29.7)
    .LeftMargin = Application.InchesToPoints(1)
    .RightMargin = Application.InchesToPoints(1)
    .TopMargin = Application.InchesToPoints(1)
    .BottomMargin = Application.InchesToPoints(1)
    .HeaderMargin = Application.InchesToPoints(1.25)
    .FooterMargin = Application.InchesToPoints(1.25)
    End With

    Sheets("Data").Select
    ' Find the last row with data in the database
    FinalRow = Range("A9999").End(xlUp).Row

    For i = 2 To FinalRow
    Sheets("Data").Select
    Range(Cells(1, 1), Cells(FinalRow, 8)).Copy
    Next

    ' Tell Word to paste the contents of the clipboard into the new document
    APPWD.Selection.Paste

    Application.CutCopyMode = xlCut

    Application.DisplayAlerts = True

    End Sub


    MY REQUEST:
    I'm trial-and-error'd this for quit a lot of time now but it looks like I'm not capable to create a loop in an Excel macro which copies one filedescription to Word, insert a pagebreak and than copy the next description to Word, untill all descriptions are available in Word, in separate, the same looking tables.
    I would really appreciate some help!

    Thanks in advance
    '/'/
    Last edited by walhallamar; 06-26-2012 at 10:02 AM. Reason: changed description of table into in example table

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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