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
'/'/
Bookmarks