Data import problem

External Since: Aug 29, 2007 Posts: 2

I am trying to import the following data from a web query:

<?xml version="1.0"?>
<NewDataSet>
<Body>
<Participant>SARAS</Participant>
<QueryResponse xmlns="http://xxxx.xxx/xml">
<ScheduleOfferSet>
<ScheduleOffer location="3205101" schedule="1" day="20070822"
slope="false">
<OfferSegment MW="50" price="200.8" />
</ScheduleOffer>
</ScheduleOfferSet>
</QueryResponse>
</Body>
</NewDataSet>

Since I would like to be able to auto refresh this query when I change a
parameter elsewhere in the workbook, I do not import it as XML (for some
reason you cannot have excel auto refresh the query if you import it as XML).
However, when I do, the "OfferSegment" tag does not show up in the resulting
dataset.
You can see this by saving the above code into an .xml file and try to
import it, bypassing the option to import as XML (that works just fine).
So I have two questions:
1. How do I get the OfferSegment tag to show up in the xl sheet and
2. Failing that, how can I get Excel to handle a web query that requires it
to format the data into XML AND auto refresh when a cell changes values.
Thanks,
Sara
Sara S

External Since: May 19, 2006 Posts: 2098

Hi,

I haven't worked with XML files but assuming you can refresh manually using
the standard refresh button, you should be able to refresh the data by
writing a VBA routine.

Example:

Private Sub Worksheet_Change(ByVal Target As Range)
Set isect = Application.Intersect(Range("xml"), Range(Target))
If Not isect Is Nothing Then
'"add the recorded refresh method here"
End If
End Sub

As you see I haven't added the code for refreshing an XLM file, but
hopefully you can record it.
--
Cheers,
Shane Devenshire

"Sara S" wrote:

> I am trying to import the following data from a web query:
>
> <?xml version="1.0"?>
> <NewDataSet>
> <Body>
> <Participant>SARAS</Participant>
> <QueryResponse xmlns="http://xxxx.xxx/xml">
> <ScheduleOfferSet>
> <ScheduleOffer location="3205101" schedule="1" day="20070822"
> slope="false">
> <OfferSegment MW="50" price="200.8" />
> </ScheduleOffer>
> </ScheduleOfferSet>
> </QueryResponse>
> </Body>
> </NewDataSet>
>
> Since I would like to be able to auto refresh this query when I change a
> parameter elsewhere in the workbook, I do not import it as XML (for some
> reason you cannot have excel auto refresh the query if you import it as XML).
> However, when I do, the "OfferSegment" tag does not show up in the resulting
> dataset.
> You can see this by saving the above code into an .xml file and try to
> import it, bypassing the option to import as XML (that works just fine).
> So I have two questions:
> 1. How do I get the OfferSegment tag to show up in the xl sheet and
> 2. Failing that, how can I get Excel to handle a web query that requires it
> to format the data into XML AND auto refresh when a cell changes values.
> Thanks,
> Sara
> Sara S
>