achortechie
New Member
- Joined
- May 14, 2013
- Messages
- 1
Hi. Hoping VBA programmers out there can help!
I have several spreadsheet tabs in an Excel workbook
-each sheet needs to link to a unique folder of jpegs but hyperlinking done at the row level with EACH row linking up to a particular jpeg. Manual insertion of each link not an option for the following reasons:<o
></o>
· There are thousands of images and manually inserting would not be cost-effective.<o></o>
· If the image folder locations change for whatever reason (we find a more permanent network spot or your server addresses change), the hyperlinks won’t work and relinking all of them would be required.<o></o>
· If we learn how to write the macro once, we can simply update the code for the change and it will save us time.<o></o>
· By knowing the code, we can alter it slightly for other similar projects and save ourselves much time and struggle.<o></o>
<o></o>
I never took a programming course in Visual Basic so was at a loss as to the coding but took a stab at it and got one piece of the code working properly. My code when “played” within the macro-creation tool does automatically insert hyperlinks into every row for that one sheet. However, I cannot get it to auto-increment the hyperlink address each time it inserts so basically every row has a link but they all link to the first jpeg, not to the remaining files (filenames go up by 1 in ascending order). My code utilizes a loop, which gets it to add links to each row one after another but that’s all it does as I’ve said. In the code below you'll see that I successfully got all 980 rows to contain a hyperlink to an image [network path of Z:\Photographs-Complete Collection\0504806000\JPEG\0504806000_001.jpg] but need to bump the 001.jpg to 002.jpg, 003.jpg etc etc. all the way to 980.jpg with each row ascending.
My code so far:
Dim rowTotal As Integer<o></o>
Dim fileNum As Integer<o></o>
fileNum = 1<o></o>
Do <o></o>
ActiveCell.Select<o></o>
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _<o></o>
"Z:\Photographs-Complete Collection\0504806000\JPEG\0504806000_001.jpg" _<o></o>
, TextToDisplay:= _<o></o>
"Click for Image"<o></o>
ActiveCell.Offset(1, 0).Select<o></o>
fileNum = fileNum + 1<o></o>
<o></o>
rowTotal = rowTotal + 1<o></o>
Loop Until rowTotal = 980<o></o>
I have several spreadsheet tabs in an Excel workbook
-each sheet needs to link to a unique folder of jpegs but hyperlinking done at the row level with EACH row linking up to a particular jpeg. Manual insertion of each link not an option for the following reasons:<o
></o>· There are thousands of images and manually inserting would not be cost-effective.<o
></o>· If the image folder locations change for whatever reason (we find a more permanent network spot or your server addresses change), the hyperlinks won’t work and relinking all of them would be required.<o
></o>· If we learn how to write the macro once, we can simply update the code for the change and it will save us time.<o
></o>· By knowing the code, we can alter it slightly for other similar projects and save ourselves much time and struggle.<o
></o><o
></o>I never took a programming course in Visual Basic so was at a loss as to the coding but took a stab at it and got one piece of the code working properly. My code when “played” within the macro-creation tool does automatically insert hyperlinks into every row for that one sheet. However, I cannot get it to auto-increment the hyperlink address each time it inserts so basically every row has a link but they all link to the first jpeg, not to the remaining files (filenames go up by 1 in ascending order). My code utilizes a loop, which gets it to add links to each row one after another but that’s all it does as I’ve said. In the code below you'll see that I successfully got all 980 rows to contain a hyperlink to an image [network path of Z:\Photographs-Complete Collection\0504806000\JPEG\0504806000_001.jpg] but need to bump the 001.jpg to 002.jpg, 003.jpg etc etc. all the way to 980.jpg with each row ascending.
My code so far:
Dim rowTotal As Integer<o
></o>Dim fileNum As Integer<o
></o>fileNum = 1<o
></o>Do <o
></o>ActiveCell.Select<o
></o>ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _<o
></o>"Z:\Photographs-Complete Collection\0504806000\JPEG\0504806000_001.jpg" _<o
></o>, TextToDisplay:= _<o
></o>"Click for Image"<o
></o>ActiveCell.Offset(1, 0).Select<o
></o>fileNum = fileNum + 1<o
></o><o
></o>rowTotal = rowTotal + 1<o
></o>Loop Until rowTotal = 980<o
></o>
