• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating Table in Word from Excel data

J

Jeffrey Lebowski

Member
Hi,

I am using a trigger event to create a word document which pulls information from the target.row that initiated the trigger event. I can get most of this done, but it gets tricky when I want to put some of the variables I define into a Word table. To clarify, I want to create a new table in word that will be filled with my variables from excel. I have been striking out with this. I think its a pretty specific request because I cant seem to find help in any forums. Any help would really be appreciated.
Thanks.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
' unhide notice sheet if hidden
If Sheets("Notice").Visible = False Then Sheets("notice").Visible = True
Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim wd As Object
Dim Data As Range, Message As String

Dim thisRow As Long
Dim wsNotice As Worksheet

Dim EmpName As String, Ttype As String, ExpDate As String
Dim Vname As String, ExpDesc As String, CtAtt As String
Dim MealCost As String, AppName As String

' constants
Const ksRange = "Data_List"
Const ksTrigger = "Warning"
' declarations
Dim rng As Range
Set wsNotice = Sheets("Notice")
' start
Set rng = Range(ksRange)
thisRow = Target.Row
If Application.Intersect(Target, rng) Is Nothing Then GoTo Worksheet_Change_Exit
If Target.Cells.Count > 1 Then GoTo Worksheet_Change_Exit
' process
With Target
If .Value = ksTrigger Then
' these are the variable which will be populated into the word doc
EmpName = Cells(thisRow, "E").Value
Ttype = Cells(thisRow, "I").Value
ExpDate = Cells(thisRow, "C").Value
Vname = Cells(thisRow, "M").Value
ExpDesc = Cells(thisRow, "J").Value
CtAtt = Cells(thisRow, "U").Value
MealCost = Cells(thisRow, "P").Value
AppName = Cells(thisRow, "G").Value
End If
End With

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

Set WordDoc = WordApp.Documents.Add

With WordDoc
.Font.Size = 12
' .Font.Bold = True
.ParagraphFormat.Alignment = 0
.TypeText Text:="Date:" & vbTab & vbTab
.TypeParagraph
.TypeText Text:="To:" & vbTab & vbTab & EmpName
.TypeParagraph
.TypeText Text:="From:" & vbTab & vbTab & "Bob Costas, CEO"
.TypeParagraph
.TypeText Text:="Cc:" & vbTab & vbTab & AppName
.TypeParagraph
.TypeParagraph
.ParagraphFormat.Alignment = 1
.Font.Bold = True
.TypeText Text:="Warning"
.Font.Bold = False
.TypeParagraph
.ParagraphFormat.Alignment = 0
.TypeText Text:="One whole paragraph of text here"
.TypeParagraph
End With

''''''''i want to add a table here

With WordDoc
.TypeParagraph
.TypeText Text:="two more paragraphs of text here"
.TypeParagraph
.TypeParagraph
.TypeText Text:="Thank you."



'
End With
End With

' end
Worksheet_Change_Exit:
Set rng = Nothing
If Sheets("Notice").Visible = True Then Sheets("Notice").Visible = False

Application.ScreenUpdating = True

End Sub

`
 
Hi, Jeffrey Lebowski!
You yet know the routine... upload a sample workbook and a sample document file, manually filled with coherent data.
Regards!
 
Sorry, I was too lazy.

Here is a dummy file that should sufficiently explain what I am doing and what remains incomplete.
 

Attachments

  • ExportTest (1).xlsm
    67.1 KB · Views: 2
Hi, Jeffrey Lebowski!
I'll give a look at it tomorrow, but haven't we done something with the same structure and conditions to create .pdf instead of .docx? I recognize my code.
Regards!
 
Correct. It was created initially to export to PDF but I now think it will be more effective to have it export to Word so it is in a format that can be easily added to/edited.

Thanks for your help Sir!
 
I have been able to incorporate a table within the word document that is created, but I am struggling with the formatting of the table. I tried to set the row height to 0.15 and the text to be vertically aligned in the middle of the table cells, but neither of these two arguments seem to work.

Please help!
 

Attachments

  • ExportTest (1).xlsm
    25.1 KB · Views: 3
Hi, Jeffrey Lebowski!

Give a look at the uploaded file. It's a Word document to show how a table is created and filled. This is the code:
Code: 
Option Explicit

Sub DoTheJobAtWord()
  '
  ' constants
  Const kiRows = 2
  Const kiColumns = 6
  Const ksTitles = "@Name@Sales@Region@Supervisor@Year Sales To Date@DOB"
  Const ksSeparator = "@"
  '
  ' declarations
  '  objects
  Dim wd As Document
  Dim tbl As Table
  '  array
  Dim vTitle As Variant
  Dim I As Integer, J As Integer
  '
  ' start
  '  array
  vTitle = Split(ksTitles, ksSeparator)
  '
  ' process
  '  objects
  Set wd = ActiveDocument
  With wd
  .Content.Delete
  Set tbl = .Tables.Add(.Range(0, 0), kiRows, kiColumns, wdWord9TableBehavior, wdAutoFitFixed)
  End With
  With tbl
  ' titles
  I = 1
  For J = 1 To kiColumns
  .Cell(I, J).Range.InsertAfter vTitle(J)
  Next J
  ' data
  I = 2
  For J = 1 To kiColumns
  .Cell(I, J).Range.InsertAfter String(J, CStr(J))
  Next J
  ' format
  .Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
  End With
  '
  ' end
  '  objects
  Set tbl = Nothing
  Set wd = Nothing
  '  beep
  Beep
  '
End Sub

Now it should be adapted to work automatized from Excel. Maybe tomorrow if not today, I guess.

Regards!
 

Attachments

  • Creating Table in Word from Excel data (for Jeffrey Lebowski at chandoo.org).zip
    20.6 KB · Views: 5
Last edited:
Hi, Jeffrey Lebowski!

Please clarify this:
a) You want to create a unique Word document with all the entries related to the trigger ("WARNING!!") or you want to create a single Word document for each entry with the trigger? In both cases which should be the filenames? (yes, I realize that the code is placed in the worksheet change event section).
b) What happens if you modify column A to be triggered, then again to any other value, then again to be triggered and so? Documents, how many and what names?... despite of cutting half of each finger of that user, of course.
c) The data within the detail row/rows of the Word table, should be crude as the Excel cell content or edited as displayed?

Regards!
 
a) one Word document for each trigger. Filename= Notice1, Notice2...etc
b) each time a WARNING!! triggers in column A, a Word document is created. If it is possible, I would prefer that multiple documents be created side-by-side....so if I selected WARNING!! in rows 2, 6&7 of column A, Notice1, Notice2 and Notice 3 would be created alongside each other.
c) I'm not sure I understand your question, but the only formatting I care about for the table is fontsize=9 and fontstyle is consistent with the other document text.

Thanks for your help
 
Hi, Jeffrey Lebowski!
Would you elaborate on b)? What if selected 2, 6, 7, then deselected 6, 7, then selected again 6, 7. About selecting, remember that copy & paste doesn't trigger the worksheet change event.
My c) was referring to 14617 or $14,617.00.
Regards!
 
Happy New Year!

b) Each time WARNING is selected, open a new word file (with Notice1, Notice2, etc. as filename). If a selection is changed from WARNING to something else, nothing should happen. If WARNING is selected, deselected, then selected again, two word documents should be created for this same selection (with different names).
This file will only be used by myself and one other person. Therefore, I am not very concerned with it being misused in this regard.

c) I would prefer that the formatting mirror how the content is formatted in the excel file. ($14,617.00 for column E and 1/1/1985 for column F)
 
You must log in or register to reply here.
Back
Top