Open a site in excel using ActiveWorkbook.FollowHyperlink

Question

So this is basically what I'm trying to do. I have employee column # in a file that is generated from MSSQL. I want to create a function in the cell where the URL will be, http://www.someplace.com/employee.php?ID=Employee#FromCell

So far, all the examples that I have found are not detailed enough for me to figure out what to do with it. I know that this is not right, but that is what I still turned out to be

Function openurl(strSKU As String) ActiveWorkbook.FollowHyperlink Address:="http://www.someplace.com/employee.php?ID=?strSKU=" & strSKU, NewWindow:=True End Function 

I think I'm mixing methods with functions, but I'm not sure where to go. I basically want to add it as a function to make it easier to insert into a column.

Answer 1

You can do it without VBA. You can use the formula.

 =Hyperlink("http://www.someplace.com/employee.php?ID="&A1,A1) 

Where A1 will have an employee id.

Mark this post I made about creating hyperlinks from external data:

http://www.spreadsheetsmadeeasy.com/creating-hyperlinks-with-external-data/

For more information, go to the "Add Hyperlinks" section.

Answer 2

I see that someone has provided you with a job for this, but I will give you the method you are asking (just in case). FYI intellisense sucks in VBA when referencing OLE objects (i.e., Some methods may not appear in button objects, but they do).

The script below will create the buttons for you automatically and send the user to the site you specified when you clicked. ** I have included notes that explain what each line does.

This creates buttons in columns B and gets the URL parameter from column A:

 Sub CreateButtons() Dim btn As Button 'Create a variable for our button Application.ScreenUpdating = False 'Speed up the process by disabling ScreenUpdating ActiveSheet.Buttons.Delete 'Delete existing buttons. Dim Report As Worksheet 'Create our worksheet variable. Set Report = Excel.ActiveSheet 'Set our worksheet to the worksheet variable. Dim t As Range 'Create a variable for the cells we will reference. For i = 1 To Report.UsedRange.Rows.Count 'This will loop through each row in the used range of our worksheet. If Report.Cells(i, 1).Value <> "" Then 'If the value of the first cell is not empty, then do the following... Set t = Report.Range(Cells(i, 2), Cells(i, 2)) 'Assign the cell in the second column of the current row to the cell variable. Set btn = Report.Buttons.Add(t.Left, t.Top, t.Width, t.Height) 'Create a button and place it in the cell in the second column. With btn .OnAction = "openurl" 'Set the button to trigger the openurl sub-routine when it is clicked. .Caption = Report.Cells(i, 1).Value 'Set the caption of the button to equal the value of the cell in the first column. .Name = i 'Set the name of the button to equal the row on which it resides. This name will be used in the openurl sub; So don't change it. End With End If Next i End Sub 

---

This is a macro executed when the user clicks a button:

 Sub openurl() Dim Report As Worksheet 'Create a variable for the worksheet Set Report = Excel.ActiveSheet 'Assign the worksheet to our variable Dim i As Integer 'Create a variable for our row number i = Application.Caller 'Assign name of the button to our row number. Dim address As String 'Create a variable for our address address = "http://www.someplace.com/employee.php?ID=?strSKU=" & Report.Cells(i, 1).Value 'Assign the URL to our address variable. ActiveWorkbook.FollowHyperlink address:=address, NewWindow:=True 'Send the user to the URL you specified (with the URL parameter at the end). End Sub 

---

BONUS INFORMATION:

Follow the next step to automatically complete the entire process for you:

When you say that current data is populated from an MSSQL database, you probably mean that you are retrieving data in Excel using another routine or VBA function. If so, then if you put the script to call the "CreateButtons ()" routine after the script that retrieves the data, this whole process will be performed automatically. Example:

 Sub getEmployeeData() 'This represents your sub that pulls your data from MSSQL '================================================================ 'This represents your script to get your information into Excel. '================================================================ Call CreateButtons 'This runs the CreateButtons() subroutine. End Sub 

Enjoy it!

Answer 3

This is what worked for me:

 ActiveWorkbook.FollowHyperlink Address:=URL 

But I had to take www. from the URL to how it works. If I didn’t do this, I would get an exception from the server. Therefore, instead of:

 "http://www.myaddress.com" 

I used:

 "http://myaddress.com"