Question:
Hi,
I have a workbook woth 2 sheets. Every sheet has a BEx query (so 2 queries).
I need, for every column in the first query, to check if it is present in the second query, to generate a list with the NOT PRESENT values (or simply, marking the not present values).
I tried with Searching excel functions by column, but every query hs more that 30.000 rows, so it takes a long time.
I think I have to use an excel macro. I have been lookong for in the forum but my doubt is that I have to execute when the two queries has been refreshed.
How can I know when the tow queries has been refreshed?
Is there any other way to do it?
Any idea of how to reference a query from the other?
I think the code shoud be in this way:
for every row in the first colum of 1st query
look in every row in the first column of 2nd query
mark in first query if not present.
If I put the macro in 1st query, how can I reference the 2nd one?
Thanks
Answer:
Hi
My experience with macros is that the macro is called anytime any query completes. So, in your case it will run twice. I have in the past set the workbook to refresh all queries on opening. In the code itself, I check the contents of a 'dummy' cell in the workbook that will not get overwritten by the query. If this cell has a value, I know the first query has refreshed, and clear the cell. If the cell is blank, then the second query has refreshed. The main body of code is executed, and I put a value back in the dummy cell.
Hope it helps.
Kind regards
Andrew Porter
Answer:
Hi,
I have done this, by using the variable query ID in VBA. If you look at the module SAPBEX
there is the subroutine Sub SAPBEXonRefresh(queryID
The queryid is different for each query. you can put a break point here and you will see that for each workbook the query ID is different so what you can do is that in this module you put an
if queryID = <2nd query> then
execute your code..
end if
cheers..
TC
Answer:
Hi,
I am not sure I know exactly what you want to do. But the only thing I know is that there is an easy way to know when a query was updated for the last time. Use the statistics infocube, with this infocube you can know exactly when the infocub was updated for the last time.
Only one think make sure you update the statistics infocube. it should be a step in your process chains...
hope it helps!