none
CODE IS FOR LIST OF SHEETS IN EXCEL WORKBOOK AND SELECT THE SHEET BY SELECTION RRS feed

  • Question

  • Hi,

    I am doing one project on vb, I have one excel sheet is there but I need to detect the list of sheets in excel and activate the current sheet for pasting the range value.

    Thanks & Regards

    Vinay Krishna

    Tuesday, June 30, 2015 11:59 AM

Answers

  • Hello,

    Simple example to get access to the active sheet and to list all sheets.

    Option Strict On
    Option Infer On
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    Module OpenWorkSheets1
        Public Sub OpenExcelDemo1(ByVal FileName As String)
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)
    
            xlApp.Visible = False
            xlWorkSheets = xlWorkBook.Sheets
    
            Dim ActiveWorkSheet As Excel.Worksheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
            Console.WriteLine(ActiveWorkSheet.Name)
    
            Runtime.InteropServices.Marshal.FinalReleaseComObject(ActiveWorkSheet)
            ActiveWorkSheet = Nothing
    
            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
                Console.WriteLine("  {0}", xlWorkSheet.Name)
                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
            Next
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheets)
            xlWorkSheets = Nothing
    
            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
    
            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
    
            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
    
        End Sub
    
    End Module
    

    If you like have a look at a library I created in the following article for listing sheets and more

    https://code.msdn.microsoft.com/Working-with-Excel-cdd73a85


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.

    • Proposed as answer by ryguy72 Thursday, July 2, 2015 3:15 PM
    • Marked as answer by L.HlModerator Friday, July 10, 2015 9:16 AM
    Tuesday, June 30, 2015 1:03 PM

All replies

  • There isn't enough information here to answer your question. Are you working in Microsoft Excel or from a Visual Basic .NET application? If the code is for Microsoft Excel then you will want to post to the Excel for Developers forum.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, June 30, 2015 12:15 PM
  • Hello,

    Simple example to get access to the active sheet and to list all sheets.

    Option Strict On
    Option Infer On
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    Module OpenWorkSheets1
        Public Sub OpenExcelDemo1(ByVal FileName As String)
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheet As Excel.Worksheet = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
    
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Open(FileName)
    
            xlApp.Visible = False
            xlWorkSheets = xlWorkBook.Sheets
    
            Dim ActiveWorkSheet As Excel.Worksheet = CType(xlWorkBook.ActiveSheet, Excel.Worksheet)
            Console.WriteLine(ActiveWorkSheet.Name)
    
            Runtime.InteropServices.Marshal.FinalReleaseComObject(ActiveWorkSheet)
            ActiveWorkSheet = Nothing
    
            For x As Integer = 1 To xlWorkSheets.Count
                xlWorkSheet = CType(xlWorkSheets(x), Excel.Worksheet)
                Console.WriteLine("  {0}", xlWorkSheet.Name)
                Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheet)
                xlWorkSheet = Nothing
            Next
    
            xlWorkBook.Close()
            xlApp.UserControl = True
            xlApp.Quit()
    
            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkSheets)
            xlWorkSheets = Nothing
    
            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook)
            xlWorkBook = Nothing
    
            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBooks)
            xlWorkBooks = Nothing
    
            Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp)
            xlApp = Nothing
    
        End Sub
    
    End Module
    

    If you like have a look at a library I created in the following article for listing sheets and more

    https://code.msdn.microsoft.com/Working-with-Excel-cdd73a85


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.

    • Proposed as answer by ryguy72 Thursday, July 2, 2015 3:15 PM
    • Marked as answer by L.HlModerator Friday, July 10, 2015 9:16 AM
    Tuesday, June 30, 2015 1:03 PM
  • Hi Vinay,

    First, please refer to Kevininstructor’s reply.

    Secondly, we could use Workbook.Sheets property to get the sheets object and use Workbook.ActiveSheet property to get current active sheet.

    To mark the sheet activate, we could use Worksheet.Activate method.

    To paste the range, we could use Range.PasteSpecial method.

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 1, 2015 5:31 AM
    Moderator
  • HI ALL ,

    SORRY FOR LATE REPLY

    I AM DOING PROJECT ON VB.NET LOOKING CODE FOR VISUAL BASICS IN VISUAL STUDIO

    Wednesday, July 1, 2015 5:33 AM
  • Hi,

    At last, it uses the excel object model too.

    # _Workbook.Sheets property

    https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._workbook.sheets.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

    # _Worksheet.Activate method

    https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._worksheet.activate.aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

    # Range.PasteSpecial method

    https://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.range.pastespecial.aspx

    Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 1, 2015 6:00 AM
    Moderator
  • HI ALL ,

    SORRY FOR LATE REPLY

    I AM DOING PROJECT ON VB.NET LOOKING CODE FOR VISUAL BASICS IN VISUAL STUDIO

    Hello,

    Did you get a chance to look at the code I provided?


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my webpage under my profile but do not reply to forum questions.

    Tuesday, July 7, 2015 12:35 PM