Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert Excel tabs to PDF (keeping very specific formats)

Bren312
8 - Asteroid

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

 
 

Excel_Example.jpg

13 REPLIES 13
Amy_smart
11 - Bolide

Hi,

 

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

 

Thanks,

Amy

apathetichell
19 - Altair

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.

 

This video shows how to write code in Excel VBA to save the Sheets in a Workbook as Separate PDF File on a Drive. The key to doing this is create a "For Each Loop" to run through each Worksheet individually in the Collection of Worksheets. This lesson also displays how to append Today's Date to ...
Bren312
8 - Asteroid

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
12 - Quasar

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
8 - Asteroid

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

ArnaldoSandoval
12 - Quasar

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.

 

Xls2PDF-01.png

Arnaldo

Bren312
8 - Asteroid

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
12 - Quasar

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.

Xls2PDF-02.png

  • 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:

Xls2PDF-03.png

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.

Xls2PDF-02.png

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
8 - Asteroid

@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!

Labels