Solved Saving Query to Excel and Naming Worksheet (1 Viewer)

miacino

Registered User.
Local time
Yesterday, 17:52
Joined
Jun 5, 2007
Messages
106
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

Enthusiastic Amateur
Local time
Today, 00:52
Joined
Sep 21, 2011
Messages
14,048
I do not think you should have the . before Name?
 

miacino

Registered User.
Local time
Yesterday, 17:52
Joined
Jun 5, 2007
Messages
106
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

Enthusiastic Amateur
Local time
Today, 00:52
Joined
Sep 21, 2011
Messages
14,048
So assign attribution control to strSheetName and use that?
 

miacino

Registered User.
Local time
Yesterday, 17:52
Joined
Jun 5, 2007
Messages
106
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!
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 00:52
Joined
Jul 9, 2003
Messages
16,245
I answered a somewhat similar question a couple of years ago for anAccess World Forum member @avalve ...

I made a blog about it on my website here:-


I modified some code by betab developments and was able to import thousands of records into around 200 to 300 separate Excel sheets!

All the information you need to duplicate my solution is in the blog.
 

miacino

Registered User.
Local time
Yesterday, 17:52
Joined
Jun 5, 2007
Messages
106
Thank you both! That did it!
Code:
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

Enthusiastic Amateur
Local time
Today, 00:52
Joined
Sep 21, 2011
Messages
14,048
Last excelapp.workbooks.Close not required?
 

Users who are viewing this thread

Top Bottom