Sending Data to excel for graph (1 Viewer)

[Crash1hd]

OK I have created a button that when pressed sends information from a Query to excel and creates a chart based on that information!

Below is the code and I have a few questions?

Sub Toggle0_Click()
    
'    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Percentages_AllDepartments_By_Round_Query", "C:\Desktop\TestFile.xls", True, "Sheet1"
'    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Percentages_AllDepartments_By_Round_Query", "C:\Desktop\TestFile.xls", True, "Sheet2"

'   this code is not intended to run from Excel
    Dim myChart1 As Object
    Dim mySheet As Object
    Dim myBook As Object
    Dim xlApp As Object

    Set xlApp = CreateObject("Excel.Application")
    With xlApp
        Set myBook = .Workbooks.Add() 'Create
        '.Workbooks.Add ("C:\Desktop\TestFile.xls")
        'Set mySheet = .Workbooks.Open("C:\Desktop\TestFile.xls").Sheets("Sheet1")
        '.Sheets("Sheet1").Name = "Test"
        '.Sheets("Sheet2").Name = "Test1"
        .Sheets("Sheet3").Delete
        .Range("A1").Select
        .ActiveCell.FormulaR1C1 = "Inbound"
        .Range("B1").Select
        .ActiveCell.FormulaR1C1 = "Outbound"
        .Range("C1").Select
        .ActiveCell.FormulaR1C1 = "CCC"
        .Range("D1").Select
        .ActiveCell.FormulaR1C1 = "Data"
        .Range("E1").Select
        .ActiveCell.FormulaR1C1 = "Helpdesk"
        .Range("F1").Select
        .ActiveCell.FormulaR1C1 = "IRO"
        .Range("G1").Select
        .ActiveCell.FormulaR1C1 = "ADT"

        .Range("A2").Select
        .ActiveCell.FormulaR1C1 = "80"
        .Range("B2").Select
        .ActiveCell.FormulaR1C1 = "20"
        .Range("C2").Select
        .ActiveCell.FormulaR1C1 = "70"
        .Range("D2").Select
        .ActiveCell.FormulaR1C1 = "75"
        .Range("E2").Select
        .ActiveCell.FormulaR1C1 = "68"
        .Range("F2").Select
        .ActiveCell.FormulaR1C1 = "10"
        .Range("G2").Select
        .ActiveCell.FormulaR1C1 = "90"

        .Rows("1:2").Select
        .Selection.Copy
        .Sheets("Sheet2").Select
        .Rows("1:2").Select
        .ActiveSheet.Paste
        .Sheets("Sheet1").Select
        .Range("A1:G2").Select
        .Application.CutCopyMode = False
        Set myChart1 = .Charts.Add(Before:=.ActiveWorkbook.Sheets(1))
        .Visible = True 'not needed
        .Sheets(myChart1.Name).Name = "MyChart" ' set a name for the new chartsheet
        
        With myChart1
          .ChartType = 51 'xlColumnClustered
         '.ChartType = 54 'xl3DColumnClustered
        .SeriesCollection.NewSeries
        .SeriesCollection(2).Values = "=Sheet2!R2C1:R2C7"
        
         .Location Where:=1 'xlLocationAsNewSheet :this not needed, default
            .HasTitle = True
           .ChartTitle.Characters.Text = "Top Title" 'add title
          With .Axes(1, 1) 'xlCategory, xlPrimary
             .HasTitle = True
                .AxisTitle.Characters.Text = "Bottem Title" 'add X-Axis Label
         End With
            With .Axes(2, 1) 'xlValue, xlPrimary
               .HasTitle = True
              .AxisTitle.Characters.Text = "Side Title" ' add Y-Axis Label
            End With
        End With
    'clean-up
        .Quit
        'Set xlApp = Nothing: Set myChart1 = Nothing: Set mySheet = Nothing
    End With
   End Sub

In here is 2 ways of doing the same thing one is creating an excel file and then opening that file and creating the chart the other (uncomplete) is creating an excel file(without saving it) and then inputing the same data into the sheets and creating the chart

on with the questions

1. without useing the
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Percentages_AllDepartments_By_Round_Query", "C:\Desktop\TestFile.xls", True, "Sheet1"
to export is there a way to send the data inside the
.Range("A2").Select
.ActiveCell.FormulaR1C1 = "80"
so instead of it being static it actually puts the right data in it?

2. is there a way of dynamically changing the names of the sheets sheet1 and sheet2 to corrisopond say when the button is pressed in method 1 (the saved excel file method) it asks me which querys I want based on a number so I would say something like 2 and 3 that way when it creates the excel file it calls them sheet2 and sheet3 or even better round2 and round3

3. How do I get it to ask me to print or save once opened and on the save rename it so that it is not called book1 or what ever it says? so that its named something like QA Rounds 2 & 3 (Again if that could be dynamic as well) to the same numbers above