[RESOLVED] Split Firstname Lastname to Hyperlink

[tome10]

I have a Userform with a Textbox. I need to Split the Firstname Lastname into a Hyperlink.
Here is the code I have from another Textbox to Hyperlink:

    Option Explicit

        Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
        ByVal lpParameters As String, ByVal lpDirectory As String, _
        ByVal nShowCmd As Long) As Long

    Private Sub CommandButton1_Click()
    
        Dim lngOpenPage As Long
        lngOpenPage = ShellExecute
'This is from a past project
(Application.hwnd, "Open", "http://eic.mycompany.com/eic_drw/qr/" & TextBox1.Text, 0&, 0&, 0&) 'What are the 0& at the end of this?
        'lngOpenPage = ShellExecute
'This is the one I need to execute. I just need the Firstname Lastname split from Textbox1.
(Application.hwnd, "Open", "http://edirectory.mycompany.com/ldap/CorpDirServlet?snMatch=exact&givennameMatch=exact&action=FindEmployee&sn=Lastname&givenname=Firstname&MiddleInitial=&telephoneNumber=
    End Sub

[westconn1]

myarr = split(textbox1)
lastname = myarr(1)
firstname = myarr(0)

assumes names separated by single space

[tome10]

I can't seem to get the link right. Here's what I have.
This is the Original Link:

http://edirectory.mycompany.com/ldap...ndEmployee&sn=lastname&givenname=firstname&MiddleInitial=&telephoneNumber=

Private Sub CommandButton1_Click()
        myarr = Split(TextBox1)
        lastname = myarr(1)
        firstname = myarr(0)
        Dim lngOpenPage As Long
lngOpenPage = ShellExecute(Application.hwnd, "Open", "http://edirectory.mycompany.com/ldap/CorpDirServlet?snMatch=exact&givennameMatch=exact&action=FindEmployee&sn=" & lastname, &"givenname=" & firstname, &"MiddleInitial=&telephoneNumber=", 0&, 0&, 0&)
'disregard just here for reference.        
'lngOpenPage = ShellExecute(Application.hwnd, "Open", "http://eic.vzbi.com/eic_drw/qr/" & TextBox1.Text, 0&, 0&, 0&) 
    End Sub

[westconn1]

middle initial is not taken into account here, if required

are you sure application.hwnd is valid in your excel, it is not in excel 2000

[tome10]

I have Excel 2003. I really don't know what Application.hwnd is. I know the other link that is there for reference works. The Middle initial is not needed. I know if I enter a firstname and lastname in the right position in the link it works.

[westconn1]

msgbox application.hwnd
will show if it is valid
or type application.h
see if hwnd is in the list
otherwise replace with 0

[westconn1]

take out the commas after lastname and firstname.

[tome10]

I did the Application. and hwnd did show up in the list. Is hwnd used to verify the link is valid prior to executing?
Here's what I have with error comments:

Private Sub CommandButton1_Click()
        myarr = Split(TextBox1)
        lastname = myarr(1)
        firstname = myarr(0)
        Dim lngOpenPage As Long
        lngOpenPage = ShellExecute(Application.Hwnd, "Open", "http://edirectory.mycompany.com/ldap/CorpDirServlet?snMatch=exact&givennameMatch=exact&action=FindEmployee&sn=lastname&givenname=firstname&MiddleInitial=&telephoneNumber=   'Errors. Compile Error: Expected: list seperator or ). And interestingly the ' I put at the beginning to write this comment didn't work and vba thinks this comment is part of the link.
        'lngOpenPage = ShellExecute(Application.hwnd, "Open", "http://start.mycompany.com/people/Results.aspx?q=" & firstname & lastname, 0&, 0&, 0&) 'When I try this it Errors on MyArray Variable not Defined
        
        'Example works
        'lngOpenPage = ShellExecute(Application.hwnd, "Open", "http://start.mycompany.com/people/Results.aspx?q=" & TextBox1.Text, 0&, 0&, 0&)
    End Sub

[westconn1]

you did not close the quotes on the link
try like

"http://edirectory.mycompany.com/ldap/CorpDirServlet?snMatch=exact&givennameMatch=exact&action=FindEmployee&sn=" & lastname & "&givenname=" & firstname & "&MiddleInitial=&telephoneNumber="

[tome10]

Alright, it is erroring on: myarr = Split(TextBox1) variable not defined.

[westconn1]

Alright, it is erroring on: myarr = Split(TextBox1) variable not defined.

so define it
dim myarr() as string

[tome10]

ok, I defined it, and now it errors on Lastname not defined. So I tried to Dim Lastname as string.. That didn't work. So I tried Set. It errored saying I can't Set an array..

        Dim myarr() As String
        myarr = Split(TextBox1)
        lastname = myarr(1)
        firstname = myarr(0)

[Hack]

myarr = Split(TextBox1) - I see that you want to split the contents of textbox1, but what character are you doing the split on? (Where is the space in this: lastname&givenname=firstname)Try:

Dim myArr() As String
Dim FirstName As String
Dim LastName As String
myArr = Split(TextBox1, "&")
Lastname = myArr(1)
Firstname = myArr(0)
'this is only here to see what we are getting
'once you are happy with the result, take this
'line of code out
Msgbox myArr(0) & " " & myArr(1)

[tome10]

The array is working now thanks Hack.. But my link is still not working.
Compile Error: Argument not Optional.

Dim lngOpenPage As Long
           lngOpenPage = ShellExecute(Application.hwnd, "Open", "http://edirectory.mycompany.com/ldap/CorpDirServlet?snMatch=exact&givennameMatch=exact&action=FindEmployee&sn=" & LastName & "&givenname=" & FirstName & "&MiddleInitial=&telephoneNumber=")

This works:

lngOpenPage = ShellExecute(Application.hwnd, "Open", "http://start.mycompany.com/people/Results.aspx?q=" & FirstName & " " & LastName, 0&, 0&, 0&)

[Hack]

That is because you are missing parameters on the first one.

Look at the ShellExecute declaration.

Count the number of parameters it is looking for, and then take a look at your code and see how many you are actually passing to it.

[tome10]

Hack, Syntax Error I do not Comprehend..

I think I know your talking about this:

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
        (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
        ByVal lpParameters As String, ByVal lpDirectory As String, _
        ByVal nShowCmd As Long) As Long

And I think your talking about the break points "," / sections in this link.

Dim lngOpenPage As Long
           lngOpenPage = ShellExecute(Application.hwnd, "Open", "http://edirectory.mycompany.com/ldap/CorpDirServlet?snMatch=exact&givennameMatch=exact&action=FindEmployee&sn=" & LastName & "&givenname=" & FirstName & "&MiddleInitial=&telephoneNumber=")

but it makes no sense to me. Can you explain? Sorry I can't afford a real school so you guys are all I have. ;-)

[westconn1]

you need to fill the other parameters

ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, _
ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long

first one is hwnd, 2nd is operation (open) 3rd is file (or shortcut)
the ones you are omitting
4th is parameters, 5th is working directory, 6th is widow type to display (showcmd)

in your previous post the one that works, has 0 for each of the last 3 parameters

[tome10]

Man, the original post where I received that code, I asked what those 0's were for but I never got an answer.. I'll try that..

[tome10]

That did it.. It works! You guys are the Bomb!

[tome10]

Can you guys help me out on another problem?
http://www.vbforums.com/showthread.php?t=590919