VBA Embed PDFs in Excel causing corruption

[u/audit157]

Im trying to build a tool that lets you select a folder of PDFs and embed each one in a separate sheet in an Excel.

It works amazingly well except when you go to save the file Excel says it's corrupt and can't be saved/error saving. I've tried tweaking it so many times but nothing works. Even just 1 pdf embedded causes the corruption.

When I manually embed the PDF there is no issue.

Does anyone know a fix? Or is programmatically embedding PDFs just not possible?

[u/zlmxtd]

Dont do that. But if you're going to anyway, try saving the workbook before adding the first pdf, and save again after each

[u/AlpsInternal → u/zlmxtd]

I was thinking the save process could be failing, I am not a VBA expert but us it a lot. Embedded PDFs do seem a bit wonky, so maybe saving the documents as word, then having a button to print them as PDFs could be a work around.

[u/audit157 → u/zlmxtd]

Good idea but unfortunately the second I add 1 embedded document the entire workbook is corrupted

[u/carnasaur]

Have you tested the code in a fresh wb with nothing else in it while configuring it to save the wb to a folder on your local drive so you can rule out UNC, sharepoint and conflicting module errors?

[u/diesSaturni]

Why would you even do that. I always dislike when people send me excel with embedded stuff , even images

[u/audit157 → u/diesSaturni]

It's required. You can only upload 1 Excel file but you need to have all of the PDFs too. Solution is to have all PDFs in the Excel file

[u/jshine13371 → u/audit157]

You can only upload 1 Excel file but you need to have all of the PDFs too.

Upload to where...?

[u/audit157 → u/jshine13371]

Accounting software

[u/jshine13371 → u/audit157]

So presumably the correct thing to do to solve the problem is ask the makers of the software how to upload these required documents if the tool only allows one upload, instead of hacking together your own solution.

[u/audit157 → u/jshine13371]

That's not possible. The real issue is why VBA embedding even 1 document fails when manually doing it is fine

[u/jshine13371 → u/audit157]

That's not possible.

It's not possible to ask a question?

[u/audit157 → u/jshine13371]

Already have. This is the only solution. It's just whether or not is manual or with VBA. After expentsive research it appears it's not possible with VBA as it automatically corrupts all workbooks after 1 off embedding

[u/jshine13371 → u/audit157]

This is the only solution.

Negative, there's always more than 1 solution.

For example, a better possible solution would be to just zip up the PDFs and Excel file into a single zipped archive file.

[u/audit157 → u/jshine13371]

I already tried that. Each PDF needs its own sheet and even if I do a zipped folder for each PDF for each sheet it would require a click to enter the folder, clicking yes on a pop up and then clicking the file which is too much

[u/diesSaturni → u/audit157]

I'd then export excel to PDF, then merge all PDF's to a single one.

[u/chiibosoil → u/diesSaturni]

It is common in network DDP documentation.

BOM, Rack diagram etc are embedded and sent to contractors to execute as single package.

[u/daishiknyte]

How large are the PDFs? Might be better to JPG them then add the images to the page. 

[u/audit157 → u/daishiknyte]

The users need access to the original PDFs. They are probably 50 pages each or 1MB

[u/daishiknyte → u/audit157]

Would a .zip of the PDFs work?

[u/Sammo223 → u/audit157]

The users don't have access to SharePoint or a shared information management system? What kind of info is contain in the pdfs and can you convert them into a better format for excel first?

Is embedding the pdf just inserting a picture?

[u/audit157 → u/Sammo223]

Tons of contracts and no it has to be done this way. If VBA can't embed PDFs people will just need to continue manually embedding

[u/HFTBProgrammer]

The following code creates a perfectly acceptable Excel workbook when I start with an empty workbook:

ActiveSheet.OLEObjects.Add(Filename:="A:\Binder1.pdf", Link:=False, DisplayAsIcon:=False).Select
Sheets.Add
ActiveSheet.OLEObjects.Add(Filename:="A:\Binder2.pdf", Link:=False, DisplayAsIcon:=False).Select
Sheets.Add
ActiveSheet.OLEObjects.Add(Filename:="A:\Binder3.pdf", Link:=False, DisplayAsIcon:=False).Select

So I wonder what you're doing differently.

[u/audit157 → u/HFTBProgrammer]

That works, thank you! I was using Shapes.AddOLEObject.Add instead of just oleobjects.add

Solution Verified!

[u/reputatorbot → u/audit157]

You have awarded 1 point to HFTBProgrammer.

---

^{I am a bot - please contact the mods with any questions}

[u/HFTBProgrammer → u/audit157]

Splendid! Come back any time.

[u/MaxHubert → u/audit157]

Glad you found a solution, I was about to give it a try myself because from experience i knew there was a solution. If you can do it manually VBA can do it for sure.

[u/kay-jay-dubya]

Are you not interested as to why b you were getting these errors?