Hello,
I am using SAP Analysis for Excel to return data from SAP. I need to continue to use that tool. My query returns some structured data. Let's say it's in cells A1 through B10. I would like to convert that range into an Excel table to do some further calcs, but if I were to do that, the table would revert back to a normal range if I were to refresh the SAP query. So, I can't do that.
I have some additional columns of formulas that I want to have near the SAP query. Since my SAP query has 10 rows, I'd want 10 rows of formulas - to work on the SAP data. So, I'd need formulas in cells D1 through E10. [Never mind about the headers, but technically formulas in just D2 through E10]. Most importantly, I would like to have these additional formulas be in an Excel table.
When I refresh my SAP query for the next month, it might have 12 rows in it: cells A1 through B12. If so, then I would like my Excel table to automatically expand to cells D1 through E12.
I thought of importing the results of my SAP query into Power Query and loading the results into an Excel table. Then, I could work with that data source. But, if I try to do that, PQ changes my SAP query to an Excel table. And when I refresh my SAP query, it would automatically convert that table back to an Excel range, which I don't want it to do.
I'm not sure if what I want is possible, but I'd be open to an Excel VBA solution and/or possible an Excel macro that executes when a cell on that sheet changes value. I'd also be open to some creative use of Power Query.
Thanks,
Scotty81
Hi Scotty, sure you can you keep the SAP import page.
What you have to do is in a new worksheet create a table (ListObject) as you want it and then every time you update the SAP query you fille the Listobject with the new data
Hope my explanation makes sense
Hi Hans,
Thank you for your quick reply - and I'm sorry for my tardy response. I wasn't sure exactly how to use the ListObject object in VBA so I looked that up and got some code to do the job. It worked! The Excel table will automatically expand when my source column expands. The only extra code I need to write is some cleanup code when the Excel table shrinks. It leaves a trail of zeros below the table when this happens. So, I'll write some code that looks for any zeros below the table and delete them.
Many thanks for pointing me in the right direction with the use of the ListObject.
-Scotty81
Hi Scotty, glad to hear it helped.
I suggest you take a look here; it explains it in a great way and has helped me to try out new ideas and put it to good use.
I suggest you download the file with all the VBA code which can be used