Solved

Bulk import of pdf's or pdf objects into Excel

Posted on 2014-11-06
24
317 Views
Last Modified: 2014-11-12
I am looking for a macro or module or something that will allow me to run or click a button and be able to choose a folder with multiple pdf files to import. I want it to import each pdf file into a specific cell so that the user can click on that pdf icon and open the pdf from within the excel spreadsheet. I know this is standard insert object stuff, however I want to be able to pick the folder and import all pdf's into consecutive cells in a column until all files have been imported regardless of the number of files without having to do this manually for every pdf. It's usually 20-30 pdf's. Does anyone have a macro that will do this bulk import of pdf to a column of cells? Thanks
0
Comment
Question by:circa62
  • 13
  • 11
24 Comments
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
ID: 40428728
> 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
0
 

Author Comment

by:circa62
ID: 40428777
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.
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
ID: 40428973
> 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
0
 

Author Comment

by:circa62
ID: 40428984
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
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
ID: 40429013
> 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
0
 

Author Comment

by:circa62
ID: 40429074
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","")
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
ID: 40429183
> 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:

enter Excel cell address
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:

Excel cell not alphanumeric
> 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:

embedded pdf
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
0
 

Author Comment

by:circa62
ID: 40429195
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?
0
 

Author Comment

by:circa62
ID: 40429209
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?
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
ID: 40429256
> 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.
0
 

Author Comment

by:circa62
ID: 40429280
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
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
ID: 40429416
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
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
ID: 40430293
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:

programmatically embedded PDFs
Regards, Joe
0
 

Author Comment

by:circa62
ID: 40432554
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.
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
ID: 40433241
> 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:

proposed UI
Let me know what you think. Regards, Joe
0
 

Author Comment

by:circa62
ID: 40433271
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.
0
 

Author Comment

by:circa62
ID: 40433318
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.
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
ID: 40433449
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:

large row height
> Let me know how I can try this out.

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

Author Comment

by:circa62
ID: 40433463
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.
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
ID: 40433522
> 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
0
 

Author Comment

by:circa62
ID: 40433550
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.
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
ID: 40433720
OK, let's talk about logistics. I just sent you a note via the EE Message system.
0
 
LVL 51

Expert Comment

by:Joe Winograd, EE MVE
ID: 40435691
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:

updated UI 20141110
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
0
 
LVL 51

Accepted Solution

by:
Joe Winograd, EE MVE earned 500 total points
ID: 40438365
For other members following this thread, there are now two more parameters:

(1) An option to control display of the green progress bar (default is YES).

(2) An option to control if the Operational Statistics are saved in a file (default is NO, in which case they are simply displayed in a MsgBox).

There's also a new choice for the "Excel spreadsheet to use" parameter — "Simulation run", which means that all of the processing takes place, except that the Excel spreadsheet is neither used nor created. The new UI looks like this:

BOPPER Build 20141112.1225 UI
Also, the program now has a name: Bulk Object Population of PDFs in an Excel spReadsheet (BOPPER). I admit that the "R" in spReadsheet is a bit of a stretch, but I like the acronym. :)  Regards, Joe
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Microsoft Office 2010 is Microsoft's latest edition to their office productivity collection. It "promises to include rich and powerful new ways to deliver your best work". So to start off, I have to say that I'm slightly disappointed... That bei…
Lately there has been a variety of news related to U.S. employment.  Stories about worker productivity, automobile and airline unions, low employment and foreign laborers have frequented the news.  Each story has good and bad attributes we might arg…
The viewer will learn how to make their project stand out over others by learning how to change colors and shapes, add spaces, change directions, and add bullets to their charts.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now