Excel & Hyperlinks

[woter324]

Hi,

I have a VBScript that produces a report in Excel from Active Directory. I'm trying to create a hyperlink for the username. When clicked, it opens the relevent page in our staff phonebook.

I've managed to get a hyperlink, but the link is the same for each user. I guess I don't fully understand the use of 'with'.

Here is the code thus far:

Function CreateSpreadSheet(strFileName)
Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Add()
' Bind to Excel object.
'On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If (Err.Number <> 0) Then
    On Error GoTo 0
    Wscript.Echo "Excel application not found."
    Wscript.Quit
End If
On Error GoTo 0

' Create a new workbook.
objExcel.Workbooks.Add

' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "AD Users"

' Populate spreadsheet cells with user attributes.
objSheet.Cells(1, 1).Value = "User Name"
objSheet.Cells(1, 2).Value = "First Name"
objSheet.Cells(1, 3).Value = "Surname"
objSheet.Cells(1, 4).Value = "Job Title"
objSheet.Cells(1, 5).Value = "Department"
objSheet.Cells(1, 6).Value = "Directorate"
objSheet.Cells(1, 7).Value = "Manager"
objSheet.Cells(1, 8).Value = "Account Creation Date"
objSheet.Cells(1, 9).Value = "Employee ID"

' Format the spreadsheet.
objSheet.Range("A1:I1").Font.Bold = True

iRow=1 ' Miss first row as this is the header.
iColumn=0
'objSheet.Visible=True

For Each aKey In processUsers
	'tmpArray=Split(processUsers(aKey),"|")
	'WScript.Echo aKey
	strContents = Split(aKey,"|")
	tmpArray = strContents
	
	iRow = iRow+1
	iUserName 	= tmpArray(0)
	iFirstName 	= tmpArray(1)
	iSurname 	= tmpArray(2)
	iJobTitle	= tmpArray(3)
	iDepartment	= tmpArray(4)
	iDirectorate= tmpArray(5)
	iManager	= tmpArray(6)
	iCreation	= tmpArray(7)
	iEmpID		= tmpArray(8)
	
	
	objSheet.Cells(iRow, iColumn+1).Value = iUsername
	objSheet.Cells(iRow, iColumn+2).Value = iFirstName
	objSheet.cells(iRow, iColumn+3).Value = iSurname
	objSheet.cells(iRow, iColumn+4).value = iJobTitle
	objSheet.cells(iRow, iColumn+5).value = iDepartment
	objSheet.cells(iRow, iColumn+6).value = iDirectorate
	objSheet.cells(iRow, iColumn+7).value = iManager
	objSheet.cells(iRow, iColumn+8).value = iCreation
	objSheet.cells(iRow, iColumn+9).value = iEmpID
Next

Set objRange = objSheet.UsedRange
Set objRange2 = objExcel.Range("C2")
Set objRange3 = objExcel.Range("A:A")
objRange.Sort objRange2,1,,,,,,1

With objExcel.Range("A:A")
	.Hyperlinks.Add objRange3, "[URL unfurl="true"]http://svr-testintra:81/PeopleUpdate/Default.aspx?TabKey=0&TaskItemKey=2&Screen=1&SummaryFilter=(samaccountname%3d"[/URL] & iUserName & "*)"
End With


blar blar...

If anyone could point me in the right direction I'd be most grateful.

 

[HughLerwill]

I think you should be adding the HyperLinks in a loop for each user in turn. To get template code for that go into Excel and record the required Macro code for creation of one Hyperlink; then put it in a loop.

 

[markdmac]

Here is a little sample code that you could alter to get your desired result. As Hugh pointed out you would want the hyperlink insert code to go inside the ForNext so the value of iUserName changes.

Dim objExcel, sLinkPrefix, sLinkPostfix    

[green]'Add this section above your for next[/green]
[red]
sLinkPrefix = "[URL unfurl="true"]http://svr-testintra:81/PeopleUpdate/Default.aspx?TabKey=0&TaskItemKey=2&Screen=1&SummaryFilter=(samaccountname%3d"[/URL]
sLinkPostfix = "*)" [/red] 

[green]'Section in blue can be ignored, this is just simpler code to duplicate what you have in your code already[/green]
[blue]
Set objExcel = WScript.CreateObject("Excel.Application")  
objExcel.Visible = TRUE  
objExcel.Workbooks.Add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
objSheet.Name = "AD Users"
iUserName = "JohnSmith"
[/blue]


[green]'Add this part inside your ForNext
' Be sure to change the range part to where you want the URL[/green]
[red]
objSheet.Range("A1").Select  

URLtext = sLinkPrefix & iUserName & sLinkPostfix
objSheet.Hyperlinks.Add objExcel.Selection, URLtext,,,iUserName [/red]

I hope that helps.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.