Macro creates links for all files in folder. How can I customize where to place the links?

proton, almost there. It worked for row L, the "est" links, with a minor glitch. For most of the widgets, it linked to the right cell... but then again, for most of the widgets there are links to them.
Widget #11 for example, does not have a link, so the macro should have skipped that row and continued applying the links to Widget #12 and going on, since those DO HAVE links.

Also, row K, the "id" links... I checked the coding... For some reason, for that one, it DID NOT WORK.
Perhaps since there are 2 cases under Select Case WidgetType, it only applied the macro to the one closer to the coding "End Select 'WidgetType?

Thanks again proton for your efforts.

I attempted to change the case placements for "id" and "est" but again, only the est links would work. When I would remove the case for "est" all together and replaced the only case left, the "id" case, it still wouldn't work.

Any idea why the "est" case would work but not the "id" case?

I altered the "Case Is" part so many times and I can't get the "id" links to pop up at all, let alone in the right part.

I wonder why it would work for the est links, but not the id links?

Proton, no worries about the delay. THANK YOU very much for your efforts.
I took a look at the sheet. I'm trying to understand some of your instructions.
The Checksheet(2) that you provided looks like in your test scenario things are working.

I'm still trying to get it to work, so stay tuned.

By the way, can't 2 macro's be merged into 1 macro? Or would the coding be too long/exhaustive for what I'm asking?

Proton, I have a question. Tab "Checksheet" has in Column A the list of file/picture links in the files we have uploaded. The file you uploaded Macro wording help(bvj).xlsm‎ has 2 tabs, "Checksheet" and "Checksheet(2)".

When I move my mouse icon to the links in column A of "Checksheet", they display the link path location. When I move my mouse icon to the links in "Checksheet(2)", the links display the link path location of where they are located relative to tab "Checksheet", not relative to the link path location. Was that done on purpose?

Also, when I run the "Create Hyperlinks" Macro, I keep on getting a Run-time error:

Invalid procedure call or argument at the following line:
"IDName = Left(JPGList.Cells(Ctrl + 1, "A").Value, Len(JPGList.Cells(Ctrl + 1, "A").Value) - 4)"

Any idea what that is for?
GetFileNames macro works as you coded it with no errors.
Create_Hyperlinks has the error on the line of coding listed above.

Proton/Ben

I took a look at the coding to see what was causing the error... and I think it got me 1 step closer to my final goal.

The GetFileNames macro had in column B put BOTH the url address for the hyperlink AND the formula for "=LEFT(A1,9)=LEFT(A2,9)", so therefore, when the second macro would start up, it created weird issues.

I do see that the two merged macros that you give in your latest coding post fixes that to put the jpglist checksheet formula in column C instead of column B. I will test that out.

In fixing the column issue, the links work, id & est shots go in their correct position, but now pictures (1), (2), (3), and (4) only go to the est and id columns.

