RE: Function for DX InDev PDF to Excel Converter Error

[Dhenn_MarkEspir]

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_MFBQ2K....

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

[Dhenn_MarkEspir]

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

[michaeloneil · Accepted solution]

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

[Dhenn_MarkEspir]

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

[michaeloneil]

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

[Tejaskumar_Darji]

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

[michaeloneil]

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

[raniaahmed]

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

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

[EllenPeereboom]

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

[michaeloneil]

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

[EllenPeereboom]

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