Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Data import problem

3 views
Skip to first unread message

Sara S

unread,
Aug 29, 2007, 2:54:04 PM8/29/07
to
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

ShaneDevenshire

unread,
Sep 27, 2007, 7:21:00 PM9/27/07
to
I should add that the code must be put in the Sheet object for the sheet
where you data is. Press Alt F11 then find the sheet on the left hand side
of the VBE and double click it. Place the code in the sheet that opens up on
the right side.
--
Thanks,
Shane Devenshire

ShaneDevenshire

unread,
Sep 27, 2007, 7:19:00 PM9/27/07
to
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

unread,
Sep 28, 2007, 5:37:03 AM9/28/07
to
Thanks, but since I will be distributing this among our company, I wanted to
get away with any local vba. I wanted to have excel refresh this query in the
same way that it refreshes non xml formatted web queries - automatically.

ShaneDevenshire

unread,
Sep 29, 2007, 12:36:00 AM9/29/07
to
Sara,

There may be a way but I just don't work with XML sorry I can't help.
--
Thanks,
Shane Devenshire

0 new messages