drag and drop files onto cells for hyperlinking?

**Tommy1005** · 07-19-2011, 06:22 PM

I had the same task at work, here's what I did that may help you out..

So I had to do this for about 2,000 line items... I uploaded all the files to a FTP server, lets call is www.mywebsite.com/pictures

In my excel file in the first column was a Part Number which was like "23-5673" and luckily every picture I had was named exactly letter for letter after one of the 2,000 part numbers.

So what I did was I used the Hyperlink function.

For example, lets say I needed to make a hyperlink for the drawing "23-5673" Well that drawing is already uploaded to the FTP server as "23-5673.pdf" So in excel I had the follow.

=HYPERLINK("http://mywebsite.com/pictures/"&C9&".pdf",C9)

And in C9 was "23-5673"

That way I could drag it down all 2,000 rows, and it made a hyperlink for everything.

Good luck!

**buhnen** · 07-23-2011, 06:32 AM

Hey Tommy,
This is a great tip! :...) for a next time.
Unfortunately, it can´t be applied to my particular case. The pictures I have can´t be renamed and each of them show several items ( parts of a machine ) which I must indentify individually (this means that most of times, several items of my excel list are are hyperlinked to the same picture)...
But still, thanks for the tip. It will work in future

Robert

**6StringJazzer** · 07-23-2011, 10:28 AM

A macro can add the hyperlink but it can't look at the picture

If you can be more specific about what you would like the workflow to look like I can probably help with a macro. What does your Excel file look like? A list of part descriptions? Would it be helpful to click a button and get a list of jpg files that haven't been linked yet to choose from? Maybe a File Open dialog box where you can see thumbnails of the jpg's and select one?

Can you describe how you would like this to work, and any limitations or constraints? I want to avoid a series of "Try this...", "No, because..." posts.

**buhnen** · 07-23-2011, 05:05 PM

Originally Posted by 6StringJazzer

A macro can add the hyperlink but it can't look at the picture

If you can be more specific about what you would like the workflow to look like I can probably help with a macro. What does your Excel file look like? A list of part descriptions? Would it be helpful to click a button and get a list of jpg files that haven't been linked yet to choose from? Maybe a File Open dialog box where you can see thumbnails of the jpg's and select one?

Can you describe how you would like this to work, and any limitations or constraints? I want to avoid a series of "Try this...", "No, because..." posts.

Thanks a lot for your interest StringJazzer. I´ll try to provide more specific information with the next example:

I have 300 pictures stored within several folders in the company´s server. Each of those are named like IMG_23, IMG_24, IMG_25, ....( I´m not allowed to modify these tags nor the folders´ names)

In each of those images, I have one or several items ( electronic components with its corresponding badge names)

For example in picture IMG_23, I can see 3 of those components with its correspondent badge names (tags) like:

-F112
-Q26
-H23

Now let´s get back to my excel list. I must build a database over an excel book. I have 300 inputs in my list, this is, 300 hundred of those components. Example:
...
-F112 | info A about this item | info B... | info C... | hyperlink to the jpg file in which this item appears| ....|...other cells|
-Q26 | info A about this item | info B... | info C... | hyperlink to the jpg file in which this item appears| ....|...other cells|
-H23 | info A about this item | info B... | info C... | hyperlink to the jpg file in which this item appears| ....|...other cells|
....

So what do I have to do? I must look up every of those 300 items, let´s say for example, the item X112, across all the group of pictures files (to make this example easier, let say that the item appears only in a pictutre. In reality it may appear in several of them). And when I find it, I must create in the correspondent cell, the hyperlink to the jpg where I found it.

I work with two computer screens, so this all would be a quicker task if in one screen I open a window with the folder where I have the jpg´s ( I set the view mode like "big icons" in windows 7, which is really helpful). On the other screen I have my excel list.
What it would be a saving-time solution, is to drag the jpg file and drop it onto a cell, and while doing so, creating automatically the hyperlink...

So, is this possible via Macros?
Or if you have any other idea...

Thanks again for your attention

**6StringJazzer** · 07-25-2011, 04:15 PM

This file will build a catalog of images in a directory you choose. A link to the image plus a thumbnail of the image will be shown. You can bring this up side-by-side with your other workbook and drag & drop the hyperlinks.

Go to Sheet2 for the buttons.

**buhnen** · 07-27-2011, 04:22 PM

Originally Posted by 6StringJazzer

This file will build a catalog of images in a directory you choose. A link to the image plus a thumbnail of the image will be shown. You can bring this up side-by-side with your other workbook and drag & drop the hyperlinks.

Go to Sheet2 for the buttons.

Thanks a lot for your Macro 6StringJazzer. Unfortunately, when I press the Build Catalog button, an error shows up ( "the variable is not defined") . Could you also please make a little example of how is to work with it, for better understanding?
Thanks again,
Robert

**6StringJazzer** · 07-27-2011, 07:20 PM

Sorry, that was an error on my part. Try this.

Press the Build Catalog button. You will be prompted to select a folder. Then the tool will put a file name in each row of column A and a scaled-down image from that file in column B. The image will be stored as the original resolution so you can drag it bigger if you want.