473,320 Members | 1,900 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Create table in Word using data from Access table

Hello Everyone!

It's been a long time since I've posted. I'm currently stuck and I have not been able to figure this out.

I have an MS Access table which is linked to a form where users can enter data. I then want them to click a button and transfer that data to specific columns within a table in MS Word. The issue is that I need a table created per record in the MS Access table. So if I have 50 records, I need 50 tables created with "X" columns.

I have the following code to create the table, I just don't know how to incorporate the fields from the table and continue to replicate it.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.     Dim numberOfTables As Integer
  3.     Dim iCount As Integer
  4.     Dim appWord As Word.Application
  5.     Dim wdDoc As Word.Document
  6.  
  7. Set appWord = New Word.Application
  8.  
  9. appWord.Visible = True
  10.  
  11. Set wdDoc = appWord.Documents.Open(FileName:="C:\Test\Test.doc", ReadOnly:=False)
  12.  
  13.  
  14.     numberOfTables = InputBox("How many tables to make?", "Tables")
  15.  
  16.     For iCount = 0 To numberOfTables - 1
  17.  
  18.         ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=7, NumColumns:= _
  19.             2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
  20.             wdAutoFitFixed
  21.         With Selection.Tables(1)
  22.             If .Style <> "Table Grid" Then
  23.                 .Style = "Table Grid"
  24.             End If
  25.             .ApplyStyleHeadingRows = True
  26.             .ApplyStyleLastRow = False
  27.             .ApplyStyleFirstColumn = True
  28.             .ApplyStyleLastColumn = False
  29.             .ApplyStyleRowBands = True 'Office 2010
  30.             '.ApplyStyleColumnBands = False 'Office 2007
  31.         End With
  32.  
  33.         Selection.EndKey Unit:=wdStory
  34.         Selection.TypeParagraph
  35.  
  36.     Next iCount
  37.  
  38. End Sub
Jan 25 '17 #1
5 2378
jforbes
1,107 Expert 1GB
50 Tables is a lot, are you sure you don't mean 50 Rows?

When you are creating your Table, you can stuff the pointer for the new Table into a Variable, then you can refer to the new Table and it's Cells (I haven't tested this code):
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command0_Click()
  2.      Dim numberOfTables As Integer
  3.      Dim iCount As Integer
  4.      Dim appWord As Word.Application
  5.      Dim wdDoc As Word.Document
  6.      Dim oTables(50) As Word.Table
  7.  
  8.  Set appWord = New Word.Application
  9.  
  10.  appWord.Visible = True
  11.  
  12.  Set wdDoc = appWord.Documents.Open(FileName:="C:\Test\Test.doc", ReadOnly:=False)
  13.  
  14.  
  15.      numberOfTables = InputBox("How many tables to make?", "Tables")
  16.  
  17.      For iCount = 0 To numberOfTables - 1
  18.          Set oTables(iCount+1) = ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=7, NumColumns:= _
  19.              2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
  20.              wdAutoFitFixed
  21. ...
  22.  
  23.      Next iCount
  24.  
  25.      oTable(4).Cell(1,1).Range.Text = "This is the Upper Left Cell in Table 4"
  26.      oTable(3).Cell(3,4).Range.Text = "This is the Fourth Cell on the Third Row in Table 3"
  27.  
  28.  End Sub
There are some other ways you can go about this. Personally, I would create loop for the Tables/Rows using OpenRecordset and create/populate the Tables in the loop. This would allow you to use just one Table object instead of an Array of 50.
Jan 26 '17 #2
Thanks for the reply J!

How would I go about creating the loop for the Tables/Rows using OpenRecordset and create/populate the Tables in the loop?
Jan 26 '17 #3
jforbes
1,107 Expert 1GB
Instead of writing the code for you, here is some real world code we currently use. I've stripped out a bunch of other code that adds headers/footers and a few other things, so hopefully it's still all viable. We also use some helper functions like setStatus() and fileExists() that I left in, so please be aware that you wont be able to just copy and paste this code and expect it to work for you. It's here so you can understand how to go about creating a Table and populating it with Data.

