followhyperlink happening twice

[Brian Murphy]

I am using the following statement to run an asp script on my web
site.

ExcelApp.ActiveWorkbook.FollowHyperlink address:=""http://
www.xlrotor.com/internet_update.asp", NewWindow:=True

The above line is actually in a VB6 dll that I call from Excel VBA.
Anyway, the asp script displays a web page and sends me an email. The
page displays in the default browser just as expected, but I get two
emails when this line is called once from Excel.

If I enter the same URL string manually in the browser, I only get one
email. So something goofy is happening with the FollowHyperlink that
I don't understand.

Does anyone know why this would happen?

Thanks,

Brian Murphy
Austin, Texas

 

[AB]

Just a wild guess but maybe your code is in some event code and the
event gets triggered 2 times - like in ws_change event and 2 cells get
changed in sequence...

 

[Brian Murphy]

It's not called from any event procedures. I checked into the
possibility of the code getting executed twice by placing a breakpoint
on it. This confirmed that it's only getting called once.

I'm not 100% sure, but it's possible that every once in a while I only
get one email. But certainly most of the time I get two.

Thanks,

Brian

 

[Peter T]

Putting a breakpoint in the routine can sometimes disguise it being called
twice, particularly from different types of "button" events. Maybe include a
debug line or write to a log file. However I don't think you've given us the
whole picture as your code simply opens a web page, no idea where those
emails are coming from.

I know Excel's FollowHyperlink is tempting for its simplicity even in a VB6
dll, but maybe try something like this -

Public 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
Public Declare Function GetDesktopWindow Lib "user32" () As Long

Sub test()
Dim sURL As String
sURL = "http://www.xlrotor.com/internet_update.asp"

Call ShellExecute(GetDesktopWindow(), vbNullString, sURL, _
vbNullString, vbNullString, vbNormalFocus)
End Sub

Regards,
Peter T



It's not called from any event procedures. I checked into the
possibility of the code getting executed twice by placing a breakpoint
on it. This confirmed that it's only getting called once.

I'm not 100% sure, but it's possible that every once in a while I only
get one email. But certainly most of the time I get two.

Thanks,

Brian

 

[Anders Høgsbro Madsen]

I experience the same problem from MS Access. The URL is sent twice and it is a problem in FollowHyperlink.
It is easy to test that it is not your code that is the problem. Just use the immediate window to execute e.g.:
FollowHyperlink ExcelApp.ActiveWorkbook.FollowHyperlink address:="http://www.mydomain.com/make_a_change.asp
You can also test that it is not the URL that is wrong, by pasting the URL directly in the browsers address field. Brian did that.

I tested using MS Access 2003 with IE7 and using MS Access 2007 with IE8. Both combinations have the problem.

Most people just use FollowHyperlink to show a page and in that case sending the URL twice is not a problem (except for performance). But if you, like Brian and I, are sending a URL that has the effect of changing something somewhere, then it is a big problem if changing twice is not allowed. Especially if the user needs to see the output of the first run - since only the output of the second run is shown.

Using ShellExecute as suggested by Peter T solves the problem.
One small thing: The last argument (vbNormalFocus) seems to be ignored but that probably does not matter.

Regards
Anders Høgsbro Madsen

 

[carpetony]

Thanks for the great tip, I have a Django database that I am trying to update and I get messages posted twice, so this will work great.

My issue is, I am getting a PtrSafe message when I try to use it.

I hole my own with Excel and VBA, but that message and the MSDN is a bit over my head. Does anyone know what I would need to do to get this to work.

Win7 64bit Excel 2010

Thanks.
Tony