Extract All worksheet names in workbook

  • Thread starter Thread starter Wendy
  • Start date Start date
W

Wendy

Hi...

How do you display all worksheet names in a workbook in a listview???

Thanks

wendy
 
I'd do it like this:

Sub aa()
On Error Resume Next
Err = 0
CommandBars("Workbook Tabs").Controls("More sheets...").Execute
If Err <> 0 Then CommandBars("Workbook Tabs").ShowPopup
End Sub
 
Hello,

I had to do something similar: Writting all names in a text files with
some formating

The principle is to go thru the Names collection, and to filter special
cases like
- Invisible (system) names
- Formulas
..

Here is the main loop:

dim Nom as Name

For Each Nom In ActiveWbk.Names


'Filtre les parametres systemes (invisibles)
If Nom.Visible Then

'Detection des erreurs
'Cas ou le nom pointe dans le vide
If InStr(Nom.value, "#REF!") Then
reponse = MsgBox(" ERROR The name:[" & Nom.Name & "] has
lost its reference, Delete ?", vbYesNo)
If reponse = vbYes Then
Nom.Delete
End If
Else
Set PlageNom = Nothing
Set PlageNom = Nom.RefersToRange


'Filtre les formules, ainsi que les Noms sur plusieurs
cellules,
If Not PlageNom Is Nothing And Not IsArray(PlageNom)
Then

' Filtre le cas ou une cellule est en erreur de par sa
formule
If PlageNom.Text = "#VALEUR!" Then
MsgBox (" ERROR :" & Nom.Name & "/" & Nom.RefersTo
& ": Reference missing in formula")
Else

NbNoms = NbNoms + 1

Var_pro = Nom.Name & " = " & var_num

End If

Var_pro = StrConv(Var_pro, vbUpperCase)
liste_var = liste_var & Chr(car_sep) & Var_pro


' Convertit tout en majuscules
fichier.WriteLine (Var_pro)
End If
Else
FormulesNommees = True
End If

End If

End If

Next Nom
 
Back
Top