Solved

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

Posted on 2013-01-27
5
937 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
  • 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now