CODEWITHSUNDEEP
excelvbasharepointhyperlinkedit
teesquared
teesquared

Reputation: 1

Mass editing hyperlinks in Excel, replacing parts of the hyperlinks with data in Excel

I'm not too good with Macros or VBA coding but I'd like to use it to solve this problem. I am creating a photo database for a hotel room inventory catalog. I am just using Excel to edit the hyperlinks. The situation is that each room has a specific hyperlink to it's folder of photographs. So technically, each of the line items has almost the same address, except the last few numbers which is it's destination.

For example: The room 301 will have the following hyperlink: http://sharepoint/sites/NYNYRoomInventoryCatalog/Rooms%20By%20Floor/Forms/AllItems.aspx?RootFolder=/sites/NYNYRoomInventoryCatalog/Rooms%20By%20Floor/03/301&

Room 302 is http://sharepoint/sites/NYNYRoomInventoryCatalog/Rooms%20By%20Floor/Forms/AllItems.aspx?RootFolder=/sites/NYNYRoomInventoryCatalog/Rooms%20By%20Floor/03/302&

Room 916 would be http://sharepoint/sites/NYNYRoomInventoryCatalog/Rooms%20By%20Floor/Forms/AllItems.aspx?RootFolder=/sites/NYNYRoomInventoryCatalog/Rooms%20By%20Floor/09/916&

Basically, I just want to replace the 09/916& this part with whatever floor and room number the room is. I have separate columns in my Excel spreadsheet indicating what the room number is.

Column A is the hyperlink, Column B is the room number, and column C is the floor number

Is there a macros code that can generate the hyperlinks for each line item in my spreadsheet

http://sharepoint/sites/NYNYRoomInventoryCatalog/Rooms%20By%20Floor/Forms/AllItems.aspx?RootFolder=/sites/NYNYRoomInventoryCatalog/Rooms%20By%20Floor/FLOOR NUMBER/ROOM NUMBER&

and the floor number and room number will be extracted from the columns. I would do it manually, but there are over 2,000 rooms. I would greatly appreciate any help or direction for this!

Upvotes: 0

Views: 515

Answers (1)

Tim Williams
Tim Williams

Reputation: 166401

In column D (starting in D2):

="http://sharepoint/sites/NYNYRoomInventoryCatalog/Rooms%20By%20Floor/Forms/AllItems.aspx?RootFolder=/sites/NYNYRoomInventoryCatalog/Rooms%20By%20Floor/" & C2 & "/" & B2 & "&"

Fill down to the end of your list of data.

Upvotes: 0

Related Questions