Click-through tracking from Excel?

[tmk2006]

I have a worksheet with a few graphic objects hyperlinked to a webpage. Any suggestions on how to track user click-throughs to them?
Thanks,
Todd
tmk2006

[Zack Barresse]

Hello tmk2006, welcome to the board!

If it's an ActiveX control (created from the Controls toolbox) then you can use the worksheet module and link some code to it that will fire everytime it's clicked. This could be used to add a single digit to a cell value or update a text file, whatever you wanted to do.

Does this sound like what you want? If so, please provide as many details as you can about the specifics of how you would like to track and impliment this.

[tmk2006]

Thanks for the welcome and look forward to contributing to this unique forum.

I am developing an excel program and it has code to detect if an internet connection is active or not. There's a portion of the program containing hyperlinks when graphical objects are clicked. People worldwide who have this program will be clicking on these objects or subject areas they're interested in. Tracking click-throughs by running ActiveX code and updating text locally is not going to be helpful because this info needs to be redirected to the web and avoid any tracking files on the user side.

In short, is there a way to nicely integrate excel and the web? where the user clicks something on a worksheet to open up a webpage (incrementing a counter particular website or something, or updating a file on the internet) and then go to the intended website (ex: www.yahoo.com or this website, or whatever the user is interested in ). I have already learned out how to fill out a webform or send an e-mail via CDO and think there's a simpler approach to tracking excel to internet click-throughs. At the end of the month, data can be summarized to identify items or worksheet to URL areas generating the most interest.

Let me know if more details are needed.

Regards,
Todd
tmk2006

[Zack Barresse]

At first I thought you were interested in testing an internet connection, which I whipped out from the library banks, hehe, will post if you want it. Ah, I'll post anyway..

Private Declare Function InternetGetConnectedState Lib "wininet" ( _
ByRef lpdwFlags As Long, _
ByVal dwReserved As Long) As Long

Private Const INTERNET_CONNECTION_CONFIGURED = &H40
Private Const INTERNET_CONNECTION_LAN = &H2
Private Const INTERNET_CONNECTION_MODEM = &H1
Private Const INTERNET_CONNECTION_OFFLINE = &H20
Private Const INTERNET_CONNECTION_PROXY = &H4
Private Const INTERNET_RAS_INSTALLED = &H10

Private Function IsConnected() As String
'originally by Ivan Moala
Dim Ret As Long
Dim Msg As String
'// Retrieve the connection status
InternetGetConnectedState Ret, 0&
'// show the result
If (Ret And INTERNET_CONNECTION_CONFIGURED) = _
INTERNET_CONNECTION_CONFIGURED Then _
Msg = "Local system has a valid connection to the Internet," & _
vbCr & "but it may or may not be currently connected."
If (Ret And INTERNET_CONNECTION_LAN) = _
INTERNET_CONNECTION_LAN Then _
Msg = Msg & vbCr & "Uses a local area network" & _
"to connect to the Internet."
If (Ret And INTERNET_CONNECTION_MODEM) = _
INTERNET_CONNECTION_MODEM Then _
Msg = Msg & vbCr & "A modem is used to connect to the Internet."
If (Ret And INTERNET_CONNECTION_OFFLINE) = _
INTERNET_CONNECTION_OFFLINE Then _
Msg = Msg & vbCr & "Local system is in offline mode."
If (Ret And INTERNET_CONNECTION_PROXY) = _
INTERNET_CONNECTION_PROXY Then _
Msg = Msg & vbCr & "Uses a proxy server to connect to the Internet."
If (Ret And INTERNET_RAS_INSTALLED) = INTERNET_RAS_INSTALLED Then _
Msg = Msg & vbCr & "System has RAS installed."
If Msg <> "" Then IsConnected = Msg
End Function

Sub TestConnectionState()
MsgBox IsConnected
End Sub

Now to control an IE instance via Excel, check out these KB entries:
http://vbaexpress.com/kb/getarticle.php?kb_id=386
http://vbaexpress.com/kb/getarticle.php?kb_id=399

[tmk2006]

Zack,
The VBA internet connection test provided is more elegant and robust than what I'm using now. Thanks!

The first kb entry is something I am already familiar with. The 2nd one is new (excel to automate a login) and looks like a possible solution logging into the website. I may have to think more about how this would work.

I'm now leaning towards to a variation of sending e-mail via CDO method. When the graphical picture is clicked - the user is taken to a webpage and runs macro sending a e-mail that it was clicked. The e-mail app can filter the mail into the appropriate folder or every morning I can run some VBA automation code w/MS Outlook and process these "click-through" into a worksheet counter. Backup option #2 is to integrate some click through tracking URL's and integrate it with a off-the-shelf program.

If some has another creative twist to this, I'd be happy to hear it!

Regards,
Todd
tmk2006

[Zack Barresse]

Another option, if dealing with a Windows OS, is to save the information to the Registry and just update accordingly per click. This would obviously only track the data on each users pc seperately.

Example: http://vbaexpress.com/kb/getarticle.php?kb_id=208