VBA code to open URL

[mckaya100]

Hi,

I need VBA code that will open a URL site when someone opens the Workbook.
I want to be able to track who uses the work book.

e.g.

When the user opens the workbook, It triggers the url site.
The site will then record user details (unknown to the user)

I will be using this information to analyse the way my reports are being used.

I have found code for button commands but this is not the same.

Has anyone been able to do this or something similar?

Thanks,

Adam

 

[Ruddles]

Create a new general code module and paste this code into it:-

Option Explicit
 
Public Sub NavigateToURL(ByVal argURL As String)

  Const READYSTATE_COMPLETE As Integer = 4

  Dim objIE As Object
  
  Set objIE = CreateObject("InternetExplorer.Application")

  With objIE
    .Visible = False
    .Silent = True
    .Navigate argURL
    Do Until .ReadyState = READYSTATE_COMPLETE
      DoEvents
    Loop
  End With

  objIE.Quit
  Set objIE = Nothing
  
End Sub

Then in the ThisWorkbook module, create a Workbook_Open event and call the code like so:-

Call NavigateToURL("http://www.whatever.com/track.php")

You can change .Visible = False to .Visible = True and comment out the lines objIE.Quit and Set objIE = Nothing just whilst testing, but change them back again for the live version so that the user's unaware this is all happening.

Note: this solution will only work if macros are enabled.

 

[mckaya100]

Thanks for the code.

It helps alot.

 

[wjvz65]

Hi, thx for the code. Very helpfull.
Have you also the track.php code?
Thank you in advance.
w.

 

[reyes]

Create a new general code module and paste this code into it:-

Option Explicit
 
Public Sub NavigateToURL(ByVal argURL As String)

  Const READYSTATE_COMPLETE As Integer = 4

  Dim objIE As Object
  
  Set objIE = CreateObject("InternetExplorer.Application")

  With objIE
    .Visible = False
    .Silent = True
    .Navigate argURL
    Do Until .ReadyState = READYSTATE_COMPLETE
      DoEvents
    Loop
  End With

  objIE.Quit
  Set objIE = Nothing
  
End Sub

Then in the ThisWorkbook module, create a Workbook_Open event and call the code like so:-

Call NavigateToURL("http://www.whatever.com/track.php")

You can change .Visible = False to .Visible = True and comment out the lines objIE.Quit and Set objIE = Nothing just whilst testing, but change them back again for the live version so that the user's unaware this is all happening.

Note: this solution will only work if macros are enabled.

Ruddles, that's a great solution but I'm curious about the READYSTATE_COMPLETE. What is it for, and what does it do? Why set it to '4'?

I'm a bit of a newbie and before using code, I make sure I understand it!

I don't really get the Do bit fully at the moment but I think I'd 'be insulting you asking about that!

 

[Ruddles]

Ruddles, that's a great solution but I'm curious about the READYSTATE_COMPLETE. What is it for, and what does it do? Why set it to '4'?

I'm a bit of a newbie and before using code, I make sure I understand it!

I don't really get the Do bit fully at the moment but I think I'd 'be insulting you asking about that!

READYSTATE_COMPLETE is a 'constant' - a name for a bit of memory which holds a value which cannot change. It's similar to a variable which you set up with DIM, but you can change the value of a variable. It's set to 4 because later on we're going to use the constant instead of 4.

ObjIE refers to (points to) our instance of Internet Explorer. By using the 'objects' in the IE model, we can find out what IE is doing. .ReadyState is such a method, one which tells us whether IE is in the process of loading a page. When .ReadyState equals 4, we know IE has loaded the page we've asked for. After issuing the .Navigate command (or 'control') we have to wait for the page to load: the value in .ReadyState is how we do that.

Have I explained it clearly?

Regards,
R.

 

[reyes]

Perfectly clear Ruddles. Thank you very much!

 

[elearningshow]

Hi Mr Excel,

I interviewed you many years ago and great to see you are still keeping up the great work.

I have a question related to this tread.

I have MS Excel file with embedded Macros file name: Myfile.xlsm

I am using: MS Excel 2013

I have placed the code: Microsoft Object ThisWorkbook
Option Explicit

Public Sub NavigateToURL(ByVal argURL As String)

Const READYSTATE_COMPLETE As Integer = 4

Dim objIE As Object

Set objIE = CreateObject("InternetExplorer.Application")

With objIE
.Visible = False
.Silent = True
.Navigate argURL
Do Until .ReadyState = READYSTATE_COMPLETE
DoEvents
Loop
End With

objIE.Quit
Set objIE = Nothing

End Sub


Call NavigateToURL("http://myurl.com/cgi-bin/excelcounter/excel.pl")

I can not seem to get it to work? Please advise.

Thanks,

Kevin

 

[elearningshow]

I was able to get a basic counter working using another example from your forum:

Private Sub Workbook_Open()
internetlogon
End Sub

Sub internetlogon()
With CreateObject("InternetExplorer.Application")
.Navigate "http://myurl.com/cgi-bin/excelcounter/excel.pl"
Do Until .ReadyState = 4
DoEvents
Loop
DoEvents
With .Document
'.items("username") = "brazenmore"
'.items("usernamesend").submit
'IE.Document.Forms(0).fGetUserName().Value = "me"
'IE.Document.Forms(0).submit
End With
End With
End Sub

However it would be great to pass more variables back to the PERL Script.

I attempted to use:

'.items("username") = "brazenmore"
'.items("usernamesend").submit
'IE.Document.Forms(0).fGetUserName().Value = "me"
'IE.Document.Forms(0).submit

I get a compile error any tips would be great.

Kevin

 

[geoffm]

I find the most simple is

    shell "explorer.exe URL"

This also works to open local folders.