If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register or Login
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.

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.

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

Re: Essbase Retrieve All Code

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.

Re: Essbase Retrieve All Code

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?

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