Solved

Get worksheet names in Excel

Posted on 2003-10-27
6
290 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

840 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