Essbase Retrieve All Code
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 15 of 15

Thread: Essbase Retrieve All Code

Hybrid View

  1. #1
    Join Date
    Apr 2011
    Posts
    7

    Essbase Retrieve All Code

    Hello,

    I am trying to create a macro in order to be able to retrieve all sheets in a workbook. Here is a "loop through all" code I used. The problem is that I want to exclude 1 of the sheets from retrieval. How can I modify the following code to be able achieve that? The sheet I would like to exclude from the loop is called "Lookup" oe Sheet 1. Please help. Thanks.

    Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long
    Sub loop_all_sheets()
    Num_Sheets = Application.Sheets.Count
    For y = 1 To Num_Sheets
    Sheets(y).Select
    x = EssMenuVRetrieve
    Sheets().Select
    Next

    End Sub

  2. #2
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,594

    Re: Essbase Retrieve All Code

    You mean the sheet to exclude always is the first one in the Sheets collention? Then why not simply start looping with the first one after that?

    Code:
    For y = 2 To Num_Sheets
    Please use code tags when posting code.

    Ah, and... Welcome to CodeGuru!
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

  3. #3
    Join Date
    Apr 2011
    Posts
    7

    Re: Essbase Retrieve All Code

    Thank you.

    I have tried y = 2 and it does not make a difference. I need like a select case code to exlcude sheet 1 from being retrieved.

    thanks

  4. #4
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,594

    Re: Essbase Retrieve All Code

    Strange. Worked perfectly for me.

    Then maybe something like this?

    Code:
      For y = 1 To Num_Sheets
        If Sheets(y).Name <> "Lookup" Then
          ' ...
        End If
      Next
    I was thrown out of college for cheating on the metaphysics exam; I looked into the soul of the boy sitting next to me.

    This is a snakeskin jacket! And for me it's a symbol of my individuality, and my belief... in personal freedom.

  5. #5
    Join Date
    Apr 2011
    Posts
    7

    Re: Essbase Retrieve All Code

    ok i will try now and let you know. thanks

  6. #6
    Join Date
    Apr 2011
    Posts
    7

    Re: Essbase Retrieve All Code

    I am now getting a compile error Next without For. Here is what my code looks like now, can you point the error?

    Declare Function EssMenuVRetrieve Lib "ESSEXCLN.XLL" () As Long
    Sub loop_all_sheets()
    Num_Sheets = Application.Sheets.Count

    For y = 1 To Num_Sheets
    If Sheets(y).Name <> "Lookup" Then
    Sheets(y).Select
    x = EssMenuVRetrieve
    Sheets().Select
    Next

    End Sub

  7. #7
    DataMiser is online now Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,860

    Re: Essbase Retrieve All Code

    Code:
    If Sheets(y).Name <> "Lookup" or _
    sheets (y).Name <> "Test" Then
    Think about the logic of that statement for a second.

    If the sheet name is Test then it is <> Lookup so the If is true
    If sheet name is Lookup then it is <> Test so again it will be true
    If sheet name is anything else then it will be <> Test And <> Lookup so it will again be true

    I can't tell what you are trying to exclude here btu your code will nto exclude anything. If those 2 sheets are the ones you want to exclude then the proper logic would be.

    Code:
    If Not Sheets(y).Name = "Lookup" And _
    Not sheets (y).Name = "Test" Then
    Always use [code][/code] tags when posting code.

  8. #8
    Join Date
    Nov 2012
    Posts
    2

    Re: Essbase Retrieve All Code

    Thank you! that works now.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center