Building charts in Excel from data out of Access to be placed in Powerpoint

New to the VBA community so please forgive me if this is not the proper way to handle my issue. I'm utilizing Access, Excel, and Powerpoint '16. I'm having trouble with some code that I have been playing with. This process takes place via Access, a form with a button will be used to generate a powerpoint presentation. The text within the powerpoint remains the same, but I have charts the will be effected when the next presentation is generated. The Charts are driven by the data within the database. I create these charts within excel. I have built this code in sections and stepped through each section with no problems. When I compile all of the code together the code carries out the process with no errors; however, the first chart that is created in excel is pasted in all chart positions in powerpoint. So I have a bunch of duplicate charts. Below you will find a portion of the code I am working with where the first chart is made. When I step through the second chart build process it builds the chart but isn't copying that chart. Its like the clipboard is not updating with the new image copied.

Private Sub Command30_Click()

'   Powerpoint
    Dim ppApp As PowerPoint.Application
    Dim ppPres As PowerPoint.Presentation
    Dim ppslide As PowerPoint.slide

'    Excel
    Dim excelapp As Excel.Application
    Dim excelwkb As Excel.Workbook
    Dim excelsht As Excel.Worksheet

'    Access
    Dim rst As Recordset

    Set ppApp = New PowerPoint.Application
    ppApp.Visible = True
    ppApp.Activate
    Set ppPres = ppApp.Presentations.Add

        With ppPres
        .PageSetup.SlideSize = 2
        End With

'    SLIDE 7
Set ppslide = ppPres.Slides.Add(1, ppLayoutTitleOnly)

ppslide.Shapes(1).Width = 720
ppslide.Shapes(1).Top = 20
ppslide.Shapes(1).Left = 0

ppslide.Shapes(1).TextFrame.TextRange = "Same old Text"

    With ppslide.Shapes(1).TextFrame
    .TextRange.ParagraphFormat.Alignment = ppAlignCenter
    .TextRange.Font.Size = 28
    .TextRange.Font.Name = tahoma
    .TextRange.Font.Bold = msoTrue
    .TextRange.Font.Color = RGB(0, 0, 205)
    .VerticalAnchor = msoAnchorTop
    End With

ppslide.Shapes.AddTextbox msoTextOrientationHorizontal, 18, 420, 658.8, 425.52
ppslide.Shapes(2).TextFrame.TextRange = "Some more old Text"

    With ppslide.Shapes(2).TextFrame
    .TextRange.ParagraphFormat.Alignment = ppAlignLeft
    .TextRange.Font.Size = 12
    .TextRange.Font.Name = tahoma
    .TextRange.Font.Bold = msoTrue
    .VerticalAnchor = msoAnchorTop
    End With

'        Step into Excel to make Chart

    Set rst = Application.CurrentDb.OpenRecordset("qrydatabase1")
    Set excelapp = CreateObject("excel.application")
    Set excelwkb = excelapp.Workbooks.Add
    Set excelsht = excelwkb.Worksheets.Add

    excelapp.Visible = False

    With excelsht
    .Range("A2").CopyFromRecordset rst
    .Name = "DB1"
    .Range("B1").Value = "Items Processed"
    .Range("C1").Value = "Man Hours"
    .Range("D1:D7").Delete
    excelapp.Charts.Add
    .Shapes.AddChart2(201, xlColumnClustered).Select

    ActiveChart.FullSeriesCollection(1).ChartType = xlLine
    ActiveChart.FullSeriesCollection(1).AxisGroup = 2
    ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(2).AxisGroup = 1
    ActiveChart.PlotBy = xlColumns
    ActiveChart.SetElement (msoElementDataTableWithLegendKeys)
    ActiveChart.SetElement (msoElementLegendNone)
    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Text = "This is your data"
    ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Man Hours"
    ActiveChart.Axes(xlValue, xlSecondary).HasTitle = True
    ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Items Processed"
    ActiveChart.Axes(xlValue).MajorGridlines.Delete
    ActiveChart.CopyPicture

    End With

    excelwkb.Close (0)
    excelapp.Quit

'    Back to Powerpoint

ppslide.Shapes.Paste
    With ppslide.Shapes(3)
    .Width = 618.48
    .Left = 110
    .Top = 60
    .Height = 354.96
    End With


'    SLIDE 8


Set ppslide = ppPres.Slides.Add(2, ppLayoutTitleOnly)

ppslide.Shapes(1).Width = 720
ppslide.Shapes(1).Top = 20
ppslide.Shapes(1).Left = 0
ppslide.Shapes(1).TextFrame.TextRange = "Same Old Text"

    With ppslide.Shapes(1).TextFrame
    .TextRange.ParagraphFormat.Alignment = ppAlignCenter
    .TextRange.Font.Size = 28
    .TextRange.Font.Name = tahoma
    .TextRange.Font.Bold = msoTrue
    .TextRange.Font.Color = RGB(0, 0, 205)
    .VerticalAnchor = msoAnchorTop
    End With

