Opening files on the web

[DKS]

Hi,

I have a VB routine to open files available on the web. I have a means to
get the address of the file that interests me, and then I open that url.
However, each time I do that, just before opening the file I get a
pop-question if I would like to open the file - with as possible options OK
or Cancel.

I want to avoid getting this pop-up question, meaning that the file is
ALWAYS opended when I execute my routine. How can I do that?

In case my question did not make sense then here is a sample code, try
running it and you will see that a question on whether to open the file or
not is asked. It is this question that I would like to avoid when I execute
my routine. (Alternately, if there is a means to download and save this file
then I can use that, and once saved I can open the file).

Many thanks in anticipation.

Private Sub OpenURL()
Dim st1 As String
st1 = "http://www.projectservertraining.com/download/vba00.pdf"
ActiveWorkbook.FollowHyperlink st1
End Sub

 

[r]

Sub IE_Follow ()
Dim myIE As Object
Set myIE = CreateObject("InternetExplorer.Application")
Const READYSTATE_COMPLETE As Long = 4
Const myURL As String =
"http://www.projectservertraining.com/download/vba00.pdf"
myIE.navigate myURL
myIE.Visible = True
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[DKS]

Hi R,

Thanks for this routine. Works fine for pdf file. Nevertheless for .xls
and .doc I am getting similar error, the system asks me to confirm if I want
to open or save or cancel. Any way to get around?

For example: here is an excel file url
http://www.exinfm.com/excel files/Balanced_Scorecard_Templates.xls

Many thanks in anticipation.

 

[r]

Sub IE_Follow()
Dim myIE As Object
Set myIE = CreateObject("InternetExplorer.Application")
Const READYSTATE_COMPLETE As Long = 4
Const myURL As String =
"http://www.exinfm.com/excel files/Balanced_Scorecard_Templates.xls"
myIE.navigate myURL
myIE.Visible = True
'Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
'DoEvents
'Loop
End Sub

Sub open_xls()
Workbooks.Open
"http://www.exinfm.com/excel files/Balanced_Scorecard_Templates.xls"
End Sub

regards
r

Il mio ultimo lavoro ...
http://excelvba.altervista.org/blog/index.php/Excel-VBA/UsedRange-eccezioni-e-alternative.html

 

[eliano]

Hi R,

Thanks for this routine.  Works fine for pdf file.  Nevertheless for ..xls
and .doc I am getting similar error, the system asks me to confirm if I want
to open or save or cancel.  Any way to get around?

For example: here is an excel file urlhttp://www.exinfm.com/excel%20files/Balanced_Scorecard_Templates.xls

Many thanks in anticipation.

Hi all.
If IE is on, try to open file directly;

Public Sub prova()
Workbooks.Open "http://www.exinfm.com/excel files/
Balanced_Scorecard_Templates.xls"
End Sub

Regards
Eliano

 

[DKS]

Thanks R and Eliano.

For my understanding: what do you mean Eliano when you say if IE is on?
Does it mean that i must have at least one IE window open?

 

[eliano]

Thanks R and Eliano.

For my understanding: what do you mean Eliano when you say if IE is on?  
Does it mean that i must have at least one IE window open?

Hi DKS.
If you are on line you can open the file directly; otherwise you can
use
the "r" solution that first open IE and then open the file.
Try this version that open IE and navigate Google and then open your
file:

Sub IE_Follow() ' by Mr. r
Dim myIE As Object
Set myIE = CreateObject("InternetExplorer.Application")
Const READYSTATE_COMPLETE As Long = 4
Const myURL As String = _
"http://www.exinfm.com/excel files/Balanced_Scorecard_Templates.xls"
myIE.navigate "http://www.google.it"
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
Workbooks.Open myURL
End Sub

Regards
Eliano