Convert Excel tabs to PDF (keeping very specific formats)

[Bren312]

Hi all, I'm hoping someone will be able to help me answer a question.  I'm trying to automate sending 20 - 30 Excel tabs which have multiple sections per sheet as PDFs attached to emails.

The spreadsheets all have very specific formats which must be keep intact (basically they are scorecards with conditional formatting built on a rat's nest of calcs).  An example of one of these scorecards is included below.

I'm hoping to create PDFs of each one of these monsters and send to various distro lists while maintaining the exact structure of each section of the individual worksheets.  If I use a Render tool, I believe I'll need to create a table to input into the tool which will wreck the structure I need to maintain.  Hopefully that makes sense.  Please let me know if you have any questions and I'll be happy to try and clarify.  Thanks all!

Bren

[Amy_smart]

Hi,

Here is another discussion that answered a similar question: Solved: Convert excel files to PDF - Alteryx Community

Thanks,

Amy

[apathetichell]

first off - I don't think there's a straight forward way to do what you are doing. If I had to do it - I'd use a VBA script like this - https://www.youtube.com/watch?v=nTvUv5Alwec. I'd use Alteryx to modify the script if needed and for tracking fo filenames/locations/sheets.

I'd use run command for launching the Visual Basic Script.

I'd use Alteryx to read in the pdf files as blobs. Once they are read in as blobs its fairly straight forward to send via e-mail.

[Bren312]

Thanks @Amy_smart  for the recommendation on the other thread with a similar issue; I don't think this will work for me due to the very structured nature of the tabs.

@apathetichell, I'm going to give your method a try; it's not something I've done before so it'll be interesting to poke around and see if I can get it to work.  But yeah...the way it's put together currently is a cluster.  Thanks to both of you!

Bren

[ArnaldoSandoval]

Hi @Bren312 

This page contains some script code to convert XLSX to PDF, perhaps it may help How to Batch Convert Excel Sheets into PDF Files 

[Bren312]

Awesome; thanks Arnaldo!  I'll check it.  Much obliged for the link!

[ArnaldoSandoval · Accepted solution]

Hi @Bren312 

I created a workflow and macro to convert XLSX files to PDF; the process convert all the sheets in the Excel files to PDFs, the output is by each sheet's print area (unfortunatelly) the VBS script could be customised to activate whatever sheet in the Excel file being process (no implemented) the conversion from Excel to PDF takes around 30 seconds, this could depend on the size of the Excel file involved, and you can as many Excel files to the Text Input tool; I did not try entering full path file locations, and still you should take into account reaching those files if the workflow is published to the server.

Arnaldo

[Bren312]

Hi @ArnaldoSandoval, first off, thank you so much for your time and effort on this; I really appreciate it! I suspect this might work for me but I'm not totally sure how to set it up.

If I have an xlsx file called "InputTemplate" located at C:\Bren\SpreadsheetInput and this file contains three sheets ('HealthServices'; 'CommunityNetwork'; and 'TechServices') how would I go about setting this up to work with the macro? Also, if I wanted a pdf for each complete sheet (instead of a select print area) how would that be accomplished?

Sorry for the newb questions; I've never used the "Run Command" tool or VBA scripting before. Thanks so much for any help you can provide!

Bren

[ArnaldoSandoval]

Hi @Bren312 

Cool 😎 you are getting exposure to new knowledge,

There are two steps you or your users should do:

• Prepared the Excel file.
• Update the workflow's Text Input with the file or files to process.

Prepare the Excel file:

• You or your user should define the Print Area in the Excel file, by example I am generating the PDF file for 3 Excel files.

• The first file, "Sample_File_1.xlsx" is on the folder where the workflow is located, while the second and third files are at the folder: "D:\temp\2023-07-July"
• The first Excel file is the Sample_File_1.xlsx already shared, nothing to do here.
• The second Excel file is: "B2B TackMyOrder Testing.xlsx", this file has 3 worksheets: "Sheet1", "Sheet2" and "Sheet3"
• "Sheet2" and "Sheet3" are empty, so nothing to do with them.
• "Sheet3" looks like the picture below:

This Sheet contains 17 record, We need to define its print area in Excel.

Let's allow Maria on this video to explains: How to Set the Print Area in Excel (Step by Step) (Great video by the way)

• The third Excel file is: "SO FC data sample.xlsx" also located on my D:\Temp\2023-07-July, with 3 sheets: "Sheet1", "Sheet2" and "Sheet3", in this case "Sheet3" is empty, nothing to do with it.
• We followed Maria's directions to set the "Print Area" on these two sheets.
• I am not sure, but the Workflow-macro-vbs will print whatever is defined in the "Print Area" so if your Excel file has many sheets, it will print whatever is defined as Print Area (I haven't test this)

Update the workflow's Text Input with the file or files to process:

• This step is simple, just add the file or files to the input tool.

Warning: If the workflow is running local on your workstation (laptop or desktop), you can enter any folder and it will work; while if you deploy this workflow to the server, the environment to select the files is different, beside, the list of files is "hard coded" into the workflow, so my advice to deploy to the Alteryx Server is to convert this workflow to an Analytic Application.

We are set, now we run the workflow, it generates 3 PDF files, each one located at the folder where the Excel file is found.

Alteryx Server or Gallery Deployement.

This workflow works fine on your workstation, but it will have difficutlies if deployed to the Alteryx Server because we hardcoded the Excel files it process, the workflow may need to be converted to an Analytic Application.

Performance:

It processed these 3 Excel files fairly quick, less than 1 minute.

Hope this helps,

Arnaldo

[Bren312]

@ArnaldoSandoval , thank you so much for this breakdown; you're a freaking pro!  I really appreciate the in-depth walkthrough.  Very excited to implement this!