none
Excel VBA / VSTO - How to loop through pages? RRS feed

  • Question

  • In Excel worksheet what is the code to loop through pages? Not worksheets but pages in one worksheet.
    Saturday, August 15, 2015 5:21 AM

Answers

  • I am not so sure about it. Try this one.

    Dim x, y
    For Each x In ActiveSheet.PageSetup.Pages
             y = y + 1
    Next
    MsgBox y

    • Marked as answer by Codename K2 Monday, August 17, 2015 5:17 AM
    Monday, August 17, 2015 12:26 AM
  • In C#,

    int x = 0 ;     
    foreach (dynamic y in Globals.ThisAddIn.Application.ActiveSheet.PageSetup.Pages)
        x ++ ;
    System.Windows.Forms.MessageBox.Show( x.ToString()); 

    Works fine with me.

    I do not use VB.NET.

    Regards






    Monday, August 17, 2015 4:49 AM

All replies

  • What do you mean pages?  There is one page, or sheet.  Do you mean ranges?  What are you trying to do?

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

    Sunday, August 16, 2015 9:26 PM
  • I am not so sure about it. Try this one.

    Dim x, y
    For Each x In ActiveSheet.PageSetup.Pages
             y = y + 1
    Next
    MsgBox y

    • Marked as answer by Codename K2 Monday, August 17, 2015 5:17 AM
    Monday, August 17, 2015 12:26 AM
  • By pages I mean what you get when you click the print preview button. I have added shape objects in every page. I need to loop through each pages and find out how many shapes each page contains.
    Monday, August 17, 2015 4:15 AM
  • Thanks. This works perfectly in Excel VBA. Do you know how to do this in Excel VSTO (VB.NET)? I tried the following code based on your one,

            Dim x, y
            For Each x In Globals.ThisAddIn.Application.ActiveSheet.PageSetup.Pages
                y = y + 1
            Next
            MsgBox(y)

    I get the error message "Unable to cast transparent proxy to type 'System.Collections.IEnumerable'.". Do you know what it is the issue here please?




    • Edited by Codename K2 Monday, August 17, 2015 4:19 AM
    Monday, August 17, 2015 4:19 AM
  • In C#,

    int x = 0 ;     
    foreach (dynamic y in Globals.ThisAddIn.Application.ActiveSheet.PageSetup.Pages)
        x ++ ;
    System.Windows.Forms.MessageBox.Show( x.ToString()); 

    Works fine with me.

    I do not use VB.NET.

    Regards






    Monday, August 17, 2015 4:49 AM
  • Thanks.
    Monday, August 17, 2015 5:17 AM