topic RE: Function for DX InDev PDF to Excel Converter Error in Digital Exchange

RE: Function for DX InDev PDF to Excel Converter Error

Dhenn_MarkEspir — Wed, 08 Jun 2022 05:19:00 GMT

Has anyone use this shared asset in DX? I tried it but got an error below when executing the code stage for Save As.

Could not execute code stage because exception is thrown by code stage: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))

Link to Asset: https://digitalexchange.blueprism.com/dx/entry/122031/solution/pdf-to-excel-converter?_ga=2.52220138.271419323.1654492142-447972324.1642993955&_gl=1*1kxbg6g*_ga*NDQ3OTcyMzI0LjE2NDI5OTM5NTU.*_ga_MFBQ2KFZ1L*MTY1NDY2NDcxNi4xMjEuMS4xNjU0NjY1NDk1LjA.

------------------------------
Dhenn Mark Espiritu
RPA Consultant
EY
Asia/Manila
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

michaeloneil — Wed, 08 Jun 2022 11:58:00 GMT

Hi Dhenn

Can you share the input values you are entering for the object?

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

Dhenn_MarkEspir — Thu, 09 Jun 2022 01:14:00 GMT

This is my input:

This is the error when attempting to save the workbook.

------------------------------
Dhenn Mark Espiritu
RPA Consultant
EY
Asia/Manila
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

Dhenn_MarkEspir — Thu, 09 Jun 2022 01:15:00 GMT

Hi @Michael ONeil, I replied my inputs below. I am using BP7

------------------------------
Dhenn Mark Espiritu
RPA Consultant
EY
Asia/Manila
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

Tejaskumar_Darji — Thu, 09 Jun 2022 12:45:00 GMT

Hello,

Your inputs are correct.

I tried to recreate the same error and I'm able to see the same Index error message. (Note: I'm not able to select any text in the PDF that I tested)

Upon checking reviews on DX couple of folks have reported the same error.

The current version of VBO on DX denotes a beta version.

To test further I took a PDF that was selectable and fully digital so I was able to convert it successfully. Check the snapshots. Also attaching the PDF if you want to try it.

So this might be the limitation that it is able to convert the PDF which is in full digital and selectable format instead of scanned PDF which uses images.

------------------------------
If I was of assistance, please vote for it to be the "Best Answer".

Thanks & Regards,
Tejaskumar Darji
Sr. Consultant-Technical Lead
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

Tejaskumar_Darji — Thu, 09 Jun 2022 12:47:00 GMT

Test with this PDF

------------------------------
If I was of assistance, please vote for it to be the "Best Answer".

Thanks & Regards,
Tejaskumar Darji
Sr. Consultant-Technical Lead
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

michaeloneil — Thu, 09 Jun 2022 13:59:00 GMT

Hi

@Dhenn Mark Espiritu and @Tejaskumar_Darji I was the one that developed this asset for use on the DX but as you stated its not intended to be used with scanned documents. This would only work using standard pdf files as this simply creates it in an excel through a series of different formats but for scanned documents the output would likely not be transferable to cells within excel. If you need to get info from scanned docs it would be better to use ocr for something similar to get the specific data from the screen rather than try to convert it.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

devneetmohanty07 — Thu, 09 Jun 2022 14:05:00 GMT

Hi Michael,

I also have observed the same and I have went in quite some details inside that VBO too. If all inputs are provided well and they are digitized, this VBO works amazingly. It has literally saved a lot of head banging situations for me so far in many use cases especially while dealing with tabular data sets.

Really appreciate your contribution and efforts for this VBO.

------------------------------
----------------------------------

Regards,
Devneet Mohanty
Intelligent Process Automation Consultant | Sr. Consultant - Automation Developer,
WonderBotz India Pvt. Ltd.
Blue Prism Community MVP | Blue Prism 7x Certified Professional
Website: https://devneet.github.io/
Email: devneetmohanty07@gmail.com

----------------------------------
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

Dhenn_MarkEspir — Fri, 10 Jun 2022 01:12:00 GMT

Hello Tejaskumari,

Still doesn't solve the error even I used your own PDF as you attached. My PDFs are not images or scanned PDFs, they are structured and have selectable text and tables as well.

See error below:

The error encountered when going to "Save workbook" code stage. The conversion from pdf to excel was not successful and it just leaving me a ".mht" file.

Is there an official release of this that is not beta already?

------------------------------
Dhenn Mark Espiritu
RPA Consultant
EY
Asia/Manila
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

michaeloneil — Fri, 10 Jun 2022 07:45:00 GMT

Hi Dhen

it could be the file type in the save script causing the error, if you can try amending the code stage from this wb.SaveAs(filename,51) to this wb.SaveAs(filename) and in the calc stage called "Set new workbook name" change the calc from Replace([filename], ".mht", "") to this Replace([filename], ".mht", ".xlsx").

The code stage is currently explicitly setting the file extension to xlsx but if you remove this and just provide the extension as part of the name it should hopefully resolve the error.

