Script to add hyperlink to Excel cell containing text

[John Doe]

Would like to use Windows Script to add a hyperlink to a cell in Excel:

Example:

The cell contains BE001.TXT. I want to turn that entry into a hyperlink to
\\Server06\C$\Program Files\Veritas\NT\Data\BE001.TXT while displaying only
BE001.TXT in the cell.

Can this be done using Windows Scripting?

Please reply to the group -no emails.

Thanks in advance

 

[Guest]

Hi,
No need of a script, you can use the HYPERLINK( path , displayed_text)
funtion:
Say column A contains the file names, eg: a2: BE001.TXT
-In B2: =HYPERLINK("\\Server06\C$\Program Files\Veritas\NT\Data\" & A2, A2)
-Copy/Paste down this cell along the data
- you can even hide column A now if necessary (select column A, then menu
Format > Column > Hide)

Regards,
Sebastien

 

[John Doe]

Here is a portion of my script which runs a report against text file backup
logs and puts them in an Excel Spreadsheet.

ElseIf InStr(s, "Job Log: ") <> 0 Then
objXL.Cells(Row,Column+6).Value = Mid(s, 10) (this enters the job
log file name BEXXX.TXT)

At this point I want to insert the Hyperlink at Row,Column+7 :
\\Server06\C$\Program Files\Veritas\NT\Data\BE001.TXT

I have a variable in my script (BEFolder) that includes the
\\Server06\C$\Program Files\Veritas\NT\Data\ path since the server
changes depending on the report.

So would something like this work?

objXL.Cells(Row,Column+7).Value = (=HYPERLINK ...BEFolder & (Row,Column+7)

Pardon my lack of knowledge in scripting but I am new to scripting.

Thanks Again

 

[Patrick Molloy]

try

objXL.Cells(Row,Column+7).FormulaR1C1 = "=HYPERLINK(""\\Server06\C$\Program
Files\Veritas\NT\Data\"" & RC2,RC2)"

 

[John Doe]

Worked great, thank you very much for the assistance.