Replies

Armando,

Take a look at the attached file. You will need to open it up and hit [ALT] +[F11] to view it in the IDE. There is some code in the ThisWorkbook and mod_menu modules but all the essbase code is contained within the module mod_essbase.

To use this on your workbook, you will need this file open, and then make your spreadsheet the active workbook. Then you can call the menu item that will refresh the retrieval zones in your workbook. However, you will need to make some changes first.

The changes you will need to make are to the connection line and the retrieval line in the macro “Refresh_Sheet”, and the Sheet Names in the macro “Refresh_Workbook”. From looking at the code you had in the file you sent me, I don’t think you will have a problem with this, although I was confused by your range: I’d check it shouldn’t be “A6:Q37” instead of “B6:Q37”.

FWIW, the approach you were taking was probably confusing you because I have been talking throughout this thread about using vba, whilst you have been trying to convert my advice into the old version 4 macro code. This still works if you know what you are doing but I don’t – I did inherit some essbase macro’s that used excel4 macro code, and may be able to work through what they do, but anything new I have written is in vba: I would strongly recommend that you adopt the same approach.
Also FWIW, I mentioned earlier that I was using essbase 5, and not 6 as you are. I have since upgraded to version 6 and I have tested the code in the attached file since.

To answer two of your earlier questions,

a) the functions such as “EssVConnect” are not native to Excel, and so need to be declared to Excel by a call to the library that they are contained in, detailing the parameters that need to be passed. This is the declaration code at the top of the essbase module.

the code that contains the variables “intchkgood” etc in a previous post of mine is just a version of the code in the attached file, with error checking included. When you call one of the essbase functions, not only does it perform the requested action, but it also returns a value indicating whether the call was successful or not. This enables you to write code comparing the number of retrievals performed against the number of succesful retrievals. If these two do not match then you can generate some kind of error report that all is not well, allowing you to fix the problem before sending inaccurate data to your boss/colleagues.

If you have any more problems or questions, feel free to ask!

Armando,

Take a look at the attached file. You will need to open it up and hit [ALT] +[F11] to view it in the IDE. There is some code in the ThisWorkbook and mod_menu modules but all the essbase code is contained within the module mod_essbase.

To use this on your workbook, you will need this file open, and then make your spreadsheet the active workbook. Then you can call the menu item that will refresh the retrieval zones in your workbook. However, you will need to make some changes first.

The changes you will need to make are to the connection line and the retrieval line in the macro “Refresh_Sheet”, and the Sheet Names in the macro “Refresh_Workbook”. From looking at the code you had in the file you sent me, I don’t think you will have a problem with this, although I was confused by your range: I’d check it shouldn’t be “A6:Q37” instead of “B6:Q37”.

FWIW, the approach you were taking was probably confusing you because I have been talking throughout this thread about using vba, whilst you have been trying to convert my advice into the old version 4 macro code. This still works if you know what you are doing but I don’t – I did inherit some essbase macro’s that used excel4 macro code, and may be able to work through what they do, but anything new I have written is in vba: I would strongly recommend that you adopt the same approach.
Also FWIW, I mentioned earlier that I was using essbase 5, and not 6 as you are. I have since upgraded to version 6 and I have tested the code in the attached file since.

To answer two of your earlier questions,

a) the functions such as “EssVConnect” are not native to Excel, and so need to be declared to Excel by a call to the library that they are contained in, detailing the parameters that need to be passed. This is the declaration code at the top of the essbase module.

the code that contains the variables “intchkgood” etc in a previous post of mine is just a version of the code in the attached file, with error checking included. When you call one of the essbase functions, not only does it perform the requested action, but it also returns a value indicating whether the call was successful or not. This enables you to write code comparing the number of retrievals performed against the number of succesful retrievals. If these two do not match then you can generate some kind of error report that all is not well, allowing you to fix the problem before sending inaccurate data to your boss/colleagues.

If you have any more problems or questions, feel free to ask!

part of the answer may depend on precisely how your retrieval zones are set out on the individual worksheets. Is it possible to post an example? overwrite any sensitve data within the retrieval zone with nonsense values – what I’m really looking for is whether you’ve got formulae in amoungst the retrieval zones and their general layout.

part of the answer may depend on precisely how your retrieval zones are set out on the individual worksheets. Is it possible to post an example? overwrite any sensitve data within the retrieval zone with nonsense values – what I’m really looking for is whether you’ve got formulae in amoungst the retrieval zones and their general layout.

My apologies Walter – looking at Steve’s response and the post He links to, I believe I may have misinterpreted your question !

My apologies Walter – looking at Steve’s response and the post He links to, I believe I may have misinterpreted your question !

in the page-setup dialog, where you see the list of default options, you should also see a “Custom” Button – click on this and you’ll be able to add your own – sample attached (from 2K but from memory it is the same)

in the page-setup dialog, where you see the list of default options, you should also see a “Custom” Button – click on this and you’ll be able to add your own – sample attached (from 2K but from memory it is the same)

On the subject of modeless forms, try stephen bullen and look for the download modelessform.zip – but take note of the warning. I haven’t tried it so can’t offer any more help.

Brooke

Edited Mar 13th 2004 to update link

(Edited by HansV to update link to Excel MVP site)

Just wondering how you were getting on with this – I’ve only just started to play with it again myself. The link Sammy gives is good but you need to go in one link further to the http://www.cpearson.com/excel/vbemenus.htm%5B/url%5D page. I know this is all in excel but hey, it should work for you. An additional link on this subject is http://www.bmsltd.ie/O2000/Default.htm%5B/url%5D again in excel, you’re looking for the VBEtools2000 download – it should be translatable to word. Neither of these is actually what I vaguely remembered seeing before – I still haven’t rediscovered that – but they are both using the same method of class modules. The second link gives an alternative method of closing all the code panes different to the one I supplied as well as a few other interesting things

(Edited by HansV to update link to Excel MVP site)

I believe the only way to do this is programattically and to use a class module to trap the click events of the custom buttons. I just use Stephen Bullens VBEtools2000 as is, which is available from http://www.BMSLtd.ie[/url%5D. This is not locked so you can use it as a tuition for how to roll your own.

I think that what you need to do is double click on the row field that you want to sort by – probably the left-most one (in my example it is the first visible column, row 15, “sales region”), which will give you the PivotTable Field Dialog – click on the advanced button and you’ll get the PivotTable Field Advanced Options Dialog. Under AutoSort options, change it from “manual” to either descending or ascending and then select the datafield you want to sort by.

I think that what you need to do is double click on the row field that you want to sort by – probably the left-most one (in my example it is the first visible column, row 15, “sales region”), which will give you the PivotTable Field Dialog – click on the advanced button and you’ll get the PivotTable Field Advanced Options Dialog. Under AutoSort options, change it from “manual” to either descending or ascending and then select the datafield you want to sort by.

Does INT(ROW()/2) do what you want?

Does INT(ROW()/2) do what you want?