topic RE: Saving Excel file as PDF in Product Forum

Saving Excel file as PDF

JonathanHolstin — Fri, 10 Apr 2020 03:15:00 GMT

Hello,
I have found a few videos on this topic and attempted to follow them but not having any luck. Does anyone have any links to easy walk-throughs on this or is anyone willing to chat with me on how to accomplish this task.

Thanks,

Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: Saving Excel file as PDF

zdenek.kabatek — Fri, 10 Apr 2020 13:28:00 GMT

Hi, Jon,

create a new action in MS Excel VBO and put there codes stage with following code. Assuming that you know what else to do. If you do not know just ask.

Dim wb as Object = GetWorkbook(handle,workbookname)
Dim excel as Object = wb.Application

Dim fileformat As Integer = 0

excel.DisplayAlerts = False
excel.ActiveSheet.ExportAsFixedFormat(fileformat, filename)
excel.DisplayAlerts = True

Regards,

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

RE: Saving Excel file as PDF

JonathanHolstin — Fri, 10 Apr 2020 14:14:00 GMT

Hi Zdenek,
Thanks for the reply. I have attached what I did, I'm going to change my code to yours and see if that works.

Does the filename you are passing need to end with .PDF or .xlsx?

If you have a chance to look at my screenshots and let me know what I'm messing up it would be greatly appreciated!

Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: Saving Excel file as PDF

JonathanHolstin — Fri, 10 Apr 2020 16:21:00 GMT

Hi Zdenek,
I think I got it working using your code. Two quick follow-ups. When I dump the collection into excel and save in this format. 1) It doesn't expand the columns to show entire cell, is there a way to make this happen?

2) Is there a way to save PDF in a landscape format vs portrait? Or do you have a better way to dump a collection into a PDF document that looks good?

Thanks so much!!

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: Saving Excel file as PDF

JonathanHolstin — Fri, 10 Apr 2020 21:16:00 GMT

Hi Zdenek,
I figured it all out. Thank you for your initial assist.

Jon

------------------------------
Jonathan Holstine
Systems Accountant
Interior Business Center
America/Denver
------------------------------

RE: Saving Excel file as PDF

zdenek.kabatek — Sun, 12 Apr 2020 18:57:00 GMT

Glad I could help, Jon :).

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

RE: Saving Excel file as PDF

KumariYadav1 — Sun, 06 Jun 2021 04:23:00 GMT

Hey im getting error in this can you plz help me out in solving it.
i am pasting the error which i am getting:
Internal : Could not execute code stage because exception thrown by code stage: Save Workbook As took more than 0 secs.
in action in timeout i have provided 10 sec.but still getting error.

------------------------------
Kumari Yadav
------------------------------

RE: Saving Excel file as PDF

zdenek.kabatek — Mon, 07 Jun 2021 07:43:00 GMT

Hi, Kumari,

can you paste the code you have in your code stage?

Regards,

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

RE: Saving Excel file as PDF

KumariYadav1 — Mon, 07 Jun 2021 08:25:00 GMT

newworkbookname = ExecWithTimeout(Timeout, "Save Workbook As",
Function()
Dim wb as Object = GetWorkbook(handle,workbookname)
Dim excel as Object = wb.Application

excel.DisplayAlerts = False
wb.ActiveSheet.ExportAsFixedFormat (Type:=0, Filename:=(filename), Quality:=0, IncludeDroperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False)

excel.DisplayAlerts = True

Return wb.Name
End Function)

This is the code i am using nd yesterday actually i did some mistake after correcting that still its not resolved getting the following error:
Internal : Could not execute code stage because exception thrown by code stage: Exception from HRESULT: 0x800A03EC
Please reply as soon as possible

------------------------------
Kumari Yadav
------------------------------

RE: Saving Excel file as PDF

KumariYadav1 — Mon, 07 Jun 2021 08:35:00 GMT

and one more thing i want to ask you that after applying this code it will just convert the excel into pdf or it will save it also at some path .
Actually i am applying this action under one loop and need to save this and then use it later . please guide me on this also , how can we save it.
Thanks in advance.

------------------------------
Kumari Yadav
------------------------------

RE: Saving Excel file as PDF

zdenek.kabatek — Mon, 07 Jun 2021 08:40:00 GMT

Hi, Kumari,

