creating a pdf cataloger in excel

[peter renton]

Hi

I am wondering if the following is possible. i would like to create a Coshh Register workbook with various tabs i.e brake fluid, antifreeze, oils etc and insert embedded pdf documents of data sheets and risk assessments, that could the be searched by name or category etc and then the pdf could be selected and opened and printed if required.Or can searching embedded docs not be done?

Or can you think of a way to achieve this ?

i tried to upload a small sample with the pdf embedded but it failed to load.


Hope this makes sense

Regards

Peter

[Zer0Cool]

There are many aspects to this, with different considerations changing the answer.

The first is, should you do this in Excel?
Personally this sounds like a much better job for Access than Excel. Excel isn't a database no matter how much people want it to be. Its meant for dealing with tabular data on sheets. Just because we can insert and embed objects doesn't mean it should be done frequently or that it works well in Excel. Access is much more stable for storing content like other files within it.

The second is, if we consider Excel, are we doing something in Excel that cant/isnt already done someplace else?
Usually this surprises people as they don't stop and think that they are just re-creating something that already exists. What you really are describing, at a base level, is a file browser (like Windows/File Explorer in Windows). It can already do much of what you ask about, and in some cases more than you can reasonably get Excel to do. File Explorer can: Display thumbnails of the PDF's, sort them based on any file metadata (default or custom metadata), search within each file without opening them using Windows search, open a file, print a file (even without opening it). So after all that what does Excel offer that cant simply be done by just placing them all in a shared drive with indexing on and the PDF preview extension required for thumbnails?

The third is, if we use Excel, whats the best way to present and implement the desired results?
To answer a question of yours direct, as far as I know Excel cannot search within embedded documents. You can obviously embed PDF's as objects (really large thumbnails) which will give a preview of the file and allow opening it from Excel.

The problem with embedding is the file size will quickly get out of hand, as each PDF is stored within the Excel file itself. Excel can do this, it doesnt mean its good at it. The more files you embed the more likely it is to crash or become corrupted, possibly losing any work done.

If you go this route, I would use Excel more like a directory/table of contents rather than a catalog. That is to say link to the PDF's instead of embedding. You can still associate content in Excel with the PDF, but the PDF is not stored in the file. The file will remain a reasonable size and should be stable. The downside is the PDF's need to be in a place the person clicking the link can access, otherwise it wont work.

My final thoughts...I would recommend Access for this, its the best tool for the job. 2nd best option, just use a shared network drive and File Explorer. If you must use Excel I highly recommend links vs embedding.

[Logit]

.
There are many ways to accomplish your goal. You can do it in Excel, as well as other formats.

The attached demonstrates how to use a macro to insert an image into a worksheet. It also demonstrates how to simply copy and insert an image (the dog pic).

You could create a PDF for each document and save those in a folder. Then on the linked worksheet provided a clickable link or button to print that document. Or, you
could insert the image of the document into the worksheet, just like the dog pic, and make the entire sheet printable.

As far a being searchable ... there are means to search the Sheet Tab Names ... as well as Terms within the worksheets. You can create a main menu with hyperlinks to the
various sections and make the menu as detailed as you need it to be. The link would take the user directly to the sheet and location on the sheet as well.

No matter which way you go, it will take some work to accomplish. But the finished product will be worth it.


I had to store the example workbook on my Amazon cloud account for download due to the size (the images added a lot to the size of the workbook).

Download : https://www.amazon.com/clouddrive/sh...ZM8DOITwLdkbAe

[peter renton]

Thanks Guys

i think i will look for other solutions ;-)

Regards

Peter