locked
How Do I Populate An Excel Column With The Names Of The Worksheets In The Active Workbook RRS feed

  • Question

  • I'm trying to add a sheet to the active workbook and have one of the columns populated with the names of all of the other worksheets in the same active workbook.  I have a new worksheet being added to the front of the workbook, but the column is not being populated with anything...I'm using VS 2008 and programming with VB.net.  The button that activates this code is part of an Excel 2007 Ribbon AddIn.

    Here is the code I have for trying to populate that column, but it is not working...I get no result at all:

            Dim wSheet2 As Excel.Worksheet
            Dim dws As Excel.Worksheet
            Dim ThisName As String
            Dim HPages As String
            Dim VPages As String
            Dim ThisPages As String
    
    
            Globals.ThisAddIn.Application.ActiveWorkbook.PageCount = 1
            Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets.Select()
    
            ' Now loop thru sheets
            For Each dws In Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets
                dws.Select()
                ThisName = dws.Name
                HPages = dws.HPageBreaks.Count + 1
                VPages = dws.VPageBreaks.Count + 1
                ThisPages = HPages
    
                ' Enter worksheet name on new wroksheet
                wSheet2.Range("B7").Value = ThisName
            Next dws


    • Edited by fdegree Wednesday, April 11, 2012 1:02 AM
    Tuesday, April 10, 2012 9:11 PM

Answers

  • I hope this helps.

     Private Sub Button1_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
            Dim sht As Excel.Worksheet
    
            Dim writeCellSht As Excel.Worksheet
            writeCellSht = Globals.ThisAddIn.Application.ActiveSheet
            Dim i As Integer
            i = 0
    
            For Each sht In Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets
    
                writeCellSht.Range("a1").Offset(i).Value2 = sht.Name
    
                i = i + 1
    
            Next
    
        End Sub


    http://vsto.tistory.com

    • Marked as answer by fdegree Wednesday, April 11, 2012 6:13 PM
    Wednesday, April 11, 2012 7:28 AM

All replies

  • I hope this helps.

     Private Sub Button1_Click(sender As System.Object, e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
            Dim sht As Excel.Worksheet
    
            Dim writeCellSht As Excel.Worksheet
            writeCellSht = Globals.ThisAddIn.Application.ActiveSheet
            Dim i As Integer
            i = 0
    
            For Each sht In Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets
    
                writeCellSht.Range("a1").Offset(i).Value2 = sht.Name
    
                i = i + 1
    
            Next
    
        End Sub


    http://vsto.tistory.com

    • Marked as answer by fdegree Wednesday, April 11, 2012 6:13 PM
    Wednesday, April 11, 2012 7:28 AM
  • Works perfectly....Thank you kindly...I sincerely appreciate this!!!!
    Wednesday, April 11, 2012 6:13 PM