-
April 29th, 2011, 03:53 PM
#1
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
-
April 29th, 2011, 05:19 PM
#2
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.
-
May 2nd, 2011, 08:43 AM
#3
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
-
May 2nd, 2011, 08:59 AM
#4
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.
-
May 2nd, 2011, 09:11 AM
#5
Re: Essbase Retrieve All Code
ok i will try now and let you know. thanks
-
May 2nd, 2011, 09:14 AM
#6
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
-
May 2nd, 2011, 09:21 AM
#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
-
May 2nd, 2011, 10:26 AM
#8
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.
-
May 2nd, 2011, 10:45 AM
#9
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.
-
May 2nd, 2011, 12:46 PM
#10
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
-
May 2nd, 2011, 12:48 PM
#11
Re: Essbase Retrieve All Code
Originally Posted by Noorie007
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.
-
May 2nd, 2011, 03:10 PM
#12
Re: Essbase Retrieve All Code
Thanks A lot Eri and Dglienna.
-
November 26th, 2012, 02:41 PM
#13
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
-
November 26th, 2012, 05:41 PM
#14
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.
-
November 26th, 2012, 06:06 PM
#15
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|