I?ve written a Sub to take records from a Make Table query in Access,
produce a chart in Excel, and then paste the chart into a Word
template at a Bookmark. (I?m using additional procedures to insert
other information into the template and then saving the outcome as a
Word document.)

The Sub for the chart works perfectly the first time I run it after
starting up Access, but if I then run it again, I get an error
message. For my questions to you, I?ve duplicated the problem with the
table and code below.

Here?s my sample Access table:
tblSampleAutomation
Office	Measure
A	12
B	18
C	19
D	21
E	26

I also created a Word template (SampleTemplate.dot) which includes a
Bookmark named InsertChartHere, and I created an Excel spreadsheet
(SampleSpreadsheet.xls) that initially has one Worksheet and no
entries in the cells.

Here is the code I placed in an Access module:
Sub SampleAutomationSub()

    Dim xLApp As Excel.Application
    Dim xLco As Excel.ChartObject
    Dim WordApp As Word.Application
    Dim WordDoc As Word.Document
    
    Set xLApp = New Excel.Application
    Set WordApp = New Word.Application
    Set WordDoc = WordApp.Documents.Add("C:\DBAs\SampleTemplate.dot")

    xLApp.Visible = True

    DoCmd.TransferSpreadsheet acExport, , "tblSampleAutomation",
"C:\DBAs\SampleSpreadsheet.xls"
 
    With xLApp
        .Workbooks.Open ("C:\DBAs\SampleSpreadsheet.xls")
        .Worksheets("tblSampleAutomation").Activate
        .Range("C2").Value = 1
        .Range("C2").Select
        .Selection.Copy
        .Range("B2:B6").Select
        .Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
        .Application.CutCopyMode = False
    End With

    With xLApp.Application
    'The first time through after I start Access, all works fine. But
the second time
    'through, I get an error message here reading:
    'Run-time error '91': Object variable or With block variable not set.
        Set xLco = ActiveWorkbook. _
            Worksheets("tblSampleAutomation").ChartObjects.Add _
            (.InchesToPoints(4.2), .InchesToPoints(0.25), _
            .InchesToPoints(4.5), .InchesToPoints(3.5))
    End With
 
    With xLco
        .Chart.ChartType = xlColumnClustered
        .Chart.SetSourceData Source:=Worksheets( _
            "tblSampleAutomation").Range("A1:B6")
        .Chart.HasLegend = False
        .Activate
        .CopyPicture
    End With
    
    WordApp.Selection.GoTo What:=wdGoToBookmark, Name:="InsertChartHere"
    WordApp.Selection.Paste
    
    WordDoc.SaveAs FileName:="C:\DBAs\SampleReport.doc"
    WordDoc.Close
    WordApp.Quit

    With xLApp
        .Application.DisplayAlerts = False
        .ActiveWorkbook.Worksheets("tblSampleAutomation").Delete
        .Application.DisplayAlerts = True
        .ActiveWorkbook.Close SaveChanges:=True
    End With

    xLApp.Quit
    Set xLApp = Nothing
    Set WordApp = Nothing
    Set WordDoc = Nothing
    Set xLco = Nothing

    MsgBox "Your report is now ready."
?If I insert Stop in the code here and then, when the routine stops, I
click the Reset button,
?I can run the procedure again without an error.
End Sub

I?ve three questions:
1. How should I change and improve the code so it works reliably and efficiently?
2. So I can learn: Why did my code work okay the first time through,
but not after that?
3. Are there a few URLs (or books) you suggest for my further learning
about Automation across the Microsoft Office products?

Thanks.