ppslide.Shapes.AddTextbox msoTextOrientationHorizontal, 0, 420, 720, 425.52
ppslide.Shapes(2).TextFrame.TextRange = _
"Again with the Same Old Text"    

    With ppslide.Shapes(2).TextFrame
    .TextRange.ParagraphFormat.Alignment = ppAlignLeft
    .TextRange.ParagraphFormat.Bullet.Character = 8226
    .TextRange.Font.Size = 16
    .TextRange.Font.Name = tahoma
    .VerticalAnchor = msoAnchorTop
    End With

'        Step into Excel to make Chart

    Set rst = Application.CurrentDb.OpenRecordset("qrydata2")
    Set excelapp = CreateObject("excel.application")
    Set excelwkb = excelapp.Workbooks.Add
    Set excelsht = excelwkb.Worksheets.Add

    excelapp.Visible = False


    With excelsht
    .Range("A2").CopyFromRecordset rst
    .Name = "DB2"
    .Range("B1").Value = "Items Processed"
    .Range("C1").Value = "Man Hours"

    excelapp.Charts.Add

    .Shapes.AddChart2(201, xlColumnClustered).Select

    ActiveChart.FullSeriesCollection(1).ChartType = xlLine
    ActiveChart.FullSeriesCollection(1).AxisGroup = 2
    ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
    ActiveChart.FullSeriesCollection(2).AxisGroup = 1

    ActiveChart.PlotBy = xlColumns
    ActiveChart.SetElement (msoElementDataTableWithLegendKeys)
    ActiveChart.SetElement (msoElementLegendNone)
    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Text = "This is more of your data"
    ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
    ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Man Hours"
    ActiveChart.Axes(xlValue, xlSecondary).HasTitle = True
    ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Items Processed"
    ActiveChart.Axes(xlValue).MajorGridlines.Delete
    ActiveChart.copy

    End With

    excelwkb.Close (0)
    excelapp.Quit



'    Back to Powerpoint

ppslide.Shapes.Paste

    With ppslide.Shapes(3)
    .Width = 618.48
    .Left = 110
    .Top = 60
    .Height = 354.96
    End With

Solution

So after doing a lot of reading and trial by error I have found the answer to my problem. First I want to thank Tim for opening my eyes, thanks man you really helped me look at my code differently to point me into the right direction. Please see the revised code below.

Summary of my issues:

I had not referenced excel properly.

The reason copy and paste was not working properly was, because after it created the second chart and copied it, the excel application was told to close and quit. When this was executed I received the excel warning asking to save, I had to disable this to have it paste the chart properly in powerpoint.

Lastly, I am a novice coder at best, my point is that this code still needs to be cleaned up and as Tim has stated to make a more robust code I should and will eventually take the guess work away from excel. When I do this I will update the code on this forum.

Private Sub Command30_Click()

'   Powerpoint
Dim ppApp As PowerPoint.Application
Dim ppPres As PowerPoint.Presentation
Dim ppslide As PowerPoint.slide

'    Excel
Dim excelapp As Excel.Application
Dim excelwkb As Excel.Workbook
Dim excelsht As Excel.Worksheet

'    Access
Dim rst As Recordset




Set ppApp = New PowerPoint.Application

ppApp.Visible = True
ppApp.Activate

Set ppPres = ppApp.Presentations.Add

    With ppPres
    .PageSetup.SlideSize = 2
    End With


'    SLIDE 7

Set ppslide = ppPres.Slides.Add(1, ppLayoutTitleOnly)

ppslide.Shapes(1).Width = 720
ppslide.Shapes(1).Top = 20
ppslide.Shapes(1).Left = 0

ppslide.Shapes(1).TextFrame.TextRange = "Text"

    With ppslide.Shapes(1).TextFrame
    .TextRange.ParagraphFormat.Alignment = ppAlignCenter
    .TextRange.Font.Size = 28
    .TextRange.Font.Name = tahoma
    .TextRange.Font.Bold = msoTrue
    .TextRange.Font.Color = RGB(0, 0, 205)
    .VerticalAnchor = msoAnchorTop
    End With

ppslide.Shapes.AddTextbox msoTextOrientationHorizontal, 18, 420, 658.8, 425.52
ppslide.Shapes(2).TextFrame.TextRange = "Text"

    With ppslide.Shapes(2).TextFrame
    .TextRange.ParagraphFormat.Alignment = ppAlignLeft
    .TextRange.Font.Size = 12
    .TextRange.Font.Name = tahoma
    .TextRange.Font.Bold = msoTrue
    .VerticalAnchor = msoAnchorTop
    End With

