1

I am trying to create tables in MS Word and fill them with data from MS Access. All of the code I have written is below in basMain and basUtilities. I am having trouble with Private Sub FillCells in basMain. I previously used this sub to fill tables with all text fields, however this table needs to allow other formats. The data listed in basUtilites is all text except tblEmployees.[Notes] and tblEmployees.[Photo]. The notes are a memo and over the limit of characters for text and the photo is a bmp picture. Also, the tables should not have any form fields. Any help with this is appreciated. Thank you!!

Here is a link to source files: https://jumpshare.com/b/Sy6mxurdTdpSSdcqKLUJ

basMain

Option Explicit
Public Const cstrPath As String = "\Source\243SRC_Final.accdb"
Public connEmp As ADODB.Connection
Public rstEmps As ADODB.Recordset
Sub ListEmps()
  Dim strAsk As String
  strAsk = InputBox("Which country?", "County Request")
  If strAsk = "UK" Then
    Call basUtilities.connect("UK")
  ElseIf strAsk = "USA" Then
    Call basUtilities.connect("USA")
  Else
    MsgBox "This name is not recognized!"
  End If
End Sub
Public Sub CreateTables()
  Dim sngRecords As Single, intFields As Integer, intI As Integer
  sngRecords = rstEmps.RecordCount
  intFields = rstEmps.Fields.Count
  rstEmps.MoveFirst
  For intI = 1 To sngRecords
    Dim intF As Integer
    Selection.TypeParagraph
    ActiveDocument.Tables.Add Range:=Selection.Range, NumRows:=intFields, NumColumns:= _
    2, DefaultTableBehavior:=wdWord9TableBehavior, AutoFitBehavior:=wdAutoFitContent
    With Selection.Tables(1)
      .Columns.PreferredWidth = InchesToPoints(6)
      If .Style <> "Table Grid" Then
        .Style = "Table Grid"
      End If
      .ApplyStyleHeadingRows = True
      .ApplyStyleLastRow = True
      .ApplyStyleFirstColumn = True
      .ApplyStyleLastColumn = True
    End With
    Call FillCells(intFields)
    Selection.EndKey Unit:=wdStory
    Selection.TypeParagraph
    rstEmps.MoveNext
  Next intI
  rstEmps.Close
  connEmp.Close
  Set rstEmps = Nothing
  Set connEmp = Nothing
  ActiveWindow.ActivePane.View.ShowAll = True
End Sub
Private Sub FillCells(intFields As Integer)
  Dim intF As Integer
  For intF = 0 To intFields - 1
    Dim strFieldName As String
    strFieldName = Right(rstEmps.Fields(intF).Name, _
    Len(rstEmps.Fields(intF).Name))
    Selection.TypeText Text:=strFieldName
    Selection.ParagraphFormat.Alignment = wdAlignParagraphRight
    Selection.MoveRight Unit:=wdCell
    Selection.Fields.Add Range:=Selection.Range, _
    Type:=wdFieldFormTextInput
    Selection.PreviousField.Select
    With Selection.FormFields(1)
      .Name = "txt" & strFieldName
      .Enabled = True
      .OwnHelp = False
      .OwnStatus = False
      With .TextInput
        .EditType Type:=wdRegularText, _
        Default:=rstEmps.Fields(intF).Value, Format:=""
        .Width = 0
      End With
    End With
    Selection.MoveLeft Unit:=wdCell
    If intF <> (intFields - 1) Then
      Selection.MoveDown Unit:=wdLine, Count:=1
    End If
  Next intF
End Sub

basUtilities

Option Explicit
Public Sub connect(strVar As String)
Dim strEmps As String, strPath As String
  strEmps = "SELECT tblEmployees.[FirstName], tblEmployees.[LastName], tblEmployees.[Notes], tblEmployees.[photo] "
  strEmps = strEmps & "FROM tblEmployees "
  strEmps = strEmps & "WHERE tblEmployees.[Country]= '" & strVar & "' ORDER BY tblEmployees.[LastName]"
  strPath = ThisDocument.Path & cstrPath
  Set connEmp = New ADODB.Connection
  Set rstEmps = New ADODB.Recordset
  connEmp.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & strPath & "'"
  rstEmps.Open strEmps, connEmp, adOpenKeyset, adLockOptimistic
  Call CreateTables
End Sub
nonya
  • 25
  • 3
  • This question is of a similar nature to my answer here (http://stackoverflow.com/questions/19916536/automated-word-template-creation-with-vba/20182786#20182786) regarding tablating data in Word from Excel. There might be some subtle differences but it should point you in the right direction. – CuberChase Dec 02 '14 at 08:39

0 Answers0