Vista has broken my SHDocVW

[TimP58]

I have a spreadsheet which I wrote some time ago for checking my
lottery results. The code is shown below and has worked perfectly on
a number of XPSP2 machines for quite some time. I have recently
upgraded my home PC to Vista 64 SP1 and installed my copy Office 2003
on it. Now when I try to run the VBA I get various errors when using
the SHDocVW functions

Sub GetResults()

Dim oIE As SHDocVw.InternetExplorer
Dim sPage As String
Dim SearchStrings As Variant
Dim SearchElements As Integer
Dim sPageLen As Variant
Dim DrawNo As Integer
Dim LatestResults(4) As Variant
Dim j As Variant
Dim i As Integer
Dim k As Integer
Dim NoOfPlayers As Integer
'
StartTime = Now
'
' Routine counts the number of players in the syndicate
'
i = 7 ' First row number with player's name in
k = 2 ' Column number that player's names are in
Do Until IsEmpty(Cells(i, k))
i = i + 1
Loop
'
NoOfPlayers = i - 7 ' Row number with last player's name in
'
Set oIE = New SHDocVw.InternetExplorer
'
' Define the search strings in the web page to get to where the
results start.
'
SearchStrings = Array("Draw History table", "Jackpot", "Prizes", "TD",
_
"<TD>", "<TD>", "class=redText", "view", "odd", _
"TD>")
SearchElements = 10
'
'Range("a1:b20").Clear ' This code was used for development and has
been left for future maintenance
'
oIE.Navigate "http://www.national-lottery.co.uk/player/p/results/
resultsHistory/resultsHistoryAction.do"
'
' A hidden version of IE is started and fetched from the Internet.
' As this takes a few seconds a progress bar is displayed in the
status bar to
' show that something is going on.
'
Do Until oIE.ReadyState = READYSTATE_COMPLETE
DoEvents
j = j + 1
If j Mod 2500 = 0 Then ProgressBar (j) ' Calls progress
routine every 2.5k clicks
Loop
'
The debugger kicks in on the Do Until oIE.ReadyState =
READYSTATE_COMPLETE line. If I set a break point and hover over the
line I get a message saying the data source has been disconnected. In
addition when the oIE.Navigate command executes it actually opens IE7
on the desktop rather than opening a hidden version as it has done in
the past. I have checked the version numbers of the ieframe.dll and
the copy on my Vista machine is newer but I have IE7 on my XP machines
and the code is fine.

 

[Lynda]

Wild guess here, but I'd put my money on it having to do with Vista's
built-in Big-Brother, er, I mean security features. Firewall or UAC settings,
maybe.

I have a spreadsheet which I wrote some time ago for checking my
lottery results. The code is shown below and has worked perfectly on
a number of XPSP2 machines for quite some time. I have recently
upgraded my home PC to Vista 64 SP1 and installed my copy Office 2003
on it. Now when I try to run the VBA I get various errors when using
the SHDocVW functions

<snip>

 

[TimP58]

Wild guess here, but I'd put my money on it having to do with Vista's
built-in Big-Brother, er, I mean security features. Firewall or UAC settings,
maybe.



<snip>

I have tried running Excel as Adminstrator but it doesn't make any
difference although I haven't tried disabling the UAC yet. Perhaps I
should have a go as web searches haven't come up with any other
answers. I did wonder whether the 64 bit version of shdocvw.dll was
in some way incompatible with the 32 bit excel. I have tried to
browse to the 32 bit version in Tools references but Vista just
ignores the new path.

 

[TimP58]

I have tried running Excel as Adminstrator but it doesn't make any
difference although I haven't tried disabling the UAC yet. Perhaps I
should have a go as web searches haven't come up with any other
answers. I did wonder whether the 64 bit version of shdocvw.dll was
in some way incompatible with the 32 bit excel. I have tried to
browse to the 32 bit version in Tools references but Vista just
ignores the new path.

Just as a footnote to this issue I tried Linda suggestion and turned
UAC off and surprise surprise the code now works fine. I guess it
would be nice to find out how to set the relevant permissions without
the blunderbuss approach of completely disabling the system security
but that's for another day.