Solved

MS Access VBA to change Worksheet Tab name

Posted on 2013-05-29
10
2,855 Views
Last Modified: 2013-05-30
Hello Experts,

I have a MS Excel workbook with 5 worksheets and I need to change the Worksheet Tab Names using MS Access VBA.  Below displays the 5 worksheets and my VBA attempt.  This code displays "Object Required" at the statement,  "wSheet.Name = rs!SSTabOut".  Inside MS Access is a table (TblReportsOrder) with the correct Tab Names.

Can you please supply the correct syntax as I was opening the workbook and reading in the MS Access Table?  I feel I need to link the two...

ID      SSName                                                            SSTab                  SSTabOut
1      S:\2013 reports\cfhc ab other 0313.xlsm      Output 1 (313)      CASCI
2      S:\2013 reports\cfhc ab other 0313.xlsm      Output 1 (312)      NCIA
3      S:\2013 reports\cabc nr other 0313.xlsm      Output 1 (320)      NCAS
4      S:\2013 reports\abhc nr other 0313.xlsmOutput 1 (102)      Willse
5      S:\2013 reports\BC Pre_Ab.xlsm      Output 1 (121)      First

VBA:
Private Sub Test222_Click()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim varCentreFooter As String
Dim rs As Recordset
Dim SSTab As String
Dim SSTabOut As String
Dim shtName As String
Dim ws As Worksheet
Set xlApp = CreateObject("Excel.Application")

Set rs = CurrentDb.OpenRecordset("TblReportsOrder")
rs.MoveFirst

xlApp.DisplayAlerts = False
'Set reference to Workbook object
xlApp.EnableEvents = False
Set xlBook = xlApp.Workbooks.Open("H:\PDF\Master.xls")   'Open the workbook

   For Each ws In xlBook.Worksheets
      shtName = rs!SSTab
      shtTabName = rs!SSTabOut
      wSheet.Name = rs!SSTabOut
   Next ws


    xlBook.Save
    xlBook.Close
    xlApp.Quit
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
   
MsgBox "Worksheet Tab Names have been Updated!"

End Sub
0
Comment
Question by:CFMI
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39205801
what are the names of the Sheets in the file  "Master.xls"?

which Sheet you want to change the name and what name are you going to use based from the recordset you opened?

i.e..  "Sheet1"  will be "CASCI" or "NCIA" or whatever???
0
 
LVL 1

Author Comment

by:CFMI
ID: 39205841
The sheet names in the workbook are identical to the names listed in the MS Access table (TblReportsOrder) using the field named, "SSTab" and the field to rename it to is "SSTabOut".  For example, Sheet 1 is, "Output 1 (313)" and I want to rename it to "CASCI".
0
 
LVL 16

Expert Comment

by:Calvin Brine
ID: 39205855
Not sure what you are trying to do here?  I figure it's just your testing.

   For Each ws In xlBook.Worksheets
      shtName = rs!SSTab
      shtTabName = rs!SSTabOut
      wSheet.Name = rs!SSTabOut
   Next ws

change it to this.
   For Each ws In xlBook.Worksheets
      ws.name = rs!SSTabOut
   Next ws

Open in new window

HTH
Cal
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 33

Expert Comment

by:Norie
ID: 39205857
Perhaps this.
Do Until rs.EOF
    xlBook.Worksheets(rs!SSTab).Name= rs!SSTabOut
    rs.MoveNext
Loop

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39205878
try this code, copy and paste


Private Sub Test222_Click()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim varCentreFooter As String
Dim rs As Recordset
Dim SSTab As String
Dim SSTabOut As String
Dim shtName As String
Dim ws As Worksheet


Set rs = CurrentDb.OpenRecordset("TblReportsOrder")
rs.MoveFirst


Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
'Set reference to Workbook object
xlApp.EnableEvents = False
    xlApp.Workbooks.Open("H:\PDF\Master.xls")   'Open the workbook
      
Do until rs.eof
    with xlApp
         .Worksheets(rs!SSTab).Select
         .Worksheets(rs!SSTab).Name= rs!SSTabOut
    end with
   rs.movenext
Loop
    xlApp.Activeworkbook.save
      xlApp.quit
    set xlApp = Nothing
   
