[Access --> Word] Code only works once
I created a button an an access form (no queries or recordsets are involved). The code is supposed to open a Word doc and create a table and then populate cells with simple text. It works once and then I have to restart access to get it to work correctly again.
Here is the code. Your help is appreciated.
Code:
Private Sub btnBuildTable2_Click()
On Error Resume Next
Dim strStatus As String
'strStatus = "Compliant"
strStatus = "Non-Compliant Low"
'strStatus = "Non-Compliant Medium"
'strStatus = "Non-Compliant High"
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
Set objDoc = objWord.Documents.Add()
Set objRange = objDoc.Range()
objDoc.Tables.Add objRange, 1, 4
Set objTable = objDoc.Tables(1)
objTable.Style = "table grid"
'Add Rows
objTable.Rows.Add
objTable.Rows.Add
objTable.Rows.Add
With objTable
.Cell(2, 4).Range.Cells(1).Split numcolumns:=2
'Do Merges
.Cell(Row:=1, Column:=1).Merge _
MergeTo:=.Cell(Row:=1, Column:=4)
.Cell(Row:=2, Column:=1).Merge _
MergeTo:=.Cell(Row:=2, Column:=3)
.Cell(Row:=3, Column:=1).Merge _
MergeTo:=.Cell(Row:=3, Column:=4)
.Cell(Row:=4, Column:=1).Merge _
MergeTo:=.Cell(Row:=4, Column:=3)
End With
With objTable
'Do Colors
.Rows.Item(2).Shading.BackgroundPatternColor = RGB(255, 255, 0) 'Yellow
.Rows.Item(3).Shading.BackgroundPatternColor = RGB(222, 219, 222) 'Gray
End With
With objTable
'Write "Static" text
.Cell(2, 2).Range.Font.Bold = True
.Cell(2, 2).Range.Paragraphs.Alignment = wdAlignParagraphCenter
.Cell(2, 2).VerticalAlignment = wdCellAlignVerticalCenter
.Cell(2, 2).Range.Text = "Test" & vbCrLf & "Results"
.Cell(2, 3).Range.Paragraphs.Alignment = wdAlignParagraphCenter
.Cell(2, 3).VerticalAlignment = wdCellAlignVerticalCenter
.Cell(2, 3).Range.Font.Bold = True
.Cell(2, 3).Range.Text = "Impact" & vbCrLf & "Code"
End With
With objTable
'Write "Dynamic" text
.Cell(1, 1).Range.Font.Bold = True
.Cell(1, 1).Range.Text = "{(U) Table B<<NUM>>: <<TABLETITLE>>"
.Cell(3, 1).Range.Font.Bold = True
.Cell(3, 1).Range.Text = "<<IA CONTROL>>"
Select Case strStatus
Case "Compliant"
.Cell(4, 2).Shading.BackgroundPatternColor = RGB(0, 178, 82)
.Cell(4, 2).Range.Paragraphs.Alignment = wdAlignParagraphCenter
.Cell(4, 2).VerticalAlignment = wdCellAlignVerticalCenter
.Cell(4, 2).Range.Font.Bold = True
.Cell(4, 2).Range.Text = strStatus
Case "Non-Compliant Low"
.Cell(4, 2).Range.Cells(1).Split numcolumns:=2
.Cell(4, 2).Range.Paragraphs.Alignment = wdAlignParagraphCenter
.Cell(4, 2).VerticalAlignment = wdCellAlignVerticalCenter
.Cell(4, 2).Shading.BackgroundPatternColor = RGB(255, 0, 0)
.Cell(4, 2).Range.Font.Bold = True
.Cell(4, 2).Range.Text = "Non-Compliant"
.Cell(4, 3).Range.Paragraphs.Alignment = wdAlignParagraphCenter
.Cell(4, 3).VerticalAlignment = wdCellAlignVerticalCenter
.Cell(4, 3).Shading.BackgroundPatternColor = RGB(255, 0, 0)
.Cell(4, 3).Range.Font.Bold = True
.Cell(4, 3).Range.Text = "Low"
Case "Non-Compliant Medium"
.Cell(4, 2).Range.Cells(1).Split numcolumns:=2
.Cell(4, 2).Range.Paragraphs.Alignment = wdAlignParagraphCenter
.Cell(4, 2).VerticalAlignment = wdCellAlignVerticalCenter
.Cell(4, 2).Shading.BackgroundPatternColor = RGB(255, 0, 0)
.Cell(4, 2).Range.Font.Bold = True
.Cell(4, 2).Range.Text = "Non-Compliant"
.Cell(4, 3).Range.Paragraphs.Alignment = wdAlignParagraphCenter
.Cell(4, 3).VerticalAlignment = wdCellAlignVerticalCenter
.Cell(4, 3).Shading.BackgroundPatternColor = RGB(255, 0, 0)
.Cell(4, 3).Range.Font.Bold = True
.Cell(4, 3).Range.Text = "Medium"
Case "Non-Compliant High"
.Cell(4, 2).Range.Cells(1).Split numcolumns:=2
.Cell(4, 2).Range.Paragraphs.Alignment = wdAlignParagraphCenter
.Cell(4, 2).VerticalAlignment = wdCellAlignVerticalCenter
.Cell(4, 2).Shading.BackgroundPatternColor = RGB(255, 0, 0)
.Cell(4, 2).Range.Font.Bold = True
.Cell(4, 2).Range.Text = "Non-Compliant"
.Cell(4, 3).Range.Paragraphs.Alignment = wdAlignParagraphCenter
.Cell(4, 3).VerticalAlignment = wdCellAlignVerticalCenter
.Cell(4, 3).Shading.BackgroundPatternColor = RGB(255, 0, 0)
.Cell(4, 3).Range.Font.Bold = True
.Cell(4, 3).Range.Text = "High"
End Select
With objTable.Rows(4).Cells(1).Range.Select
Selection.Font.Bold = True
Selection.TypeText Text:="<<IA TEAM NAME>> Test Results: "
Selection.TypeParagraph
Selection.Font.Bold = False
Selection.TypeText Text:="<<RESULTS>> "
Selection.TypeParagraph
Selection.Font.Bold = True
Selection.TypeText Text:="<<IA TEAM NAME>> Comments: "
Selection.TypeParagraph
Selection.Font.Bold = False
Selection.TypeText Text:="<<COMMENTS>> "
Selection.TypeParagraph
Selection.Font.Bold = True
Selection.TypeText Text:="Severity Code Recommendations: "
Selection.TypeParagraph
Selection.Font.Bold = False
Selection.TypeText Text:="<<RECOMMENDATIONS>>"
End With
End With
Set CommentsCell = Nothing
Set objWord = Nothing
Set objDoc = Nothing
Set objTable = Nothing
End Sub
Re: [Access --> Word] Code only works once
For starters you can get rid of the very first line of your code. If you use "On Error Resume Next", you will never figure out what's wrong with your code, comment it out or delete it, then when you run your code the debuger will tell you exactly where your problem is. Just as a heads up, your problem is within this portion of your code:
vb Code:
With objTable.Rows(4).Cells(1).Range.Select
Selection.Font.Bold = True
Selection.TypeText Text:="<<IA TEAM NAME>> Test Results: "
Selection.TypeParagraph
Selection.Font.Bold = False
Selection.TypeText Text:="<<RESULTS>> "
Selection.TypeParagraph
Selection.Font.Bold = True
Selection.TypeText Text:="<<IA TEAM NAME>> Comments: "
Selection.TypeParagraph
Selection.Font.Bold = False
Selection.TypeText Text:="<<COMMENTS>> "
Selection.TypeParagraph
Selection.Font.Bold = True
Selection.TypeText Text:="Severity Code Recommendations: "
Selection.TypeParagraph
Selection.Font.Bold = False
Selection.TypeText Text:="<<RECOMMENDATIONS>>"
End With
Just out of curiosity, why did you change the format of your code?? The beginning portion worked just fine:
Code:
With objTable
'Write "Dynamic" text
.Cell(1, 1).Range.Font.Bold = True
Then you went to:
Code:
With objTable.Rows(4).Cells(1).Range.Select
Selection.Font.Bold = True
Re: [Access --> Word] Code only works once
i agree access should consider the selection to the access application selection, as you do not specify otherwise
unless no other option, avoid using selection, select, activedocument or active anything else
