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?

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?

Hi,

one more detail that I just learned... it helped me out and who knows,
indirectly it might help you.

in addition tot he Workbook.XmlImport method, there's an XmlMap.Import
method. If I use the latter, I don't get prompted to pick an XML Map.

this works for me with no user interaction:

Sub SetFilter(token As String, filter As String)
'
'
''''''''''
Dim urlStr As String

''''''''''
urlStr = "http://server.compnay.com/api.asp?" & _
"cmd=saveFilter&" & _
"sFilter=" & filter & _
"&token=" & token

With Workbooks("Destination Workbook.xlsm")
.Activate
Worksheets("Destination Sheet").Activate

.XmlMaps("setFilterRespMap").Import urlStr
End With

End Sub

"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?