Hello all.
I have an inventory workbook that uses data slicers to drill down to specific criteria. The worksheet that displays the inventory has the product models in columns going across the worksheet. The rows going down are the locations where the product is housed. There is a module of code that exports the print range (which is dynamic) to a PDF file but there is one problem. If there are too many columns (too many different models selected) being displayed the PDF will be crunched down to only one page in width and it is impossible to read.
I would like to add some code that will take the width of the report before it is exported as a PDF and determine if it needs to be resized. If the width exceeds 756 points (10.5 inches) then the output to the PDF will be split onto two pages wide. I have the code to get the width but I have no idea on how to get Excel 2016 to resize/change the output to more than one page in width using code. Also need to do the same with the height: 504 points or 7 inches.
I used Google for the code to get the width in points but have only done a minimal search for the code to resize/change the page width. This isn't a time critical upgrade to my workbook so I have not put that much time into yet.
For reference...
Row 17 = This is the row that has all the product model names for each column
Col C: First column of product. Goes out to as far as needed. (Should not exceed 50 columns)
Row 18 and down: all the different locations where product is kept (Can be as many as 500)
First cell with Product quantity will always be C18.
I am using this for the cell phone industry so the two columns are for Col A: Franchise owner's business name, Col B: Address of location (most franchises have more than one location.)
Any help would be great!
Thanks!
I have an inventory workbook that uses data slicers to drill down to specific criteria. The worksheet that displays the inventory has the product models in columns going across the worksheet. The rows going down are the locations where the product is housed. There is a module of code that exports the print range (which is dynamic) to a PDF file but there is one problem. If there are too many columns (too many different models selected) being displayed the PDF will be crunched down to only one page in width and it is impossible to read.
I would like to add some code that will take the width of the report before it is exported as a PDF and determine if it needs to be resized. If the width exceeds 756 points (10.5 inches) then the output to the PDF will be split onto two pages wide. I have the code to get the width but I have no idea on how to get Excel 2016 to resize/change the output to more than one page in width using code. Also need to do the same with the height: 504 points or 7 inches.
I used Google for the code to get the width in points but have only done a minimal search for the code to resize/change the page width. This isn't a time critical upgrade to my workbook so I have not put that much time into yet.
For reference...
Row 17 = This is the row that has all the product model names for each column
Col C: First column of product. Goes out to as far as needed. (Should not exceed 50 columns)
Row 18 and down: all the different locations where product is kept (Can be as many as 500)
First cell with Product quantity will always be C18.
I am using this for the cell phone industry so the two columns are for Col A: Franchise owner's business name, Col B: Address of location (most franchises have more than one location.)
Any help would be great!
Thanks!
