I have to create a report using an excel file as source with dat say as follows
C1 C2 C3
India Assam Mumbai
India Assam Pune
India Goa Surat
England Kerala Panjim
England Kerala Thane
And in that I need to create prompts in the following manner.
The report should first prompt me for C1, if I select India then it should
prompt me for Assam or Goa and upon selection of one of these it
should show the resp. data.,i.e, if I select India and then Assam it should
show 2 rows in the report
C1 C2 C3
India Assam Mumbai
India Assam Pune
Is it possible to do so using a VB macro,if so I would be grateful if u could send me such a macro.I hope somebody could help me out.
Hi susan,
Thanks for your help.The drill option does work but my client wants prompts to be incorporated , that is why I asked if maybe the same can be done using vb or something.
First, you can’t use VBA to manipulate data providers of any type other than those based on a Universe. Second, even if you could, you can’t use prompts in a personal data provider. So…
I think your first step is going to have to be creating a universe based on your Excel spreadsheet. There are a whole set of challenges there. From that point you can consider writing some VBA code that manipulates the data provider prompts.
It’s not the most difficult thing in the world, but it’s not trivial.
Now if you’re truly talking about using Visual Basic (VB) rather than Visual Basic for Applications (VBA) then that’s another wrinkle to deal with as well.
I don’t have such code to share. I expect that it’s more than a few minutes worth of work.