Expand|Select|Wrap|Line Numbers
  1. Public Function exportDrawingBOM(ByRef sDrawingName As String, ByRef sJobNumber As String) As Boolean
  2.  
  3.     ' 72 Points = Inch
  4.     '     Y -->
  5.     '   +---
  6.     ' X |
  7.  
  8.     Dim sLocation As String
  9.     Dim sFileName As String
  10.     Dim sBackupFileName As String
  11.     Dim sPrefix As String
  12.  
  13.     Dim oWord As Word.Application
  14.     Dim oDocument As Word.Document
  15.     Dim oSection As Word.Section
  16.     Dim oRange As Word.Range
  17.     Dim oSubRange As Word.Range
  18.     Dim oTable As Word.Table
  19.     Dim oRow As Word.Row
  20.     Dim oCell As Word.Cell
  21.     Dim iRowCount As Integer
  22.     Dim iPageCount As Integer
  23.  
  24.     Dim sSQL As String
  25.     Dim oRst As DAO.Recordset
  26.  
  27.     Dim sItemNumber As String
  28.     Dim sSupplier As String
  29.     Dim sLineFormat As String
  30.  
  31.     Dim nItemNumber As Integer
  32.     Dim nQuantity As Integer
  33.     Dim nSupplier As Integer
  34.     Dim nDescription As Integer
  35.  
  36.  
  37.     ' Row Constants
  38.     nItemNumber = 1
  39.     nQuantity = 2
  40.     nSupplier = 3
  41.     nDescription = 4
  42.  
  43.     If Len(sDrawingName) > 0 And Len(sJobNumber) > 0 Then
  44.         setStatus ("Creating BOM for " & sDrawingName & " and " & sJobNumber & "")
  45.  
  46.  
  47.         ' Determine file locations and make backups
  48.         sLocation = getBOMPath(sDrawingName)
  49.         sFileName = sLocation & sDrawingName & ".doc"
  50.         sBackupFileName = mBOMBackupBackupDir & sDrawingName & ".doc"
  51.         sBackupFileName = appendDateTimeToPath(sBackupFileName)
  52.         If fileExists(sFileName) Then
  53.             If msgBoxAreYouSure("The BOM, '" & sFileName & "' already exits, would you like to make a backup copy first?") Then
  54.                 FileCopy sFileName, sBackupFileName
  55.             End If
  56.             Kill sFileName
  57.         End If
  58.  
  59.  
  60.         ' Create the Word BOM
  61.         ' -------------------
  62.         setStatus ("Creating Word Document")
  63.         Set oDocument = New Word.Document
  64.         Set oSection = oDocument.Sections(1)
  65.  
  66.         ' Page Layout
  67.         oDocument.PageSetup.Orientation = wdOrientLandscape
  68.         oDocument.PageSetup.RightMargin = 36
  69.         oDocument.PageSetup.LeftMargin = 36
  70.         oDocument.PageSetup.TopMargin = 18
  71.         oDocument.PageSetup.BottomMargin = 18
  72.         oDocument.PageSetup.FooterDistance = 0
  73.  
  74.  
  75.         ' Body
  76.         setStatus ("Creating the Main Table")
  77.         Set oRange = oSection.Range
  78.         oRange.Font.Name = "Arial"
  79.         oRange.Font.Size = 12
  80.  
  81.  
  82.         ' Main Table - Create and Format
  83.         Set oTable = oDocument.Tables.Add(oRange, 1, 4)
  84.         iRowCount = iRowCount + 1
  85.         oTable.PreferredWidth = 94
  86.         oTable.Cell(1, nItemNumber).Width = 54
  87.         oTable.Cell(1, nQuantity).Width = 41
  88.         oTable.Cell(1, nSupplier).Width = 85
  89.         oTable.Cell(1, nDescription).Width = 552
  90.         oTable.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
  91.         oTable.Cell(1, 4).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
  92.         oTable.Range.Font.Name = "Arial"
  93.         oTable.Range.Font.Size = 12
  94.         oTable.Cell(1, 1).Borders.OutsideLineStyle = wdLineStyleSingle
  95.         oTable.Cell(1, 2).Borders.OutsideLineStyle = wdLineStyleSingle
  96.         oTable.Cell(1, 3).Borders.OutsideLineStyle = wdLineStyleSingle
  97.         oTable.Cell(1, 4).Borders.OutsideLineStyle = wdLineStyleSingle
  98.  
  99.         ' Main Table -  Line Spacing
  100.         oTable.Cell(1, 1).Range.ParagraphFormat.SpaceBefore = 0
  101.         oTable.Cell(1, 2).Range.ParagraphFormat.SpaceBefore = 0
  102.         oTable.Cell(1, 3).Range.ParagraphFormat.SpaceBefore = 0
  103.         oTable.Cell(1, 4).Range.ParagraphFormat.SpaceBefore = 0
  104.         oTable.Cell(1, 1).Range.ParagraphFormat.SpaceAfter = 0
  105.         oTable.Cell(1, 2).Range.ParagraphFormat.SpaceAfter = 0
  106.         oTable.Cell(1, 3).Range.ParagraphFormat.SpaceAfter = 0
  107.         oTable.Cell(1, 4).Range.ParagraphFormat.SpaceAfter = 0
  108.         oTable.Cell(1, 1).Range.ParagraphFormat.LineSpacing = 12
  109.         oTable.Cell(1, 2).Range.ParagraphFormat.LineSpacing = 12
  110.         oTable.Cell(1, 3).Range.ParagraphFormat.LineSpacing = 12
  111.         oTable.Cell(1, 4).Range.ParagraphFormat.LineSpacing = 12
  112.  
  113.         ' Main Table - Select Contents
  114.         sSQL = ""
  115.         sSQL = sSQL & "SELECT * FROM LineItem "
  116.         sSQL = sSQL & "WHERE DocType='Drawing' "
  117.         sSQL = sSQL & "AND LineType='BOM' "
  118.         sSQL = sSQL & "AND  DocNumber='" & sDrawingName & "' "
  119.         sSQL = sSQL & "ORDER BY PrintIndex "
  120.  
  121.         Set oRst = CurrentDB.OpenRecordset(sSQL, dbOpenDynaset, dbForwardOnly + dbSeeChanges)
  122.         While Not oRst.EOF
  123.             sItemNumber = Nz(oRst!BOMItem, "")
  124.             sLineFormat = Nz(oRst!LineFormat, "")
  125.             sSupplier = Nz(oRst!Supplier, "")
  126.             sQuantity = Nz(oRst!Quantity, "")
  127.             sDescription = Nz(oRst!Description, "")
  128.  
  129.  
  130.             ' Main Table - Basic Formatting
  131.             setStatus ("Formatting Line " & sItemNumber)
  132.             If sLineFormat <> "Hidden" Then
  133.                 Set oRow = oTable.Rows.Add()
  134.                 iRowCount = iRowCount + 1
  135.                 oRow.Range.Font.Name = "Arial"
  136.                 oRow.Range.Font.Size = 12
  137.                 oRow.Range.Font.Bold = False
  138.                 oRow.Range.Font.Underline = False
  139.                 oRow.Range.Font.Italic = False
  140.                 oRow.Range.Font.Color = wdColorBlack
  141.                 oRow.Shading.BackgroundPatternColorIndex = wdAuto
  142.  
  143.                 ' Main Table - Set Contents
  144.                 oRow.Cells(nItemNumber).Range.Text = sItemNumber
  145.                 If Len(sItemNumber) > 0 Then oRow.Cells(nQuantity).Range.Text = sQuantity
  146.                 oRow.Cells(nSupplier).Range.Text = sSupplier
  147.                 oRow.Cells(nDescription).Range.Text = sDescription
  148.  
  149.                 ' Main Table - Apply formatting
  150.                 Select Case sLineFormat
  151.                     Case "Standard"
  152.                         ' Already there
  153.                     Case "Reference"
  154.                         oRow.Cells(nQuantity).Range.Text = "REF"
  155.                     Case "Hidden"
  156.                         ' Already taken care of
  157.                     Case "Bold"
  158.                         oRow.Cells(nDescription).Range.Bold = True
  159.                         'oRow.Cells(nDescription).Range.Underline = True
  160.                     Case "BoldNote"
  161.                         oRow.Cells(nDescription).Range.Bold = True
  162.                         oRow.Cells(nItemNumber).Range.Text = ""
  163.                         oRow.Cells(nQuantity).Range.Text = ""
  164.                         oRow.Cells(nSupplier).Range.Text = ""
  165.                     Case "Note"
  166.                         oRow.Cells(nItemNumber).Range.Text = ""
  167.                         oRow.Cells(nQuantity).Range.Text = ""
  168.                         oRow.Cells(nSupplier).Range.Text = ""
  169.                         oRow.Cells(nDescription).Shading.BackgroundPatternColorIndex = wdGray25
  170.                         'oRow.Cells(nDescription).Range.HighlightColorIndex = wdGray25
  171.                     Case "Blank Line"
  172.                         oRow.Cells(nItemNumber).Range.Text = ""
  173.                         oRow.Cells(nQuantity).Range.Text = ""
  174.                         oRow.Cells(nSupplier).Range.Text = ""
  175.                         oRow.Cells(nDescription).Range.Text = ""
  176.                     Case "New Page"
  177.                         oRow.Select
  178.                         Selection.InsertBreak (wdPageBreak)
  179.                     Case "Yellow"
  180.                         oRow.Cells(nDescription).Shading.BackgroundPatternColorIndex = wdYellow
  181.                     Case "BoldYellow"
  182.                         oRow.Cells(nDescription).Range.Bold = True
  183.                         oRow.Cells(nDescription).Shading.BackgroundPatternColorIndex = wdYellow
  184.                     Case "Red"
  185.                         oRow.Cells(nDescription).Range.Font.Color = wdColorRed
  186.                     Case "BoldRed"
  187.                         oRow.Cells(nDescription).Range.Bold = True
  188.                         oRow.Cells(nDescription).Range.Font.Color = wdColorRed
  189.                     Case "Green"
  190.                         oRow.Cells(nDescription).Range.Font.Color = wdColorGreen
  191.                     Case "BoldGreen"
  192.                         oRow.Cells(nDescription).Range.Bold = True
  193.                         oRow.Cells(nDescription).Range.Font.Color = wdColorGreen
  194.                     Case "Maroon"
  195.                         oRow.Cells(nDescription).Range.Font.Color = wdColorDarkRed
  196.                     Case "BoldMaroon"
  197.                         oRow.Cells(nDescription).Range.Bold = True
  198.                         oRow.Cells(nDescription).Range.Font.Color = wdColorDarkRed
  199.                 End Select
  200.  
  201.             End If
  202.             oRst.MoveNext
  203.         Wend
  204.         oRst.Close
  205.  
  206.         oDocument.SaveAs (sFileName)
  207.         oDocument.Close
  208.  
  209.     End If
  210.  
  211.     setStatus ("Finished creating BOM for " & sDrawingName & " and " & sJobNumber & ", it took " & DateDiff("s", dStartTime, Now()) & " Seconds")
  212.  
  213. End Function
