Bulk import of pdf's or pdf objects into Excel

> the user can click on that pdf icon and open the pdf from within the excel spreadsheet

Are you OK with the cell containing a clickable link? In other words, the cell would have this:

=HYPERLINK("d:\path\filename.pdf")

The user would click on the link in the cell (rather than a PDF icon) to open that PDF.

> excel spreadsheet

How do you want to handle the case where the Excel file does not exist? Exit with an error code/message? Provide an option to create the file?

Regards, Joe

my user would prefer that the icon is shown, the same as if you pick insert > object > create from file > display as icon and then choose the pdf. It can't be a link because the link or original location won't be accessible to the final user.

The excel file will already be created with other info with these pdf needing to be imported into it. As for the non-existent file, I assume you mean the pdf files. I don't know that this is an issue because a folder won't be chosen if there are no pdf's in it. Example is if the folder has 3 pdf's I want 3 icons for those files embedded into cells C1-C3. If there are 20 pdf's they would come into cells C1-C20. When there are no more files to import the routine ends.

> As for the non-existent file, I assume you mean the pdf files.

No, I meant the Excel file.

> icons for those files embedded

I was willing to consider modifying a program that I had already written which knows how to prompt the user for a folder, spin through every PDF file in that folder, and access a user-specified cell in an Excel spreadsheet. I could change that program to put a hyperlink to each PDF file starting in the specified Excel cell (such as C1) and running down that column for as many PDF files as there are in the folder (such as C20), but not to do embedding. Regards, Joe

thanks for looking into this and it sounds like you have the macro that I need but I do need the files embedded with icons since the end user won't have access to the original location. Thanks

> you have the macro

It's a stand-alone, executable program, not a macro.

> need the files embedded with icons

I'm not an expert in Excel, so I could be wrong about this, but I don't think you can insert an object into a specific cell in an Excel worksheet. In other words, there's no way to embed the PDFs in, for example, cells C1 through C20. Regards, Joe

oh ok... I have seen an example of what I am looking for in the past but I don't have access to the macro anymore or the example. Im not looking for a standalone program because I want to specify the location of the starting cell before running the macro.

I know the icon part works also or embed because it's the same as selecting a cell > insert > object > create from file > select pdf file > insert > select Display as icon checkbox > OK

Then when I highlight an individual icon in one cell the formula bar shows Object XX and =EMBED("Acrobat Document","")

> I want to specify the location of the starting cell before running

Yes, I understand. A modified version of the program in the referenced article would prompt you for the cell address like this:


It even performs error checking on the cell address, as follows:

(a) The cell address must be entered

(b) The first character of the cell address must be alphabetic

(c) The cell address must be entirely alphanumeric

(d) The cell address must contain at least one number that is not zero – in other words, cells such as A0 or B00 are not valid

All of those errors display an Error dialog box. Here's one example:


> Then when I highlight an individual icon in one cell the formula bar shows Object XX and =EMBED("Acrobat Document","")

Yes, but it is not in a cell. Do this: hover the mouse over the icon, which looks something like this:


You will get the standard Windows drag symbol. Now left-click and drag it elsewhere. You will see that it can go anywhere and is not in a cell. Btw, what version of Excel are you using? Regards, Joe

my fault, I wasn't understanding and you are correct. I didn't realize that they weren't tied to a cell but placed manually instead. I am using excel 2013, does your program allow for bulk import of pdf's as objects like you show above and then we can just manipulate their location?

so now I know it's not tied to a cell but I think the user was selecting a cell before the import and it just looks like that is where it was placed. Even though it's not tied to the cell, could it still be automatically placed near that next location?

> does your program allow for bulk import of pdf's as objects like you show above and then we can just manipulate their location?

I know how to programmatically write a hyperlink to a cell in an Excel worksheet. I do not (yet) know how to programmatically embed/insert an object into an Excel worksheet. I may be able to figure it out, but I don't know how long the research would take.

> Even though it's not tied to the cell, could it still be automatically placed near that next location?

I don't know if it is possible to programmatically control where an embedded/inserted object goes on the worksheet.

I think the example that I saw placed the incoming object at the current cell focus and then advanced the cell focus before the next import object, but anyway. At this point I don't have anything so I would appreciate any solution that you have to offer or are willing to assist with as long as it's not the link option since the end user won't have access to that. Thanks

I'll do some research on programmatically inserting objects, but my gratis work here at EE, especially when I think it's going to be very time-consuming, sometimes must take a back seat to my paid work, so I can't say exactly how long it's going to be. As I'm sure you can appreciate, paid consulting work goes to the top of my queue. In the meantime, perhaps an Excel expert will jump in with a quick, macro-based solution to this (which is beyond my expertise). Regards, Joe

