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. - Private Sub Command0_Click()
-
Dim numberOfTables As Integer
-
Dim iCount As Integer
-
Dim appWord As Word.Application
-
Dim wdDoc As Word.Document
-
-
Set appWord = New Word.Application
-
-
appWord.Visible = True
-
-
Set wdDoc = appWord.Documents.Open(FileName:="C:\Test\Test.doc", ReadOnly:=False)
-
-
-
numberOfTables = InputBox("How many tables to make?", "Tables")
-
-
For iCount = 0 To numberOfTables - 1
-
-
ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=7, NumColumns:= _
-
2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
-
wdAutoFitFixed
-
With Selection.Tables(1)
-
If .Style <> "Table Grid" Then
-
.Style = "Table Grid"
-
End If
-
.ApplyStyleHeadingRows = True
-
.ApplyStyleLastRow = False
-
.ApplyStyleFirstColumn = True
-
.ApplyStyleLastColumn = False
-
.ApplyStyleRowBands = True 'Office 2010
-
'.ApplyStyleColumnBands = False 'Office 2007
-
End With
-
-
Selection.EndKey Unit:=wdStory
-
Selection.TypeParagraph
-
-
Next iCount
-
-
End Sub
5 2378
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): -
Private Sub Command0_Click()
-
Dim numberOfTables As Integer
-
Dim iCount As Integer
-
Dim appWord As Word.Application
-
Dim wdDoc As Word.Document
-
Dim oTables(50) As Word.Table -
-
Set appWord = New Word.Application
-
-
appWord.Visible = True
-
-
Set wdDoc = appWord.Documents.Open(FileName:="C:\Test\Test.doc", ReadOnly:=False)
-
-
-
numberOfTables = InputBox("How many tables to make?", "Tables")
-
-
For iCount = 0 To numberOfTables - 1
-
Set oTables(iCount+1) = ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=7, NumColumns:= _ -
2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:= _
-
wdAutoFitFixed
-
...
-
-
Next iCount
-
-
oTable(4).Cell(1,1).Range.Text = "This is the Upper Left Cell in Table 4" -
oTable(3).Cell(3,4).Range.Text = "This is the Fourth Cell on the Third Row in Table 3" -
-
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.
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?
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. - Public Function exportDrawingBOM(ByRef sDrawingName As String, ByRef sJobNumber As String) As Boolean
-
-
' 72 Points = Inch
-
' Y -->
-
' +---
-
' X |
-
-
Dim sLocation As String
-
Dim sFileName As String
-
Dim sBackupFileName As String
-
Dim sPrefix As String
-
-
Dim oWord As Word.Application
-
Dim oDocument As Word.Document
-
Dim oSection As Word.Section
-
Dim oRange As Word.Range
-
Dim oSubRange As Word.Range
-
Dim oTable As Word.Table
-
Dim oRow As Word.Row
-
Dim oCell As Word.Cell
-
Dim iRowCount As Integer
-
Dim iPageCount As Integer
-
-
Dim sSQL As String
-
Dim oRst As DAO.Recordset
-
-
Dim sItemNumber As String
-
Dim sSupplier As String
-
Dim sLineFormat As String
-
-
Dim nItemNumber As Integer
-
Dim nQuantity As Integer
-
Dim nSupplier As Integer
-
Dim nDescription As Integer
-
-
-
' Row Constants
-
nItemNumber = 1
-
nQuantity = 2
-
nSupplier = 3
-
nDescription = 4
-
-
If Len(sDrawingName) > 0 And Len(sJobNumber) > 0 Then
-
setStatus ("Creating BOM for " & sDrawingName & " and " & sJobNumber & "")
-
-
-
' Determine file locations and make backups
-
sLocation = getBOMPath(sDrawingName)
-
sFileName = sLocation & sDrawingName & ".doc"
-
sBackupFileName = mBOMBackupBackupDir & sDrawingName & ".doc"
-
sBackupFileName = appendDateTimeToPath(sBackupFileName)
-
If fileExists(sFileName) Then
-
If msgBoxAreYouSure("The BOM, '" & sFileName & "' already exits, would you like to make a backup copy first?") Then
-
FileCopy sFileName, sBackupFileName
-
End If
-
Kill sFileName
-
End If
-
-
-
' Create the Word BOM
-
' -------------------
-
setStatus ("Creating Word Document")
-
Set oDocument = New Word.Document
-
Set oSection = oDocument.Sections(1)
-
-
' Page Layout
-
oDocument.PageSetup.Orientation = wdOrientLandscape
-
oDocument.PageSetup.RightMargin = 36
-
oDocument.PageSetup.LeftMargin = 36
-
oDocument.PageSetup.TopMargin = 18
-
oDocument.PageSetup.BottomMargin = 18
-
oDocument.PageSetup.FooterDistance = 0
-
-
-
' Body
-
setStatus ("Creating the Main Table")
-
Set oRange = oSection.Range
-
oRange.Font.Name = "Arial"
-
oRange.Font.Size = 12
-
-
-
' Main Table - Create and Format
-
Set oTable = oDocument.Tables.Add(oRange, 1, 4)
-
iRowCount = iRowCount + 1
-
oTable.PreferredWidth = 94
-
oTable.Cell(1, nItemNumber).Width = 54
-
oTable.Cell(1, nQuantity).Width = 41
-
oTable.Cell(1, nSupplier).Width = 85
-
oTable.Cell(1, nDescription).Width = 552
-
oTable.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter
-
oTable.Cell(1, 4).Range.ParagraphFormat.Alignment = wdAlignParagraphLeft
-
oTable.Range.Font.Name = "Arial"
-
oTable.Range.Font.Size = 12
-
oTable.Cell(1, 1).Borders.OutsideLineStyle = wdLineStyleSingle
-
oTable.Cell(1, 2).Borders.OutsideLineStyle = wdLineStyleSingle
-
oTable.Cell(1, 3).Borders.OutsideLineStyle = wdLineStyleSingle
-
oTable.Cell(1, 4).Borders.OutsideLineStyle = wdLineStyleSingle
-
-
' Main Table - Line Spacing
-
oTable.Cell(1, 1).Range.ParagraphFormat.SpaceBefore = 0
-
oTable.Cell(1, 2).Range.ParagraphFormat.SpaceBefore = 0
-
oTable.Cell(1, 3).Range.ParagraphFormat.SpaceBefore = 0
-
oTable.Cell(1, 4).Range.ParagraphFormat.SpaceBefore = 0
-
oTable.Cell(1, 1).Range.ParagraphFormat.SpaceAfter = 0
-
oTable.Cell(1, 2).Range.ParagraphFormat.SpaceAfter = 0
-
oTable.Cell(1, 3).Range.ParagraphFormat.SpaceAfter = 0
-
oTable.Cell(1, 4).Range.ParagraphFormat.SpaceAfter = 0
-
oTable.Cell(1, 1).Range.ParagraphFormat.LineSpacing = 12
-
oTable.Cell(1, 2).Range.ParagraphFormat.LineSpacing = 12
-
oTable.Cell(1, 3).Range.ParagraphFormat.LineSpacing = 12
-
oTable.Cell(1, 4).Range.ParagraphFormat.LineSpacing = 12
-
-
' Main Table - Select Contents -
sSQL = ""
-
sSQL = sSQL & "SELECT * FROM LineItem "
-
sSQL = sSQL & "WHERE DocType='Drawing' "
-
sSQL = sSQL & "AND LineType='BOM' "
-
sSQL = sSQL & "AND DocNumber='" & sDrawingName & "' "
-
sSQL = sSQL & "ORDER BY PrintIndex "
-
-
Set oRst = CurrentDB.OpenRecordset(sSQL, dbOpenDynaset, dbForwardOnly + dbSeeChanges)
-
While Not oRst.EOF
-
sItemNumber = Nz(oRst!BOMItem, "")
-
sLineFormat = Nz(oRst!LineFormat, "")
-
sSupplier = Nz(oRst!Supplier, "")
-
sQuantity = Nz(oRst!Quantity, "")
-
sDescription = Nz(oRst!Description, "")
-
-
-
' Main Table - Basic Formatting
-
setStatus ("Formatting Line " & sItemNumber)
-
If sLineFormat <> "Hidden" Then
-
Set oRow = oTable.Rows.Add()
-
iRowCount = iRowCount + 1
-
oRow.Range.Font.Name = "Arial"
-
oRow.Range.Font.Size = 12
-
oRow.Range.Font.Bold = False
-
oRow.Range.Font.Underline = False
-
oRow.Range.Font.Italic = False
-
oRow.Range.Font.Color = wdColorBlack
-
oRow.Shading.BackgroundPatternColorIndex = wdAuto
-
-
' Main Table - Set Contents
-
oRow.Cells(nItemNumber).Range.Text = sItemNumber
-
If Len(sItemNumber) > 0 Then oRow.Cells(nQuantity).Range.Text = sQuantity
-
oRow.Cells(nSupplier).Range.Text = sSupplier
-
oRow.Cells(nDescription).Range.Text = sDescription
-
-
' Main Table - Apply formatting
-
Select Case sLineFormat
-
Case "Standard"
-
' Already there
-
Case "Reference"
-
oRow.Cells(nQuantity).Range.Text = "REF"
-
Case "Hidden"
-
' Already taken care of
-
Case "Bold"
-
oRow.Cells(nDescription).Range.Bold = True
-
'oRow.Cells(nDescription).Range.Underline = True
-
Case "BoldNote"
-
oRow.Cells(nDescription).Range.Bold = True
-
oRow.Cells(nItemNumber).Range.Text = ""
-
oRow.Cells(nQuantity).Range.Text = ""
-
oRow.Cells(nSupplier).Range.Text = ""
-
Case "Note"
-
oRow.Cells(nItemNumber).Range.Text = ""
-
oRow.Cells(nQuantity).Range.Text = ""
-
oRow.Cells(nSupplier).Range.Text = ""
-
oRow.Cells(nDescription).Shading.BackgroundPatternColorIndex = wdGray25
-
'oRow.Cells(nDescription).Range.HighlightColorIndex = wdGray25
-
Case "Blank Line"
-
oRow.Cells(nItemNumber).Range.Text = ""
-
oRow.Cells(nQuantity).Range.Text = ""
-
oRow.Cells(nSupplier).Range.Text = ""
-
oRow.Cells(nDescription).Range.Text = ""
-
Case "New Page"
-
oRow.Select
-
Selection.InsertBreak (wdPageBreak)
-
Case "Yellow"
-
oRow.Cells(nDescription).Shading.BackgroundPatternColorIndex = wdYellow
-
Case "BoldYellow"
-
oRow.Cells(nDescription).Range.Bold = True
-
oRow.Cells(nDescription).Shading.BackgroundPatternColorIndex = wdYellow
-
Case "Red"
-
oRow.Cells(nDescription).Range.Font.Color = wdColorRed
-
Case "BoldRed"
-
oRow.Cells(nDescription).Range.Bold = True
-
oRow.Cells(nDescription).Range.Font.Color = wdColorRed
-
Case "Green"
-
oRow.Cells(nDescription).Range.Font.Color = wdColorGreen
-
Case "BoldGreen"
-
oRow.Cells(nDescription).Range.Bold = True
-
oRow.Cells(nDescription).Range.Font.Color = wdColorGreen
-
Case "Maroon"
-
oRow.Cells(nDescription).Range.Font.Color = wdColorDarkRed
-
Case "BoldMaroon"
-
oRow.Cells(nDescription).Range.Bold = True
-
oRow.Cells(nDescription).Range.Font.Color = wdColorDarkRed
-
End Select
-
-
End If
-
oRst.MoveNext
-
Wend
-
oRst.Close
-
-
oDocument.SaveAs (sFileName)
-
oDocument.Close
-
-
End If
-
-
setStatus ("Finished creating BOM for " & sDrawingName & " and " & sJobNumber & ", it took " & DateDiff("s", dStartTime, Now()) & " Seconds")
-
-
End Function
The section with ' Main Table - Select Contents is where Loop and OpenRecordset happen.
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.
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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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....
|
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...
|
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...
|
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...
|
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...
|
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...
|
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>...
|
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.
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
| |