Macro for hyperlink on Word/Excel

I'd like to use a macro in a Word document that runs and a string corresponds to an excel cell, I take the address as a hyperlink.

For example I have a Word document, where I see the string AABB, which corresponds to a single cell in Excel, for example, A1 and in B1, I have the address corresponding to this line (www.google.com for example).

I would therefore like to have link on the word AABB addressed to www.google.com

In addition I do not know if this is feasible, since the string length can vary, for example, I have a little AABB, then a AA3B0, then a Q_AA02.

It should already be seen that how to determine which channels to search.

I suppose, you must begin by using the Excel file, determine the range that contains the pairs "keyword/URL", then scan the Word document, line by line ...

Is this?

Yes, it is better to let the excel file to check if the lines are in the Word document.

So in fact I took the string in a cell, I compare with my Word, and if it finds, it sets the corresponding hyperlink.

But I think it will take a huge time knowing that there are 1000+ and the same word for the number of registration in excel.

Ideas to make it fast enough? and especially for the macro call the documents one after the other (so basically any analysis of any file for example).

Here is the code:

Code:

---

On Error Resume Next
  'Variables declarations
  V_jobName = ""
  V_Stream = ""
  V_Application = ""
  V_NAME_OF_TREATMENT = ""
  V_DESCRIPTION_OF_TREATMENT = ""
  V_DESCRIPTION_FUNCTIONAL = ""
  V_Moment_dexecution = ""
  V_temps_treatment = ""
  V_successcondition = ""
  V_Consequence = ""
  V_Column1 = ""
  V_TASKTYPE = "" 
  V_url = "<A HREF = http://www.google.com/"
  V_url_middle = ""
  V_url_End = "</ A>"
  V_errorManage = ""

  'declaration of the locations of files excels 2
          path_file = "C:\test_vbs\test2.xls"
          path_file_ListJobname = "C:\test_vbs\ListJob.xls"
       
  'load excel
          set ex = Wscript.createobject ("Excel.Application")
          set ex2 = Wscript.createobject ("Excel.Application")
       
  'opens on 1 doc excel to retrieve info
          ex.Workbooks.Open path_file
          ex.visible = false
       
  'opens the 2nd doc excel to retrieve the list of job
          ex2.Workbooks.Open path_file_ListJobname
          ex2.visible = false

  'removes the first line
  'will put all the headers and count the number of columns
                  nbcol = 1

  'Retrieve the number of columns "nbcol"
          While not ex.ActiveSheet.cells (1, nbcol).Value = "" 
                  nbcol = nbcol 1
          Wend
          nbcol = nbcol - 1

  'No initialization of the 1st line address
          nbline = 3

  'on the way on 1 doc
                                       
  While not ex. ActiveSheet.cells (nbline, 1). Value = "" 

                          for i = 1 to nbcol

  we get the data corresponding to each column
                                                               
                                  Select Case i
                                          Case "2" V_Application = ex. ActiveSheet. Cells (nbline, i). Value
                                          Case "3" V_Jobname = ex. ActiveSheet. Cells (nbline, i). Value
                                          Case "6" V_Stream = ex. ActiveSheet. Cells (nbline, i). Value
                                          Case "7" V_TASKTYPE = ex. ActiveSheet. Cells (nbline, i). Value
                                          Case "8" V_NAME_OF_TREATMENT = ex. ActiveSheet. Cells (nbline, i). Value
                                          Case "9" V_DESCRIPTION_OF_TREATMENT = ex. ActiveSheet. Cells (nbline, i). Value
                                          Case "10" V_temps_treatment = ex. ActiveSheet. Cells (nbline, i). Value
                                          Case "11" V_Moment_dexecution = ex. ActiveSheet. Cells (nbline, i). Value
                                          Case "12" V_DESCRIPTION_FUNCTIONAL = ex. ActiveSheet. Cells (nbline, i). Value
                                          Case "13" V_successeurcondition = ex. ActiveSheet. Cells (nbline, i). Value
                                          Case "14" V_Consequence = ex. ActiveSheet. Cells (nbline, i). Value
                                          Case "15" V_Column1 = ex. ActiveSheet. Cells (nbline, i). Value
                                  End Select
                               
       
  'Retrieve the value for the title of the file to create
                                  If i = 3 Then
                                          filename = ex. ActiveSheet. cells (nbline, i). value & '#.htm "                               
                                  End If

  'Create file htm
                                  Set filesys = CreateObject ( "Scripting.FileSystemObject")
                                  Set readfile = filesys.OpenTextFile ("c:\test_vbs\Job.htm",1, false)
                                                  contents = readfile. ReadAll
                                  readfile. close
                               
                                  If filename = "" Then
                                          filename = "Name_Credits.htm"       
                                  End If
                               
                               
  'Treatment of the dynamic creation of domains
                               
                                  nbline2 = 1
                               
  'route on the 2nd excel where the job is stored
                       
                                  While not ex2. ActiveSheet. Cells (nbline2, 1). Value = "" 
                                          file_job = ex2. ActiveSheet. cells (nbline2, 1). value
                                       
                                          If Len (V_successcondition)> 0 Then
                                                  Vx = Mid (V_successcondition, InStr (V_successcondition, file_Job), Len (file_job))
                                                          If file_job Then Vx =
                                                                  V_successcondition = V_url & file_job & V_url_Middle & file_job & V_url_End
                                                          End If
                                                  Vx = ""
                                          End If
                                       
                                          If V_Column1 <> "" Then
                                                  Vx = Mid (V_Column1, InStr (V_Column1, file_Job), Len (file_job))
                                                  If file_job Then Vx =
                                                          V_Column1 = V_url & file_job & V_url_Middle & file_job & V_url_End
                                                  End If
                                                  Vx = ""
                                          End If
                                       
                               
                                          If V_Consequence <> "" Then
                                                  Vx = (Mid (V_Consequence, InStr (V_Consequence, file_Job), Len (file_job)))
                                                  If Then file_job = Vx
                                                                  V_Consequence = V_url & file_job & V_url_Middle & file_job & V_url_End
                                                  End If
                                                  Vx = ""
                                          End If
                                       
               
                                          file_Job = ""
                                          nbline2 = nbline2 + 1 
                                  Wend
                               
  'Replaces the values of Htm file by that recovered in the excel
                               
                                          Set text = filesys. CreateTextFile ( "c:\test_vbs \" &filename)
                                          contents = replace (contents, ""JOBNAME ", V_Jobname)
                                          contents = Replace (contents, "" stream ", V_Stream)
                                          contents = Replace (contents, "" APPLICATION ", V_Application)
                                          contents = Replace (contents, "" Description_of_treatment ", V_DESCRIPTION_OF_TREATMENT)
                                          contents = Replace (contents, "" description_functional ", V_DESCRIPTION_FUNCTIONAL)
                                          contents = Replace (contents, "" Name_of_treatment ", V_NAME_OF_TREATMENT)
                                          contents = Replace (contents, "" Moment_dexecution ", V_Moment_dexecution)
                                          contents = Replace (contents, "" temps_treatment ", V_temps_treatment)
                                          contents = Replace (contents, "" successcondition ", V_successcondition)
                                          contents = Replace (contents, "" predecessor ", V_Column1)
                                          contents = Replace (contents, "" consequence ", V_Consequence)
                                          contents = Replace (contents, "" tasktype ", V_TASKTYPE)
                                          text. Write (contents)
                                  text. close

                          next
                                  nbline = nbline 1
          Filename = ""
          V_Jobname = ""
          V_Stream = ""
          V_Application = ""
          V_DESCRIPTION_OF_TREATMENT = ""
          V_DESCRIPTION_FUNCTIONAL = ""
          V_NAME_OF_TREATMENT = ""
          V_Moment_dexecution = ""
          V_temps_treatment = ""
          V_successcondition = ""
          V_Column1 = ""
          V_Consequence = ""
          V_TASKTYPE = ""
          Wend
       
    MsgBox (V_errorManage) 
       
          ex. Application. DisplayAlerts = False
          ex. quit
       
          ex2. Application. DisplayAlerts = False
          ex2. quit
          msgbox ". html created"

          set ex = Close
          Set ex2 = Close

---