Find queries and Macro in a workbook

[jennychen1]

All:

I am given a workbook in 3.5 version developed earlier by someone else. I have no document about it and was told that there may be multiple queries and also macro involved in this workbook. How can I find out which queries are used and in which cell area in the workbook? Also, how can I find out if Macro is used for it?

Help need urgently. Thank you very much!

Jenny

[jennychen1]

Thanks!

[Former Member]

Hi Jenny,

Here is how you can tell how many Queries you have in your Workbook.

When Excel is Open do

1) ALT + F11. This will open the VBA Project of the Workbook .

2) Under Microsoft Excel Objects, you will see a List of all the Sheets in that Workbook.

3) There Should be a Sheet name ( SAPBEXqueries) Click on it If you don't see the Properties Tab then Hit F4 this will open the Properties Pane.

4) The Last Property on the Page is Visible it will be set at 2 - xlveryhidden. Change it to -1 - xlVisible. ( Remember to change it back to 2 once you are done).

This will now show you the Sheet where BEX maintains all the Queries that are part of the the Workbook

5) Cell A2 will tell you number of Queries in the workbook and Column F has the Queries in the workbook.

This will also be a good place to make any sheet that is Hidden Visible so that you can check where the Queries are.

6) In the Same Window under Modules You will be able to see any macros that have been written for this workbook.

By default there will be atleast one Module SAPBEX.

If you double click on any module you will be able to see the code.

Hope this helps.

Datta.

[Former Member]

Hi Jenny

To see the queries, select a cell outside the Results Area - any of the 'usual' Excel cells.

Refresh the query, it asks if you want to refresh ALL queries, say yes.

If there are any hidden queries, or ones that weren't removed properly, you will be prompted for a cell to put them in. Provide a suitable cell, and run the query, then you will see what they are, and you can remove them properly if they aren't needed.

For your info, the way NOT to delete a query from a workbook is to delete the sheet!!!! Use the BEx Tools -> Remove Query.

Hope that helps.... points?

Patrick

[Former Member]

Hi,

To see the macro goto>tool>macro-->edi macro.

Thanks,

Ashok