Any word why that may happen?
When I had the run time error, the (#) pictures would go in their correct column, but not the right row.

Proton, I tested it out for another scenario. The widgets were placed in the correct descending order regardless of digits, but because they had 1 less character, one of the links ommited the word "id" in it, and thus, the link doesn't work.

Any way to change some of that line of coding so that it bases the link on the right column based on the word "id" being there rather than the amount of characters?

Ex: The test case I gave you fortunately happened to have 8 characters (7 numbers, and 1 letter).
Another test case I studied the macro for has widgets that have both, 8 characters (7 #'s and 1 letter), and 7 characters (6 #'s and 1 letter). Also, the last letter ends in S. In some cases there are less #'s, and in others cases, there are 2 or 3 letters in the widget id.

Would loading up the excel file on that example be better instead?

Ben, I found an issue with the linking part of the macro. When I made the coding change from post #14, the links now made their way to their proper cell AND with the right link name. Before I delve into what I found in post #16 (different widgets having different amount of character lengths in their name), the links that are currently created don't always necessarily go to the right file.

I did note that the links MOST of the time were one file off.

Ex 1: For the first third of the links, est shot & id picture links were in the right column, but then they switch the rest of the way down the template, where all photos that had "id" in the file name are now placed in the est column although the link name is correct.

Ex 2: Where there are finding pictures (123E(1), 123E(2), 123E(3), etc.), picture (1) will never link to 123E(1). Picture 123E(2) will always link to 123E(3), picture 123E(3) will always link to 123E(4), and 123E(4) will always link to 124E est

Is there a way to create the links BEFORE they get moved to their correct spots in the Checksheet tab?

Again, the coding change I made from your post in post #14 now makes the correct link names AND puts them in the right spots, but at the moment, they aren't linking to the right picture.

Sorry for the delayed post Ben.

Ben, here is the upload you requested.
I made a tab called "Notes" to show you which files are linking to what file name hyperlinks.

Most of the time, the hyperlink name is off by 1 file name, but the hyperlink names are correct and at the right location on the checksheet, they are just hyperlinking the wrong file.

Ben, have you had a chance to look at the file yet?

Proton, in your absence I was tinkering with the file template in a way that the links for (1), (2), (3), and (4) don't need to be in the right place anymore. This is due to the fact that their accuracy is better if done manually (it makes sense to me... big time).

Having that said, the hyperlinking would only need to be for the id and est shots. In your current macro, it ALSO puts the widget numbers on the left column (I don't need that. At the time of your absence it wasn't as necessary, but since you put it in there, I'm ok with it). Since only the "id" and "est" would be needed, could the macro state that if the widget numbers on the left of the spreadsheet don't have an id or est sheet, they skip to the next widget (like they CURRENTLY DO), AND if current links can't be placed somewhere then just have them moved to a new sheet for my re-work? That way I can find out why those links aren't where they are supposed to be. (Usually that is because I named them wrong and once I fix the file names and run the macro again, then it is supposed to put them in the right place).

Proton, I took a look at the new test case you uploaded.

- File links *(1), *(2), *(3), and *(4) now don't create Links.
- The File link NAMES *(1), *(2), *(3), and *(4) gets placed on the Structure Column.
- The est and id links remain with the same issue as the last test case upload WITH the coding change from post #14.... the link NAMES are correct, but they open up a different link.

In your test case, the links appear correct until 1600229E. In that widget, since you have (#) links, the est and id links will not create, just like in your test case.

Proton, I was tinkering with my template, and with the new one that I have...

All I need is (rather than all the components needed to make the current macro work)

est shots and id shot links in the right location, with the right hyperlink name, AND linking to the right file

where I have multiple (#) files...

Ex 1: if I have 1 widget with (1), (2), (3), (4), (5), (6),
then in row, for the corresponding widget id, just to have a 6.

Ex 2: if I have a widget with (1), (2), then the widget row in column N will have a 2.

I don't need the macro to put in the list of all the different widget names in the Structure column, unless it is needed. Let me know what you think.

Thanks Proton.

I seem to be getting an error in the Create Hyperlinks Macro in the following line:

TestName = Left(StrucID.Value, StrucLen)

From there it won't move on.
I can run the getfilenames macro just fine.

I replaced the macro coding you have in your latest upload and replaced it with the coding you have posted up.
After working out the location quirks and the tab name quirks, I get stuck at the following error message:

TestName = Left(JPGList.Range("A1").Value, StrucLen)

Proton, after replacing coding from what you have in your latest file update to what your latest coding post shows, it works MUCH better when...

1. I create a tab called jpg list (I thought the macro getfilenames does that automatically)
2. I first run the getfilenames macro, then I seperately run the createhyperlinks macro.

I did notice though that the hyperlinks were a little off as before. I took a look at the jpg list, and noticed the url paths were one cell off.

In the getfilenames tab, can you have both columns (the file name column, and the url address column) sort seperately so that they are in the same order and open up the correct link?

Once I ran the macros seperately, it hyperlinked MOST of the files. At some point, it gave the the error:

For Each StrucID In NameList
If Left(StrucID, StrucLen) <> TestName Then
RowPointer = RowPointer + 1
StrucLen = InStr(StrucID.Value, " ") - 1
TestName = Left(StrucID.Value, StrucLen) <---- This line of coding was highlighted as having an error.

Ben (or Proton, which do you prefer... I've been calling you both)

Once I sorted the 2 columns in the jpg list tab, it worked much better, but a few items linger...

1. The hyperlinking only goes down 45 rows. A few structures didn't get linked although there were no (#) links.
2. One of the structures has 12 (#) shots. Therefore, in the Findings Pictures, there was to be a 12. Instead, a 9 pops up.

Thanks again for your HUGE help with everything you've done.

Ok. No problem. I just saw your message right now. I don't know why I didn't get an email notifiying me that there was a post reply on the thread.