Solved Saving Query to Excel and Naming Worksheet

[miacino]

I'm trying to save a query to excel, which I am able to do just fine.
I'd like to assign a new tab (worksheet) to the excel sheet. Got that as well.
Where I'm struggling is trying to NAME the excel tab/worksheet the name of a field on the form I am running the code on.

So if in my form the field [attribution month] is Dec-2021, that is what I want the tab name to be.
------
Private Sub Command146_Click()
Set dbs = CurrentDb
Set rsQuery = dbs.OpenRecordset("Totals by Practice")
Set excelapp = CreateObject("Excel.application", "")

excelapp.Visible = True
Set targetworkbook = excelapp.workbooks.Open("G:\\Data\Reporting\Member Attribution\2021Attribution.xlsx")
targetworkbook.Worksheets.Add.Name = [attribution month]
targetworkbook.Worksheets([attribution month]).Range("A1").CopyFromRecordset rsQuery

targetworkbook.Save
targetworkbook.Close

excelapp.workbooks.Close
excelapp.Quit

excelapp.workbooks.Close
End Sub
------
Any help appreciated!!

 

[Gasman]

I do not think you should have the . before Name?

 

[miacino]

I do not think you should have the . before Name?

That seems to be OK - it works if I put in a name, i.e,
targetworkbook.Worksheets.Add.Name = "New Sheet Name"

It is not liking
targetworkbook.Worksheets.Add.Name = [attribution month]

 

[Gasman]

So assign attribution control to strSheetName and use that?

 

[miacino]

So assign attribution control to strSheetName and use that?

I'm sorry - this is a little over my head. Can you help with what you mean? Or give me an example? Thanks Gasman!

 

[miacino]

Thank you both! That did it!

Private Sub Command146_Click()
    Set dbs = CurrentDb
    Set rsQuery = dbs.OpenRecordset("Totals by Practice")
    Set excelapp = CreateObject("Excel.application", "")
    Dim strSheetName As String
    strSheetName = Format([attribution month], "mmm-yyyy")
    
    excelapp.Visible = True
    Set targetworkbook = excelapp.workbooks.Open("G:\Reporting\Member Attribution\2021 Attribution.xlsx")
    targetworkbook.Worksheets.Add.Name = strSheetName
    targetworkbook.Worksheets(strSheetName).Range("A1").CopyFromRecordset rsQuery
    
    targetworkbook.Save
    targetworkbook.Close

    excelapp.workbooks.Close
    excelapp.Quit
    
    excelapp.workbooks.Close
End Sub

 

[Gasman]

Last excelapp.workbooks.Close not required?

 

[miacino]

Last excelapp.workbooks.Close not required?

Yes, good catch. You are correct