MsgBox "Worksheet Tab Names have been Updated!"

End Sub
0
 
LVL 33

Expert Comment

by:Norie
ID: 39205942
The code I posted would go right after this.
Set xlBook = xlApp.Workbooks.Open("H:\PDF\Master.xls")  

Open in new window


You would also remove this.
   For Each ws In xlBook.Worksheets
      shtName = rs!SSTab
      shtTabName = rs!SSTabOut
      wSheet.Name = rs!SSTabOut
   Next ws

Open in new window

0
 
LVL 1

Author Comment

by:CFMI
ID: 39207332
Good Morning,

At first, I received Type Mismatch then I updated the code and now I am receiving, "Subscript out of Range" and debugging points to ".Worksheets(shtNameTab).Select".  Below displays the current code:

Private Sub Test222_Click()
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim varCentreFooter As String
Dim rs As Recordset
Dim SSTab As String
Dim SSTabOut As String
Dim shtName As String
Dim shtNameTab As String
Dim ws As Worksheet


Set rs = CurrentDb.OpenRecordset("TblReportsOrder")
rs.MoveFirst


Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
'Set reference to Workbook object
xlApp.EnableEvents = False
    xlApp.Workbooks.Open ("H:\PDF\Master.xls")  'Open the workbook
   
Set xlBook = xlApp.Workbooks.Open("H:\PDF\Master.xls")
     
Do Until rs.EOF
    With xlApp
        shtNameTab = rs!SSTab
         .Worksheets(shtNameTab).Select
         .Worksheets(shtNameTab).Name = rs!SSTabOut
    End With
   rs.MoveNext
Loop
    xlApp.ActiveWorkbook.Save
      xlApp.Quit
    Set xlApp = Nothing
   
MsgBox "Worksheet Tab Names have been Updated!"


End Sub
0
 
LVL 33

Expert Comment

by:Norie
ID: 39207347
You don't need that line of code.

Do you definitely have worksheets with the names in SSTab in the workbook you are opening?

Are there any other workbooks open?

Did you try the code I posted?

Here's the original code with it added.
Private Sub Test222_Click()
Dim xlApp As Object
Dim xlSheet As Object
Dim rs As Recordset
Dim ws As Worksheet

    Set xlApp = CreateObject("Excel.Application")

    Set rs = CurrentDb.OpenRecordset("TblReportsOrder")
    rs.MoveFirst

    xlApp.DisplayAlerts = False
    xlApp.EnableEvents = False
    
    Set xlBook = xlApp.Workbooks.Open("H:\PDF\Master.xls")   'Open the workbook

    Do Until rs.EOF
        xlBook.Worksheets(rs!SSTab).Name = rs!SSTabOut
        rs.MoveNext
    Loop

    xlBook.Close True
    xlApp.Quit
    Set xlSheet = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing

    MsgBox "Worksheet Tab Names have been Updated!"

End Sub

Open in new window

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39207564
you don't need this line in my codes

Set xlBook = xlApp.Workbooks.Open("H:\PDF\Master.xls")  


your error  of
"Subscript out of Range" and debugging points to ".Worksheets(shtNameTab).Select"

means, the "shtNameTab" does not exists in the workbook



use this revised codes


Private Sub Test222_Click()
Dim xlApp As Object
Dim varCentreFooter As String
Dim rs As Recordset

Dim j as integer

Set rs = CurrentDb.OpenRecordset("TblReportsOrder")
rs.MoveFirst


Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
'Set reference to Workbook object

    xlApp.Workbooks.Open ("H:\PDF\Master.xls")  'Open the workbook
        
Do Until rs.EOF
    With xlApp
		For j =1 to .Worksheets.Count
		  if .Worksheets(j).name=  rs!SSTab then
         
                          .Worksheets(j).Name = rs!SSTabOut
			 exit for
		  end if
	   next 
    End With
   rs.MoveNext
Loop
    xlApp.ActiveWorkbook.Save
      xlApp.Quit
    Set xlApp = Nothing
   
MsgBox "Worksheet Tab Names have been Updated!"


End Sub 

Open in new window

0
 
LVL 1

Author Closing Comment

by:CFMI
ID: 39207754
Excellent the revised codes works well and all of the worksheet names have been changed - Thank you!
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question