@devneetmohanty07 I'm glad youre finding it useful, its saved me a lot of dev time trying to extract data from pdf's.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

Dhenn_MarkEspir — Fri, 10 Jun 2022 08:55:00 GMT

Hi Michael,

Still not working :(. This could be saved me a lot of hours if I can only make it work.

I tried changing the stages you mentioned:

Code Stage:

And still not working.

I'm using this version of Excel:

------------------------------
Dhenn Mark Espiritu
RPA Consultant
EY
Asia/Manila
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

michaeloneil — Fri, 10 Jun 2022 13:56:00 GMT

Hi Dhen

I've been retesting the object and i can only recreate the error when i provide incorrect parameters but there are a couple of things you can try. Change the calculation back to Replace([filename], ".mht", "") and the code stage back to wb.SaveAs(filename,51). Once done double check the inputs for the SaveAs code stage match my screenshot below. Save your changes and re-run your process, if you get the same error the next thing to try would be to move the pdf to another folder location for example on your desktop and then run the process again with the new location for the pdf as the input. Should you still have the same issue then try recreating the error directly through excel. You mentioned before that you get the mht output file so with this file open it with excel and then try to save it as an xlsx file to the original folder and see if you get any errors or warnings.

Beyond this I think the only other thing you could do is, since it fails but still produces the mht file, you can then use the standard excel object actions to open this mht file and resave as an xlsx. This will give you what you need it to be, the convert object only seems to fail on the final step of saving the file as an xlsx so as long as you can get the mht file then you only need to add a few steps to your process to get the final output file. See my other screenshot for an example.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

Dhenn_MarkEspir — Tue, 14 Jun 2022 01:11:00 GMT

Awesome! This workaround worked for me. The workaround to use standard excel objects to save mht file as excel file. Thank you so much @Michael ONeil!

------------------------------
Dhenn Mark Espiritu
RPA Consultant
EY
Asia/Manila
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

michaeloneil — Tue, 14 Jun 2022 07:35:00 GMT

Hi Dhenn

No problem I'm glad I was able to provide you a solution I hope it will save you some time in data extraction. I'll work on the asset itself to hopefully resolve this issue properly for future users 🙂

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

Tejaskumar_Darji — Sat, 25 Jun 2022 11:51:00 GMT

Hi Michael,

I followed the same route of saving the mht file as xlsx using regular MS Excel VBO.

It created the XLSX file but when I open it throws this error and does not proceed further.

Are you guys able to open and consume the resaved mht file as xlsx properly?

------------------------------
If I was of assistance, please vote for it to be the "Best Answer".

Thanks & Regards,
Tejaskumar Darji
Sr. Consultant-Technical Lead
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

michaeloneil — Mon, 27 Jun 2022 11:07:00 GMT

Hi @Tejaskumar_Darji

Are you using Save As from the excel vbo or Save? have you tried saving it as a .xls instead?

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

raniaahmed — Sun, 03 Jul 2022 09:14:00 GMT

waiting your kind update also to asset as when i opened the excel it didn't show the characters

------------------------------
rania ahmed
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

EllenPeereboom — Tue, 20 Jun 2023 15:35:00 GMT

Hi Michael,

Thanks for your tips regarding this issue. We also get the .mht file but are unable to convert it to an .xslx. We extensively tried the manual approach you suggested, but it does not work yet. Would you mind giving more insight into the exact details of the standard Excel object actions?

Thanks a lot! Best regards,
Ellen

------------------------------
Ellen Peereboom
Lead RPA Developer
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

michaeloneil — Tue, 20 Jun 2023 18:21:00 GMT

Hi @EllenPeereboom

I've never been able to replicate the issue that others have had with this object without giving incorrect parameters and that doesnt effectively replicate the issue so I couldnt identify the cause. I think this is being caused due to notifications from excel, if you can could you make a change in one of the code stages in your version of this object?

Open the object and select the tab Convert pdf to excel, on this page open the code stage called "open file in excel" change the code to match the following

Dim excel as object

Dim wb as Object = GetInstance(handle).Workbooks.Open(filename, 0, False, , , , True)

Dim sheets as Object = wb.Sheets

excel = sheets.Application

excel.DisplayAlerts = False

name = wb.Name

wb.Activate()

Once you have done this save the object and retry with the file you are having a problem with and see if it works.

------------------------------
Michael ONeil
Technical Lead developer
NTTData
Europe/London
------------------------------

RE: Function for DX InDev PDF to Excel Converter Error

EllenPeereboom — Wed, 21 Jun 2023 09:22:00 GMT

Hi Michael,

We tried the workaround by saving the file as an .xls instead of .xlsx in the regular MS Excel VBO (as mentioned above in this thread), and from there it worked perfectly again!
The display alerts settings were already turned off in the object we used (excel.DisplayAlerts = False), so that we did not need to change.

Thanks a lot!

Best regards, Ellen

------------------------------
Ellen Peereboom
Lead RPA Developer
------------------------------