Avatar of Stephen Roesner
Stephen Roesner
Flag for United States of America asked on

Rename excel tab on the fly using access vba

Set oExcel = New Excel.Application    

Set oWorkBook = oExcel.Workbooks.Open(strfilenameI & "Audit Errors Report_" & fd & ".xlsx")    

Set oWorksheet = oWorkBook.Worksheets("DCs")    

Set qdf = db.QueryDefs("Audit 01B Export")    

Set rs = qdf.OpenRecordset(dbOpenDynaset)    oWorksheet.Range("A2").CopyFromRecordset rs 


this is the code I use to export a table using a query to an excel spreadsheet template to a specific tab name "DCs" .

Is there a way I can name the tab on the fly like naming it a specific company name like "Honeywell"

VBA

Avatar of undefined
Last Comment
Martin Liss

8/22/2022 - Mon
Martin Liss

Activesheet.name = "Honeywell"
ASKER CERTIFIED SOLUTION
Martin Liss

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Stephen Roesner

ASKER
Activesheet.name = "Honeywell"        
oWorksheet.name = "Honeywell"                                  

how would these work into the code I use ?  does it go before I   Set oWorksheet = oWorkBook.Worksheets("DCs")        
I'm not sure how your 2 suggestions would work. Right now I'm using a excel template with a prenamed sheet but If im running thru a table of company names to export that companies data when would I set the Activesheet.name = "Honeywell"  or oWorksheet.name = "Honeywell" within my existing code.?
Martin Liss

I don't do Access so this may not work. I added lines 1 and 2 and modified line 7.

Dim strName As String
strName = InputBox("Please enter the tab name", "Select Tab Name", "DCs")

Set oExcel = New Excel.Application    
Set oWorkBook = oExcel.Workbooks.Open(strfilenameI & "Audit Errors Report_" & fd & ".xlsx")   
 
Set oWorksheet = oWorkBook.Worksheets(strName)    
Set qdf = db.QueryDefs("Audit 01B Export")    
Set rs = qdf.OpenRecordset(dbOpenDynaset)    oWorksheet.Range("A2").CopyFromRecordset rs 

Open in new window

Your help has saved me hundreds of hours of internet surfing.
fblack61
Stephen Roesner

ASKER
Set oExcel = New Excel.Application    
Set oWorkBook = oExcel.Workbooks.Open(strfilenameI & "Audit Errors Report_" & fd & ".xlsx")    
Set oWorksheet = oWorkBook.Worksheets(1)    
oWorksheet.Name = ("New Name")
Set qdf = db.QueryDefs("Audit 01B Export")    
Set rs = qdf.OpenRecordset(dbOpenDynaset)    oWorksheet.Range("A2").CopyFromRecordset rs

This is how it works
Martin Liss

Where does "New Name" come from?