Macro for hyperlink on Word/Excel

[Mindstorm]

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.

[Raine]

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?

[Mindstorm]

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).

[sergioKomic]

Here is the 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