Automate Links

Ben_24 

Automating the creation of links in Excel based on specific data in a column is possible using OfficeScripts. Here's an example of how you could automate this process:

Let's assume that the job numbers in the format xx-xx-xx are in column A.

OfficeScripts Example:

javascript code is untested, please backup your file first.

function main(workbook: ExcelScript.Workbook) {
  // Get the active worksheet
  let sheet = workbook.getActiveWorksheet();

  // Get the used range in column A
  let range = sheet.getUsedRange(true);
  let jobNumbers = range.getColumn(0); // Assuming job numbers are in column A

  // Loop through each cell in the range
  for (let i = 0; i < jobNumbers.length; i++) {
    let jobNumber = jobNumbers[i][0]; // Assuming data is in the first column

    // Construct the file path based on the job number
    let filePath = "path_to_your_server_folder/" + jobNumber + "/"; // Adjust the file path accordingly

    // Create a hyperlink
    let cell = range.getCell(i, 0);
    cell.setFormula(`=HYPERLINK("${filePath}", "${jobNumber}")`);
  }
}

This script gets the job numbers from column A and constructs a file path based on the job number. Then, it creates a hyperlink in the adjacent cell in the same row, linking to the respective job's folder on your server.

Before using OfficeScripts, ensure that the correct paths and formatting are used according to your server's structure. Also, make sure that OfficeScripts is enabled and that the necessary permissions are in place to access the server folders.

Feel free to adjust the script based on the actual column location of your job numbers and the file path to match your server's structure. The text and the code was created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.