Import XML Problem
I don't see anything different in your XML Import than in some of mine that
worked, so the first thing I would do is look at the URL, since that's the
only thing that would be different. When I'm debugging things that look
perfect by staring at them I try to mix things up by building the data
differently. I would suspect that the string was not correctly formed, so
that's where I would start
To test that theory I would build the URL in a string variable and step
through the macro to see that it is built correctly:
---------------
Dim myUrl As String
myUrl = "http://..._getCPdata?year=2009&&;" ' or whatever your URL
ActiveWorkbook.XmlImport _
URL:= myUrl, _
ImportMap:=Nothing, _
Overwrite:=True, _
Destination:=Range("$A$1")
-------------
I know that's not a definitive answer for you, but maybe it will help.
Now maybe you can help me. I can execute an import like yours, but only
once. the next time I try it I get an error dialog saying I can't "overlap
the destination", even though Overwrite:=true.
To get around that, I generated some XMLMaps to map my imported data to. The
overwrite works but now I always get prompted to select the map, even though
its specified in the method call
I have code like this:
''''''''''
Dim map As XmlMap
''''''''''
Set map = ActiveWorkbook.XmlMaps("LogonResponseMap")
ActiveWorkbook.XmlImport _
&password=woo dnUlik2no", _
importMap:=map
''''''''''
this is an alternative to the way you use XmlImport where Excel builds the
map for you. I don't know why it's prompting me to identify an import map
when it has obviously successfully found one in the set map = call.
any ideas?
thanks
P.
"MelMat" wrote:
I'm attempting to import XML data onto a worksheet in Excel 2003. If I
create
the VBA procedure and run it, it works just fine, as long as don't
close down
the application. I can re-run the import as many times as I like and
have no problems.
However, if I close down Excel, re-open it, then attempt
to run my procedure, I get a run-time error: -2147217376 (80041020).
The
description says, "A string literal was expected, but no opening
quote
character was found."
The code line looks like this (with, of course, a valid address):
ActiveWorkbook.XmlImport URL:= "http://..._getCPdata?year=2009&&;"
_
, ImportMap:=Nothing, Overwrite:=True, Destination:=Range("$A
$1")
I've run out of things to try - anyone have any clue as to what I can
do?
|