if you do everything correctly - open excel with Blue Prism first, do your modifications and then call the action containing below code stage it MUST work I tested it.

Dim wb as Object = GetWorkbook(handle,workbookname)
Dim excel as Object = wb.Application
'For details, google XlFileFormat Enumeration
Dim xlTypePDF As Integer = 0

excel.DisplayAlerts = False

wb.activesheet.ExportAsFixedFormat (xlTypePDF, filename)

excel.DisplayAlerts = True

Regards,

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

RE: Saving Excel file as PDF

KumariYadav1 — Mon, 07 Jun 2021 09:19:00 GMT

Thanks for your response .
i will try this code also but can you let me what is the input need to be provided .
if i am not wrong handle ,workbookname ,filename also need to be passed as input in object studio? if yes then it repersents which file name excel filename or after saving as pdf what would be the name for pdf.
2)and please revert to the other query also that i have posted in this chain.

------------------------------
Kumari Yadav
------------------------------

RE: Saving Excel file as PDF

zdenek.kabatek — Mon, 07 Jun 2021 09:28:00 GMT

Kumari, how well you know Blue Prism? Let me give you some hints:
1. you need to create a process
2. in process open excel file with standard business object actions
3. this makes handle data item and New Workbook Name data item which you will use in the code as input parameters. Filename is the target PDF name which can be anything you choose/define.
4. you can run this in loop and create many files with different names following some naming convention (you should define your filename in calculation stage).

Regards,

Zdenek

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

RE: Saving Excel file as PDF

KumariYadav1 — Mon, 07 Jun 2021 12:35:00 GMT

1)Hey thanks for your help its working fine now. And yes actually i am little new to blue prism still in learning phase.
In above query actually my bad i just wanted to ask about file name anyways its okk now
2) still i have a query that if can we provide target path here that pdf should get save at a particular path
because for right now its taking document folder as default path but what if i want give a particular path?please reply.

------------------------------
Kumari Yadav
------------------------------

RE: Saving Excel file as PDF

ewilson — Mon, 07 Jun 2021 17:17:00 GMT

FWIW - There's an updated version of the MS Excel VBO on the Digital Exchange that includes a Export as PDF action for exporting either an entire workbook or just the active worksheet as a PDF.

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Saving Excel file as PDF

KumariYadav1 — Tue, 08 Jun 2021 03:40:00 GMT

thanks ,will check.

------------------------------
Kumari Yadav
------------------------------

RE: Saving Excel file as PDF

KumariYadav1 — Tue, 08 Jun 2021 03:53:00 GMT

MS Excel VBO- Enhanced ,right? in this i was getting error that -- dictionary key is not available.
anyways
can you tell me one thing i will explain you the situation .......
i have an excel file and in this i need to find the unique customer name and after getting that customer name i need the whole row for that particular customer name
because i need the details of that customer to perform other operation. i want that row to get into collection nd then perform other needed operation
But i don't want to use loop for getting unique customer name as its taking so much of tym ,actually i have around 200 to 300 data in excel so iterating each row is time taking. I have tried looping 20 rows nd perform all opearation , for this it took around 13 min.
Please guide me as soon as possible.

------------------------------
Kumari Yadav
------------------------------

RE: Saving Excel file as PDF

zdenek.kabatek — Tue, 08 Jun 2021 09:34:00 GMT

Hi, Kumari,

the steps should be following:
1. Read everything from excel to BP Collection
2. Use the VBO attached to this thread where you can find an action Return Unique Rows. You need to specify two input parameters. Input collection in which you want to search for unique rows and collection containing names of columns to compare for uniqueness. It can be just one row or multiple rows. In Output Collection you should get unique rows only.

There are some other actions you may find useful.

Regards,

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

RE: Saving Excel file as PDF

ewilson — Tue, 08 Jun 2021 12:23:00 GMT

@Kumari Yadav,

No, not the Enhanced. Just the regular MS Excel VBO. See the following link.

https://digitalexchange.blueprism.com/dx/entry/3439/solution/ms-excel-vbo

It looks like Zdeněk has covered your other question.

Cheers,

------------------------------
Eric Wilson
Director, Partner Integrations for Digital Exchange
Blue Prism
------------------------------

RE: Saving Excel file as PDF

KumariYadav1 — Tue, 08 Jun 2021 14:46:00 GMT

Yes Thanks

------------------------------
Kumari Yadav
------------------------------