How do I auomate the import of XML into Excel with VBA

Hello Experts,

I am trying to download XML House roll call votes into excel using VBA to automate the process. When I try to run the script on my pc, it produces an error that reads: "DTD is prohibited", which is odd, because the exact same script works fine with the XML Senate roll call votes. My code is as follows...


Sub XML_Import_House()
 

    ActiveWorkbook.XmlImport URL:= _
 "http://clerk.house.gov/evs/2012/roll610.xml", ImportMap:= _
Nothing, Overwrite:=True, Destination:=Range("$A$1")
 
End Sub

how do I get around this error?

Thank you,
Ryan
Ryan45Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ken ButtersConnect With a Mentor Commented:
I was able to reproduce your error.

I downloaded roll610.xml and then removed the following 2 lines from the source XML. the resulting file imported into Excel without error.

<!DOCTYPE rollcall-vote PUBLIC "-//US Congress//DTDs/vote v1.0 20031119 //EN" "http://clerk.house.gov/evs/vote.dtd">

  <?xml-stylesheet type="text/xsl" href="http://clerk.house.gov/evs/vote.xsl"?>

Open in new window

0
 
Ken ButtersCommented:
The Content of http://clerk.house.gov/evs/2012/roll610.xml looks like it is formatted as HTML even though it has an XML extension.

Can you provide the URL of an XML that works ok for you?
0
 
aikimarkCommented:
@buttersk

If you open up the XML in the browser, you will see the result of an XSL transformation of the XML as a result of this line:
<?xml-stylesheet type="text/xsl" href="http://clerk.house.gov/evs/vote.xsl"?>

Open in new window

0
 
aikimarkCommented:
@Ryan45

Unfortunately, I was unable to reproduce your DTD error.  My import works just fine.
0
 
Ryan45Author Commented:
Hello Experts,

Here is an address that functions correctly in my code.

http://www.senate.gov/legislative/LIS/roll_call_votes/vote1131/vote_113_1_00005.xml

The format issue may be the problem.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.