Solved

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

Posted on 2013-01-27
5
950 Views
Last Modified: 2013-02-09
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
0
Comment
Question by:Ryan45
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 19

Expert Comment

by:Ken Butters
ID: 38856520
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
 
LVL 45

Expert Comment

by:aikimark
ID: 38857415
@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
 
LVL 45

Expert Comment

by:aikimark
ID: 38857433
@Ryan45

Unfortunately, I was unable to reproduce your DTD error.  My import works just fine.
0
 

Author Comment

by:Ryan45
ID: 38857900
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
 
LVL 19

Accepted Solution

by:
Ken Butters earned 500 total points
ID: 38860284
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question