rename tab in excel workbook

  • Thread starter Thread starter rename tab in excel workbook
  • Start date Start date
R

rename tab in excel workbook

i have written code to export 3 excel workbooks in access. what is the syntax
to rename each of the sheets(1) to specific names? do not find anything in
the library that would indicate. thanks much for your help
 
See how this works for ya:

Option Explicit
Option Compare Database
Private objExcel As Excel.Application
Private xlWB As Excel.Workbook
Private xlWS As Excel.Worksheet

Private Sub Command0_Click()

' Use this to make sure your variables are defined
' One way to be able to use these objects throughout the Module is to
Declare them here, and not in a Sub

Dim strFile As String
strFile = "C:\Documents and Settings\Excel\Desktop\Book1.xls"

' Opens Excel and makes it Visible
Set objExcel = New Excel.Application
objExcel.Visible = True
' Opens up a Workbook
Set xlWB = objExcel.Workbooks.Open(strFile)
' Sets the Workseet to the last active sheet - Better to use the commented
version and use the name of the sheet.
Set xlWS = xlWB.ActiveSheet
' Set xlWS = xlWB("Sheet1")

With xlWS ' You are now working with the Named file and the named worksheet
' Your Excel code begins here
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Ryan1"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Ryan2"
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Ryan3"

End With

' Close and Cleanup
xlWB.Save
xlWB.Close

End Sub
 
You should experiemnt with this concept as well:
Worksheets(1).Activate
Worksheets(1).Name = "Ryan1"
Worksheets(2).Activate
Worksheets(2).Name = "Ryan1"
Worksheets(3).Activate
Worksheets(3).Name = "Ryan1"

I didn't try it, but I think that would work too...
 
Back
Top