September 15, 2020
I have used Power Query to match data from 3 other excel worksheets to create one worksheet that contains data that I need to create separate PDFS. The Power Query results worksheet are in Sheet1 of the attached PDFQuestion.xlsm. I want to create a PDF grouped by tblSampleInfo.Assess Num (column M on worksheet). You will see from the excel sheet there are 56 rows with a tblSampleInfo.Assess Num of CL20-0975. So the first PDF (Attached) created should have the patient info, physician, and sample info as header data. The Target name (col B), Crt mean (col C), RX treatment (col U) and a few other fields would go into a list box or the body of the report. Once I have printed that PDF with Assess num CL20-0975, then I create another PDF - this time using the Assess Num of CL20-0976 - there will probably be another 56 rows of data that will have patient/physician/sample information as the header data and the body would have a listbox of Target name, crt mean, etc.
I am new to Power Query and Pivot Tables.
How can I create the multiple PDF's from the one Power Query results worksheet ?
What steps do I need to take to create those PDF's from the Power Query runs and refreshes (as those PQ will be run weekly, sometimes two times a week. I plan on just having a folder for the client to upload data to refresh the query).
Additionally, I am trying to automate this process because this will be used by a client. So if possible, I would like limit the excel keystrokes for the user, or create a macro button to run the Power Query and the creation of the PDF's. But if this is not possible, I am looking for the most automation.
Thank you for any advice!
July 16, 2010
Welcome to our forum!
Thanks for sharing your example files. Unfortunately, you can't use Power Query to generate the PDFs.
If you weren't restricted by the PDF layout you could use PivotTables to extract the data. But if you want that specific layout then you'll need to use VBA. I don't have a tutorial that will teach you everything you need to know to write the VBA code to create the PDFs, so if you don't already know VBA, you might like to engage a consultant. Let me know if you want me to recommend one.
October 5, 2010
September 15, 2020