You might hit the lucky jackpot by having VBA call Applescript (via
the MacScript() Function) and then getting Applescript to read the
XML, then getting Applescript to store the result in a sheet.

You first need to install "XMLLib osax" (from versiontracker.com) to
get install XML functionality for Applescript.

Woo.

Sounds complicated.

This would be easier ...

You could get a Perl script to do the job and have Excel read the
remote information and call Perl from VBA's shell command.

You can also write a Perl script that will look for the file, read it
if it's there, and then insert data from the URL into Excel. (See my
MySql/Excel/Perl post.) You could also have Perl parse the file you
fetch after getting the URL.

Note that if you read the information with Perl you can parse the data
into a Worksheet with SpreadSheet::WriteExcelXML

I was looking at XML::Twig last night, that looks like a the easiest
Perl XML reading tool out there. See http://xmltwig.org .

To

On May 31, 7:31 am, stainless wrote:
Due to some very complicated circumstances regarding firewalls and
current systems, I have been given the task of fixing some VBA code
that needs to identify if a file exists, and this file is behind
firewalls that prevent direct access.

A colleague of mine has written a web application (note: not a web
service) that returns file info for a file on a server in the form of
xml.

e.g. the web application is called UsersShare and the url below will
try and find a file called File1.txt in folder \myfolder\ on server
Server1 (using a getfileinfo function in the C# application code)

http://localhost/UsersShare/getfilei...lder\File1.txt

If the file exists, xml is returned with the details

e.g.

<?xml version="1.0" encoding="UTF-8" ?
- <UsersShare xmlns="http://company.intranet"
- <FileInfo xmlns=""
<FileName\\\Server1\myfolder\File1.txt</FileName
<LastWriteTime2002-01-23 02:09:34</LastWriteTime
<Length1290</Length
</FileInfo
</PegasusUserShare

If not found, there is no xml returned

I need to amend some Excel VBA code to use this web application to
test if such a file exists. In simplest terms, if the length of the
xml returned is 0, then the file exist. However, if it is 0, there
is no file.

The VBA version I am using is using Visual Basic 6.3, and as far as I
can tell, has no XML functions available (unless they are wll hidden).
The reason I believe this is because I tried to access MSXML2 and
these did not exist.

There may be other ways of doing this if the whole process is
rewritten but I need to use this web application

I would be grateful if anyone can tell what code I need to return this
XML using the url and how I determine if the result is populated at
all.

Cheers

Mark