Get stuck in a VBA DO loop

existence of a file on the internet | Herber's Excel forum

The short way to the goal: job programming



Subject: Existence of a file on the internet from: helmut.em
Written on: 03/03/2010 11:49:35 AM

I have already checked the existence of files with fileexists and also with you.
But this time I have to check whether a file exists in an internet directory, in this specific case:
http://www2.uni-jena.de/erzwiss/

reason: the whole thing happens in a loop with many different files. this loop should not get stuck if a file does not exist.
the script should be executable by any people, so I cannot assume any ftp rights.
thanks for your help
helmut


Subject: AW: Existence of a file on the internet by: Ramses
Written on: 03/03/2010 11:56:02 AM

Hello

"... so I cannot assume any ftp rights ..."

Then forget it. EXCEL also has to access the FTP directory when checking and is a user who needs at least reading rights.

By the way: Why do users who have no rights anyway have to be able to check something?

Greetings Rainer



Subject: AW: Existence of a file on the internet by: Martin S.
Written on: 03/03/2010 12:16:27 PM

Hello Helmut,

Excel can control Internet Explorer very well and query results / error messages. The requirement would then be that IE is installed on the respective computers (... without necessarily having to be a standard browser). But since Excel is already a prerequisite ...

If this is of interest to you, look it up in the archive. In the beginning, the code should look something like this:

Dim IEApp As Object
Dim IEDocument As Object
Set IEApp = CreateObject ("InternetExplorer.Application")
IEApp.Visible = False
IEApp.Navigate "http://www.deine- Adresse.de"
Do: Loop Until IEApp.Busy = False
Do: Loop Until IEApp.Busy = False
Set IEDocument = IEApp.Document
Do: Loop Until IEDocument.ReadyState = "complete"

Best wishes

Martin



Subject: AW: Existence of a file on the internet from: helmut.em
Written on: 03/03/2010 1:12:31 PM

thank you martin, i think that helps me.
I don't have the time at the moment, but I'll be able to do it tomorrow. if you still have questions, I'll answer this thread again.
fg from jena helmut



Subject: AW: Existence of a file on the internet by: Martin S.
Written on: 03/03/2010 16:26:11

okay, I'll have another look here tomorrow! ;-)



Subject: AW: Existence of a file on the internet from: helmut.em
Written on: 03/04/2010 10:42:32 AM

In your code, Navigate will try to open the file in any case and that's no alternative, whether it exists or not.

I have now written - probably in the most primitive way - the following:



[code]

Dim IEApp As Object

Dim IEDocument As Object

Dim Text As String

Set IEApp = CreateObject ("InternetExplorer.Application")

IEApp.Visible = False

IEApp.Navigate "http://www2.uni-jena.de/erzwiss/maerchen.txt"

Do: Loop Until IEApp.Busy = False

Do: Loop Until IEApp.Busy = False

Set IEDocument = IEApp.Document

Text = IEDocument.Body.InnerText

Set IEDocument = Nothing

Set IEApp = Nothing

If Text Like "* The web page was not found *" Then MsgBox "File does not exist" Else MsgBox "File exists"

End Sub

[/code]



(the messagebox is now only for testing, in the program the decision is then different.)



But this poses another problem:

when I go through the individual steps with F8, everything works fine in every case. With F5 or after executing the command button, the error message: "Object variable or with block variable not defined" appears at the debug point "Text = IEDocument.Body.InnerText", if the file does not exist. if it exists then everything is ok. how is that possible?



please help again and thank you in advance.

helmut


Contributions from the Excel examples on the subject of "Existence of a file on the internet"