Workbooks.Open(URL) not working

[chris_j_adams]

Hi,

Can anyone suggest a workaround for the following in XL97 SR2 please?
I'm trying to open a workbook from a web download, however, the
workbook will not open if it is invoked from VBA (it is called from a
Workbook_Open event). But it will work if I simply run the code. I
can't find an explanation for this odd behaviour so I'm wondering if
there is a workaround to download a sheet from this excel workbook held
on a web site.

Sub GetDataSheet
strURL = "http://server//SpreadsheetToOpen.xls"
Set myWB = Workbooks.Open (strURL)
End Sub

(btw, there is no other code in the Workbook_Open event and the URL is
very simple with no strange characters).

Many thanks in advance if anyone can help.

Regards,
Chris Adams

 

[Dave Peterson]

Try moving the code from workbook_Open() to Auto_Open() (in a general module).

(Untested)

 

[chris_j_adams]

Dave,

Many, many thanks - that worked a treat..!! I'm awestruck because its
hard to tell how many years of experience went behind that simple,
left-field solution

I've tested your solution on a couple of PCs so far - all worked fine.


Heck of a relief because I'd made a commitment that this opening the
URL workbook was easy stuff anyone could do, but then it blew up over
this illogical problem and I've been sweating all weekend trying to
figure a solution. You've saved my derriere!

Much appreciated again,
Chris Adams

 

[Dave Peterson]

It's been awhile, but I have seen posts like this:

I have seen posts like this that:

Private Sub Workbook_Open()
Application.OnTime Now, "Continue_Open"
End Sub

Then in a general module:

sub Continue_Open()
'your real code here
end sub

Sometimes there would be timing issues that could be avoided this way. (I've
never seen this in real life, though--but others have.)