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
Bookmarks