Here is my code.
Sub sWordTables(daSN2, daGL, daST, daSY, daBEY, daENY, daSUP)
Dim daDB As DAO.Database
Dim appWord As Object 'Word.Application
Dim WordDoc As Object 'Word.Document
Dim wdTable As Object 'Word.Table'
Dim cname As String
Dim cnum As String
Dim gss As Integer
Dim rst As DAO.Recordset
Dim aVar As String
Dim wdcount As Integer
Dim strPath As String
Dim IntHolder As Integer
Set appWord = CreateObject("Word.Application")
strPath = "C:\Users\wpulk\Documents\"
Set daDB = CurrentDb()
Set rst = daDB.OpenRecordset("qry" & daST, dbOpenDynaset)
Set WordDoc = appWord.Documents.Open(strPath & "MasterRecord.docx")
wdcount = WordDoc.Tables.Count
Do While Not rst.EOF
With WordDoc
cname = rst!CourseName
cnum = rst!CourseNumber
gss = rst!GradeScore
incn = rst!IncrementNumber
wdcount = wdcount + 1
If wdcount = 3 Then
.Variables.Add ("StudentName"), daST
.Variables.Add ("Academic Advisor"), daSUP
.Variables.Add ("School Year"), daSY
.Variables.Add ("Grade Level"), daGL
.Variables.Add ("Beginning Date"), daBEY
.Variables.Add ("Ending Date"), daENY
.Fields.Update
End If
If wdcount >= 4 Then
MsgBox .Variables(6).Name
If .Variables("Math").Value = cname Then
.Variables.Add ("Math" & incn & "a"), cnum
.Variables.Add ("Math" & incn & "b"), gss
ElseIf .Variables("English").Value = cname Then
.Variables.Add ("Eng" & incn & "a"), cnum
.Variables.Add ("Eng" & incn & "b"), gss
ElseIf .Variables("Word Building").Value = cname Then
.Variables.Add ("WB" & incn & "a"), cnum
.Variables.Add ("WB" & incn & "b"), gss
ElseIf .Variables("Lierature").Value = cname Then
.Variables.Add ("Lit" & incn & "a"), cnum
.Variables.Add ("Lit" & incn & "b"), gss
ElseIf .Variables("Science").Value = cname Then
.Variables.Add ("Science" & incn & "a"), cnum
.Variables.Add ("Science" & incn & "b"), gss
ElseIf .Variables("Social Studies").Value = cname Then
.Variables.Add ("SocS" & incn & "a"), cnum
.Variables.Add ("SocS" & incn & "b"), gss
ElseIf .Variables("Bible").Value = cname Then
.Variables.Add ("Bible" & incn & "a"), cnum
.Variables.Add ("Bible" & incn & "b"), gss
ElseIf .Variables("Florida History").Value = cname Then
.Variables.Add ("oth" & incn & "a"), cnum
.Variables.Add ("oth" & incn & "b"), gss
.Fields.Update
End If
End If
End With
Loop
appWord.ActiveDocument.SaveAs strPath & "MasterRecord" & daST & ".docx"
Set WordDoc = Nothing
Set appWord = Nothing
End Sub
With other's help I was able to fix my first problem, but I have another problem where my document has 2 tables, and I know how to get the variable number of the first table, but I don't know how to get the second table. The first table has only 1 row and 6 columns, and I was able to process that one correctly, but when the code was going to what I thought was the second table, it did not. it was still at the first table. I don't know what is the right code to do it.
I do not understand why you are initializing wdcount
with the number of tables and also why it is called wdcount
. You are not counting words, you are counting tables. Usually you start counting at 1 or maybe 0.
You could also count backwards. Then starting with the number of tables would be appropriate. But then the counter would have to be decreased at each iteration.
Here is what I suggest:
...
Dim tableIndex As Integer '<=======
...
tableIndex = 1 '<=======
Do While Not rst.EOF
With WordDoc
cname = rst!CourseName
cnum = rst!CourseNumber
gss = rst!GradeScore
incn = rst!IncrementNumber
'===> wdcount = wdcount + 1 <==== removed!
If tableIndex = 1 Then '<=======
.Variables.Add ("StudentName"), daST
.Variables.Add ("Academic Advisor"), daSUP
.Variables.Add ("School Year"), daSY
.Variables.Add ("Grade Level"), daGL
.Variables.Add ("Beginning Date"), daBEY
.Variables.Add ("Ending Date"), daENY
.Fields.Update
End If
If tableIndex >= 2 Then '<=======
MsgBox .Variables(6).Name
If .Variables("Math").Value = cname Then
.Variables.Add ("Math" & incn & "a"), cnum
.Variables.Add ("Math" & incn & "b"), gss
...
...
End If
End If
End With
tableIndex = tableIndex + 1 '<======= ADDED!
Loop
...
I also suspect the conditions like If .Variables("Math").Value = cname Then
to be wrong. Shouldn't you be testing the course name from the database like this:
Select Case cname
Case "Math"
.Variables.Add ("Math" & incn & "a"), cnum
.Variables.Add ("Math" & incn & "b"), gss
Case "English"
.Variables.Add ("Eng" & incn & "a"), cnum
.Variables.Add ("Eng" & incn & "b"), gss
Case "Word Building"
.Variables.Add ("WB" & incn & "a"), cnum
.Variables.Add ("WB" & incn & "b"), gss
Case "Lierature" ' <==== Shoudn't this be "Literature"?
.Variables.Add ("Lit" & incn & "a"), cnum
.Variables.Add ("Lit" & incn & "b"), gss
Case "Science"
.Variables.Add ("Science" & incn & "a"), cnum
.Variables.Add ("Science" & incn & "b"), gss
Case "Social Studies"
.Variables.Add ("SocS" & incn & "a"), cnum
.Variables.Add ("SocS" & incn & "b"), gss
Case "Bible"
.Variables.Add ("Bible" & incn & "a"), cnum
.Variables.Add ("Bible" & incn & "b"), gss
Case "Florida History"
.Variables.Add ("oth" & incn & "a"), cnum
.Variables.Add ("oth" & incn & "b"), gss
End Select
.Fields.Update ' <===== maybe this should be after `Loop`?
Or simplified:
Dim prefix As String
Select Case cname
Case "Math"
prefix = "Math"
Case "English"
prefix = "Eng"
Case "Word Building"
prefix = "WB"
Case "Lierature" ' <==== Shoudn't this be "Literature"?
prefix = "Lit"
Case "Science"
prefix = "Science"
Case "Social Studies"
prefix = "SocS"
Case "Bible"
prefix = "Bible"
Case "Florida History"
prefix = "oth"
End Select
.Variables.Add (prefix & incn & "a"), cnum
.Variables.Add (prefix & incn & "b"), gss
Or even more simplified. First we take cname
as a prefix and assign a corrected version if the prefix differs from cname
:
Dim prefix As String
prefix = cname
Select Case cname
Case "English"
prefix = "Eng"
Case "Word Building"
prefix = "WB"
Case "Lierature" ' <==== Shoudn't this be "Literature"?
prefix = "Lit"
Case "Social Studies"
prefix = "SocS"
Case "Florida History"
prefix = "oth"
End Select
.Variables.Add (prefix & incn & "a"), cnum
.Variables.Add (prefix & incn & "b"), gss
You are also not moving to the next record. The end of the loop should look like this:
...
End With
tableIndex = tableIndex + 1
rst.MoveNext
Loop
WordDoc.Fields.Update
appWord.ActiveDocument.SaveAs strPath & "MasterRecord" & daST & ".docx"
...