'        Step into Excel to make Chart
    Set rst = Application.CurrentDb.OpenRecordset("qryDB1")
    Set excelapp = CreateObject("excel.application")
    Set excelwkb = excelapp.Workbooks.Add
    Set excelsht = excelwkb.Worksheets.Add

    excelapp.Visible = False

    With excelsht
    .Range("A2").CopyFromRecordset rst
    .Name = "Text"
    .Range("B1").Value = "Items Processed"
    .Range("C1").Value = "Man Hours"
    .Range("D1:D7").Delete
    End With

excelapp.Charts.Add
    excelapp.ActiveChart.FullSeriesCollection(1).ChartType = xlLine
    excelapp.ActiveChart.FullSeriesCollection(1).AxisGroup = 2
    excelapp.ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
    excelapp.ActiveChart.FullSeriesCollection(2).AxisGroup = 1
    excelapp.ActiveChart.PlotBy = xlColumns
    excelapp.ActiveChart.SetElement (msoElementDataTableWithLegendKeys)
    excelapp.ActiveChart.SetElement (msoElementLegendNone)
    excelapp.ActiveChart.HasTitle = True
    excelapp.ActiveChart.ChartTitle.Text = "Text"
    excelapp.ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
    excelapp.ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Man Hours"
    excelapp.ActiveChart.Axes(xlValue, xlSecondary).HasTitle = True
    excelapp.ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Items Processed"
    excelapp.ActiveChart.Axes(xlValue).MajorGridlines.Delete
    excelapp.ActiveChart.CopyPicture

    excelapp.DisplayAlerts = False
    excelwkb.Close savechanges:=False
    excelapp.Quit

'    Back to Powerpoint
ppslide.Shapes.Paste
    With ppslide.Shapes(3)
    .Width = 618.48
    .Left = 110
    .Top = 60
    .Height = 354.96
    End With


'    SLIDE 8

Set ppslide = ppPres.Slides.Add(2, ppLayoutTitleOnly)

ppslide.Shapes(1).Width = 720
ppslide.Shapes(1).Top = 20
ppslide.Shapes(1).Left = 0
ppslide.Shapes(1).TextFrame.TextRange = "Text"

    With ppslide.Shapes(1).TextFrame
    .TextRange.ParagraphFormat.Alignment = ppAlignCenter
    .TextRange.Font.Size = 28
    .TextRange.Font.Name = tahoma
    .TextRange.Font.Bold = msoTrue
    .TextRange.Font.Color = RGB(0, 0, 205)
    .VerticalAnchor = msoAnchorTop
    End With

ppslide.Shapes.AddTextbox msoTextOrientationHorizontal, 0, 420, 720, 425.52
ppslide.Shapes(2).TextFrame.TextRange = "Text"


    With ppslide.Shapes(2).TextFrame
    .TextRange.ParagraphFormat.Alignment = ppAlignLeft
    .TextRange.ParagraphFormat.Bullet.Character = 8226
    .TextRange.Font.Size = 16
    .TextRange.Font.Name = tahoma
    .VerticalAnchor = msoAnchorTop
    End With

'        Step into Excel to make Chart
    Set rst = Application.CurrentDb.OpenRecordset("qryDB2")
    Set excelapp = CreateObject("excel.application")
    Set excelwkb = excelapp.Workbooks.Add
    Set excelsht = excelwkb.Worksheets.Add

    excelapp.Visible = False


    With excelsht
    .Range("A2").CopyFromRecordset rst
    .Name = "Text"
    .Range("B1").Value = "Items Processed"
    .Range("C1").Value = "Man Hours"
    End With

    excelapp.Charts.Add
    excelapp.ActiveChart.FullSeriesCollection(1).ChartType = xlLine
    excelapp.ActiveChart.FullSeriesCollection(1).AxisGroup = 2
    excelapp.ActiveChart.FullSeriesCollection(2).ChartType = xlColumnClustered
    excelapp.ActiveChart.FullSeriesCollection(2).AxisGroup = 1
    excelapp.ActiveChart.PlotBy = xlColumns
    excelapp.ActiveChart.SetElement (msoElementDataTableWithLegendKeys)
    excelapp.ActiveChart.SetElement (msoElementLegendNone)
    excelapp.ActiveChart.HasTitle = True
    excelapp.ActiveChart.ChartTitle.Text = "Text"
    excelapp.ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
    excelapp.ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Man Hours"
    excelapp.ActiveChart.Axes(xlValue, xlSecondary).HasTitle = True
    excelapp.ActiveChart.Axes(xlValue, xlSecondary).AxisTitle.Characters.Text = "Items Processed"
    excelapp.ActiveChart.Axes(xlValue).MajorGridlines.Delete
    excelapp.ActiveChart.CopyPicture

    excelapp.DisplayAlerts = False
    excelwkb.Close savechanges:=False
    excelapp.Quit


'    Back to Powerpoint
ppslide.Shapes.Paste

    With ppslide.Shapes(3)
    .Width = 618.48
    .Left = 110
    .Top = 60
    .Height = 354.96
    End With

End Sub