VBA calling a web application that returns XML

[stainless]

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/getfileinfo.aspx?file=\\Server1\myfolder\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>
<LastWriteTime>2002-01-23 02:09:34</LastWriteTime>
<Length>1290</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

 

[Tim]

In the VBE you need to add a reference to "Microsoft XML <version>"

Then you could do something like this:
'**********************
Sub GetFileInfo(FileName)

Const URL As String = "http://localhost/UsersShare/getfileinfo.aspx?file="
Dim msxml As MSXML2.XMLHTTP26

Set msxml = CreateObject("MSXML2.XMLHTTP26")

msxml.Open "GET", URL & FileName, False
msxml.send

Debug.Print msxml.responseXML
End Sub
'**********************

Note: your example xml is not valid - the root tags do not match.

I would not recommend sending no content if the file is not there: better to
*always* send at least a <status></status> node in all cases.

Tim

 

[gimme_this_gimme_that]

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

 

[Tim Williams]

A more complete example using a simplified XML reply

Tim

'********************************
Option Explicit

Sub tester()
GetFileInfo "test.txt"
End Sub

Sub GetFileInfo(FileName)

Const URL As String = "http://localhost/simple_stuff/fileinfo.asp?file="
Dim msxml As New MSXML2.XMLHTTP26

msxml.Open "GET", URL & FileName, False
msxml.send

Debug.Print msxml.responseXML.XML
' Prints (for example):
' <?xml version="1.0"?>
' <response><status>OK</status><filename>test.txt</filename></response>

Debug.Print msxml.responseXML.selectSingleNode("response/status").Text
' prints: OK
Debug.Print msxml.responseXML.selectSingleNode("response/filename").Text
' prints: "text.txt"

End Sub
'********************************

 

[stainless]

Note: your example xml is not valid - the root tags do not match.

Should have been:

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

 

[stainless]

Thanks for your help everyone. Tim especially.

I have taken your code and used it successfully (it appears the
responseText is populated with "File Not Found" when the file is
missing, and thus, I test for this after the xml send command).

Cheers

Mark