CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 15 of 15
  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,675

    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,675

    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
    Join Date
    Apr 2011
    Posts
    7

    Re: Essbase Retrieve All Code

    sorry this is what the code looks like now and Im getting a run-time error 1004: Method 'Select' of Object 'sheets' failed.

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

    For y = 3 To Num_Sheets
    If Sheets(y).Name <> "lookup" Then
    Sheets(y).Select
    x = EssMenuVRetrieve
    Sheets().Select
    End If
    Next

    End Sub

  8. #8
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    Re: Essbase Retrieve All Code

    Sorry, I can't reproduce the error here. Can you post some code that reproduces the error without using that .xll file?

    Please remember to use code tags.
    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.

  9. #9
    Join Date
    Apr 2011
    Posts
    7

    Re: Essbase Retrieve All Code

    I'm note sure what you're telling me to do. Bare with me since I'm not a vba user. This is my first attempt to write a macro. Basically, I need to specify in the code to exclude a sheet from being part of the loop. so far this is what I have as the code:

    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
    x = EssMenuVRetrieve
    Sheets(y).Select
    End If
    Next
    End Sub

    I want the macro to skip sheet "lookup" only and perform the function on the remaining sheets. Will I need to write something after "Then"? As of now using the above code, it is not skipping sheet "lookup". It still goes through all sheets and performs essbase retrieval.

  10. #10
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Essbase Retrieve All Code

    This is VB6 code, but you should get the idea:
    Code:
    Option Explicit
    
    ' Add a reference to Excel Object Library x.x
    
    Private Sub Form_Load()
      Call SheetExists("test.xls")
    End Sub
    
    Sub SheetExists(nameX As String)
    Dim xls As New Excel.Application
    Dim wk As New Excel.Workbook
    Dim i As Integer
    
    Set wk = xls.Workbooks.Open(App.Path & "\test.xls")
    
    With wk.Worksheets
    For i = 1 To .Count
       If .Item(i).Name = nameX Then
         MsgBox "Found"
         Exit For
       End If
    Next
    End With
    Set wk = Nothing
    xls.Quit
    Set xls = Nothing
    End Sub
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  11. #11
    Join Date
    Jun 2010
    Location
    Germany
    Posts
    2,675

    Re: Essbase Retrieve All Code

    Quote Originally Posted by Noorie007 View Post
    Will I need to write something after "Then"?
    No, not when using it the way we do here, i.e. in conjunction with End If. In that case the instructions dependent on the If condition are those between these two keywords.

    This aspect of the code structure is emphasized by the indentation of the dependent statements but that is only preserved in forum posts when using code tags. And that's the most important reason for using them. Please do that too when posting further code. See the code snippet posted by me below for example.

    As of now using the above code, it is not skipping sheet "lookup". It still goes through all sheets and performs essbase retrieval.
    That's most likely because the sheet is named "Lookup", not "lookup". Note that string comparisons are case-sensitive in VBA (and VB).

    I copied your code from your most recent post and changed these three things:
    • Removed the Essbase stuff because I simply don't have that.
    • Changed the casing of the "Lookup" string literal to which the sheet names are compared.
    • Added a MsgBox instruction for testing.

    This is the resulting code which works perfectly for me:

    Code:
    Sub loop_all_sheets()
      Num_Sheets = Application.Sheets.Count
      For y = 1 To Num_Sheets
        If Sheets(y).Name <> "Lookup" Then
          Sheets(y).Select
          MsgBox "Selected sheet: " & ActiveSheet.Name  ' Just for diagnostics
        End If
      Next
    End Sub
    Ah, and... Though I don't really know what that actually does I think it would be more reasonable to make the call to EssMenuVRetrieve after selecting the sheet, not before selecting it.
    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.

  12. #12
    Join Date
    Apr 2011
    Posts
    7

    Re: Essbase Retrieve All Code

    Thanks A lot Eri and Dglienna.

  13. #13
    Join Date
    Nov 2012
    Posts
    2

    Re: Essbase Retrieve All Code

    Hi - I am trying to do the same but instead of excluding 1 sheet, I want to exclude 2 or more specific streets. I edited the code to the below but it still loops through ALL the sheets. When I have it just a single sheet being excluded, it works but not with the below. What is the proper way to write in such a code?


    Sub loop_all_sheets()
    Num_Sheets = Application.Sheets.Count
    For y = 1 To Num_Sheets
    If Sheets(y).Name <> "Lookup" or _
    sheets (y).Name <> "Test" Then
    Sheets(y).Select
    MsgBox "Selected sheet: " & ActiveSheet.Name ' Just for diagnostics
    End If
    Next
    End Sub

  14. #14
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  15. #15
    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
  •  





Click Here to Expand Forum to Full Width

Featured