The section with ' Main Table - Select Contents is where Loop and OpenRecordset happen.
Jan 26 '17 #4
Thanks J, let me digest this... Would it be easier to just have a pre populated table and just insert data and then do some copy and paste of the table above and clear contents and just paste the new data?

I would like to achieve this without using bookmarks, as I'm using Rich Text and the bookmarks don't like that.
Jan 26 '17 #5
jforbes
1,107 Expert 1GB
I don't know what would be easier for you.

I did find that working with Templates would require that the Template be available and maintained. You can also get into file locking issues when using a .doc or .docx as a template/reference, or run the risk of someone editing the source and changing something. Instead, we build the document from scratch within code, which gives us complete control over the Formatting.

I don't think there is bookmarks in the code, but then again I've never attempted to use Word Bookmarks.
Jan 26 '17 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Phil Rutter | last post by:
Hello All, I have about 700 word documents that have 2 tables one is static 4 colums x 5 rows the other is 5 colums x rows ranging from 2 to 100 what i wolud like to do is open the word doc....
8
by: brian kaufmann | last post by:
Hi, I'm new to Access and this may be a basic question but I would appreciate it if you could let me know how to do this: I've created an Access table and would like to insert a column with...
0
by: Dave | last post by:
I am trying to develop Data Access Pages (DAP) within Access 2003, specifically using Pivot Table Lists. When creating pivot tables as forms, all of my records in a table consisting of over 18,000...
0
by: jayne | last post by:
I have created the following code the first part works to create a word document with but when I have created the document and existed word, I then click the button again to open up the document to...
3
by: Galka | last post by:
Hello Why do you think a create table query doesn't create table from the following code? Set myQuery = myDB.QueryDefs("qryCON absent adults previous day log") myQuery.Execute If I execute...
1
by: Cryptographic_ICE | last post by:
Hello, I have a table that contains the name and location of backup tapes. The first Column has the tape ID (a four digit number) and the second column has a drop down box of possible locations...
5
by: Lee | last post by:
I have a xml file, here is sample part: <?xml version="1.0" encoding="UTF-8"?> <ProducsList> <Product id="1"> <SpecList> <Spec> <SpecLabel>Height</SpecLabel> <SpecValue>10</SpecValue>...
0
by: cb123 | last post by:
Hello, Please bear with me I'm new to t-sql. I've had no luck trying to figure this out yet using sql code. I need to use an existing table (data dictionary) as input for creating a new table. ...
15
by: OfficeDummy | last post by:
Hello all! I searched the Internet and this forum for a similar problem, but I found no help... Also, I'm a complete newbie to the fascinating world of programming, VBA and Access, so my question...
2
by: jhoelter | last post by:
Hi, First of all, I thank you for reading the following post and taking a little bit of your time helping me figuring this out. I first tryed to explain my exact case, but it is to hard. Let me...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.