Monday 12 March 2018

Find and replace all hyperlinks in excel workbook

using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Excel = Microsoft.Office.Interop.Excel;
using System.Drawing;

namespace xlUpdate
{
    class Program
    {
        static void Main(string[] args)
        {
            string folderpath = "d:\\Editted_old";
            DirectoryInfo di = new DirectoryInfo(folderpath);
            FileInfo[] files = di.GetFiles("*.xlsx");
            foreach (FileInfo singleFile in files)
            {
                Excel.Application oExcel = new Excel.Application();

                //specify the file name where its actually exist 
                string filepath = singleFile.FullName;

                //pass that to workbook object 
                Excel.Workbook WB = oExcel.Workbooks.Open(filepath);

                object missing = System.Reflection.Missing.Value;
                // statement get the workbookname 
                string ExcelWorkbookname = WB.Name;

                // statement get the worksheet count 
                int worksheetcount = WB.Worksheets.Count;
                for (int ws = 1; ws <= worksheetcount; ws++)
                {
                    Excel.Worksheet wks = (Excel.Worksheet)WB.Worksheets[ws];

                    // statement get the firstworksheetname 

                    int linkCount = wks.UsedRange.Cells.Hyperlinks.Count;

                    for (int i = 1; i <= linkCount; i++)
                    {
                        object index = (object)i;
                        string currLink = wks.UsedRange.Cells.Hyperlinks[i].Address;
                        string TTD = wks.UsedRange.Cells.Hyperlinks[i].TextToDisplay;
                        if (currLink == "https://www.google.co.in")
                        {
                            wks.UsedRange.Cells.Hyperlinks[i].Address = "https://www.google.com";
                            wks.UsedRange.Cells.Hyperlinks[i].TextToDisplay = "Google";
                        }
                        else
                        {
                            wks.UsedRange.Cells.Hyperlinks[i].Address = "https://www.yahoo.com";
                            wks.UsedRange.Cells.Hyperlinks[i].TextToDisplay = "Yahoo";
                        }
                    }
                }
                //we are saving the editted file into new folder"Editted" from old folder "Editted_old"
                object UpdatedFile = singleFile.FullName.Replace("Editted_old", "Editted");
                WB.SaveAs(UpdatedFile, missing,
                    missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
                    missing, missing, missing, missing, missing);
                WB.Close(true, missing, missing);
                oExcel.Quit();
            }
        }
    }
}

No comments:

Post a Comment

Featured post

Data connections in Infopath forms

https://www.qdoscc.com/blog/how-automatically-retrieve-current-username-infopath-sharepoint-list-form

Popular Posts