Solved

Get worksheet names in Excel

Posted on 2003-10-27
6
283 Views
Last Modified: 2010-12-08
What code would you use to get a list of every worksheet name in an Excel Speadsheet?
0
Comment
Question by:LorangerG
  • 4
6 Comments
 
LVL 44

Accepted Solution

by:
bruintje earned 125 total points
ID: 9626891
in vba you could try something like

Option Explicit

Sub ListNames()
Dim sh As Worksheet
Dim i As Integer
i = 1
For Each sh In ThisWorkbook.Sheets
  Sheets(1).Range("A" & i) = sh.Name
  i = i + 1
Next sh
End Sub
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 9626893
Hi LorangerG,

Dim shtToList As string
Dim strSheets As String
For Each shtToList In Worksheets
  strSheets = strSheets & shtToList.Name & vbLf
Next
Msgbox strSheets

Should do it.

Tim Cottee MCSD, MCDBA, CPIM
Brainbench MVP for Visual Basic
http://www.brainbench.com
0
 
LVL 44

Expert Comment

by:bruintje
ID: 9626899
which will list the names on the first sheet in your workbook starting in cell A1 till the number of sheets in your workbook
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 44

Expert Comment

by:bruintje
ID: 9626900
which will list the names on the first sheet in your workbook starting in cell A1 till the number of sheets in your workbook
0
 
LVL 44

Expert Comment

by:bruintje
ID: 9626922
in a function you could be passing in the workbook object and retrieve an array of names

Public Function GetWshNames(ByVal myxl As Object) As String()
Dim sh As Object
Dim sOut() As String
Dim i As Integer

ReDim sOut(myxl.Workbooks(1).Sheets.Count)

i = 0
For Each sh In myxl.Workbooks(1).Sheets
  sOut(i) = sh.Name
  i = i + 1
Next sh

GetWshNames = sOut

End Function
0
 

Author Comment

by:LorangerG
ID: 9627006
Man you guys are quick! Both answers were great bruintje was first.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 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

13 Experts available now in Live!

Get 1:1 Help Now