Solved

MS Access VBA to change Worksheet Tab name

Posted on 2013-05-29
10
2,663 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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 33

Expert Comment

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

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 33

Expert Comment

by:Norie
Comment Utility
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
Comment Utility
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
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
Excellent the revised codes works well and all of the worksheet names have been changed - Thank you!
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now