Power Query Load table-type data to individual Excel Worksheet

0
votes

Hi I am trying to load table records of a power query table to my Excel Workbook.

I have this transformed table in Power Query wherein Table in the Records column are table objects that contains data fetched from a REST endpoint.

enter image description here

I want to load the Tables to worksheets which I will be naming based on the WorksheetName column. I have a worksheet that will contain the payload for the request and in the VBA which will be triggered by a click of a button, the Worksheet creation + population will occur.

enter image description here

Is there a way of doing that in VBA? I tried searching for a comprehensive way of doing that but I cant find any.

Thank you very much.

1
excelvbarestpowerquery

1 Answers

0
votes

Are the sheetnames variable based on userinput? I had a similar issue, I just created the sheets, loaded the query to them and removed all but the first two rows (to prevent the file not getting slow). Then when a user clicks the button, the VBA will unhide the sheet and refresh the query.