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!!
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!!