Here's what I have so far. The program can now embed an object (such as a PDF file) with the upper left corner of its icon beginning at a specified cell address, such as C3. As stated earlier, it is not in the cell — the icon is merely displayed at that cell's location. The width of the icon is variable to accommodate the file name; its height is fixed at 40 pixels. Since the default row height in Excel is 15 pixels, the icon will take up almost three rows, unless you specify a non-default row height. I'm thinking it would be nice to place the icon in every third row so they don't overlap when the default row height is in effect. Another idea is to allow the user to specify the row increment, perhaps a number between 1 and 5. Anyway, here's what the programmatically embedded PDFs look like in an Excel 2013 worksheet with the beginning cell of C3, the default row height of 15, and the placement of the icons 3 rows apart:


Regards, Joe

Joe, thanks for your efforts. I completely understand the gratis vs paid work. I appreciate any assistance I can get when I can get it. Thanks again. The above already looks like something that I could make work with a little end user row adjustments or something.

> I completely understand the gratis vs paid work.

Thanks for understanding!

> I could make work with a little end user row adjustments or something

Shouldn't have to adjust. I added an option to the program so you can specify the number of rows between entries (a drop-down choice between 1 and 5). Also, to make it have broader applicability, I'm providing options to (1) embed the PDFs or use hyperlinks to them, (2) select an existing spreadsheet or create a new one (either XLS or XLSX), (3) process subfolders. Here's a mockup of the UI:


Let me know what you think. Regards, Joe

It looks like this would work fine Joe. Thanks for all of the effort. I would like to test this out with a couple of my users for a few days and get back with you if needed. Let me know how I can try this out.

Pertaining to your row height comment above, the spreadsheet that we have to import into is provided to us with a large row height already changed from the default. So if your files import based on the upper left corner of the file icon to the next column cell such as C1, then C2 etc. they should all come in perfect without manipulation.

The only feature not developed yet is the folder/subfolders options. Right now it handles just the specified folder ("Root folder only" option), not subfolders.

> if your files import based on the upper left corner of the file icon to the next column cell such as C1, then C2 etc. they should all come in perfect without manipulation.

Yes, that's exactly what it does. For example, here's what it looks like with a large row height (50 pixels), a starting cell of C3, and a row increment ("Select number of rows between entries" option) of 1:


> Let me know how I can try this out.

OK, I'll let you know. Regards, Joe

Can you elaborate on specified folder? It may work now then. My test user would like to specify a starting cell, select a folder and import all of the pdf's there from that folder. Then if needed select a new starting cell, specify a different folder of pdf's and import again, etc. Running this macro or program multiple times is not an issue, it doesn't have to do everything in one process.

> My test user would like to specify a starting cell, select a folder and import all of the pdf's there from that folder.

That's what it does now. But I've developed a lot of programs for batch/bulk/mass processing of PDF files (and TIFF files), and my experience is that users often want to process the subfolders, typically to an unlimited depth. I've already written the subroutine for it and and have incorporated it into numerous programs, but each time needs a little tweaking depending on the surrounding code.

> Then if needed select a new starting cell, specify a different folder of pdf's and import again, etc.

No problem — does that now.

I was also planning to write a Windows installer (with uninstaller) for it, providing a standard Setup.exe file, but I could get it to you faster as a stand-alone executable (without the installer/uninstaller) and without the subfolders option. Sounds as if it will work for your test user in its current state. Regards, Joe

Either way works for me Joe, let me know here or PM me when your ready. I'm not up on the process of getting the program from you or how this usually works on these forums. I assume your not just posting it here. I can provide you my email or if you have a dropbox location etc for me to grab it from just let me know. Thanks again.

OK, let's talk about logistics. I just sent you a note via the EE Message system.

The only feature not developed yet is the folder/subfolders options. Right now it handles just the specified folder ("Root folder only" option), not subfolders.

For anyone else following this thread, the folder/subfolders options are now working. I changed some of the language and ordering in the UI, so it looks like this now:


Also, I added two more features:

(1) It shows a green progress bar moving to the right so you know that it isn't hanging and that processing is taking place. The dialog box with the green bar also contains the file name of the PDF file currently being processed and the percentage completion within the current folder.

(2) When the program finishes, it creates an Operational Statistics (plain text) file in the same folder from which the program was executed, containing this information about the run:

Operational Statistics
Beginning date and time: 2014-11-10_14.22.24
Source folder: D:\temp\bulk import test\
Beginning Excel cell: C3
Row increment: 3
Number of PDF files processed: 5
Excel file: C:\temp\bulktest.xlsx
Ending date and time: 2014-11-10_14.22.26
Elapsed time (minutes:seconds): 0:3

That's it for now. Regards, Joe