Solved

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

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

773 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