Filling dropdown values from remote xml file

[anjanesh]

Hi

I need to make one cell a drop down. This drop down will be filled with values that is to be retrieved from the net as an XML file. I can figure out how to parse the XML because there are enough functions to do that using .NET but how do I get the url content in the macro ?
Also, is .NET supported in macros ? It would be a lot easier if it is.

Thanks

[Fountainhead]

Hi

I need to make one cell a drop down. This drop down will be filled with values that is to be retrieved from the net as an XML file. I can figure out how to parse the XML because there are enough functions to do that using .NET but how do I get the url content in the macro ?
Also, is .NET supported in macros ? It would be a lot easier if it is.

Thanks

Have you considered creating a reference to Excel in your project then making the calls directly from .Net?

[Tim Williams]

..NET isn't supported in macros: VBA is the only option there.

How many values are in the drop-down? If not too many you could use a
validation list and enter the values directly: if more then you could dump
the values onto a hidden sheet and use a named range in the validation list.

Tim


"anjanesh" <[email protected]> wrote in
message news:[email protected]...
>
> Hi
>
> I need to make one cell a drop down. This drop down will be filled with
> values that is to be retrieved from the net as an XML file. I can figure
> out how to parse the XML because there are enough functions to do that
> using .NET but how do I get the url content in the macro ?
> Also, is .NET supported in macros ? It would be a lot easier if it is.
>
> Thanks
>
>
> --
> anjanesh
>
> Freelance Developer
> ------------------------------------------------------------------------
> anjanesh's Profile:
> http://www.excelforum.com/member.php...o&userid=27132
> View this thread: http://www.excelforum.com/showthread...hreadid=526272
>

[anjanesh]

Fountainhead - Im actually looking for a macro and not using VB .NET and embedding the xls into it. I thought the framework alone would do make .NET macros !
Tim - the dropdown will be taken from a remote xml file which I shall parse and display. This xml file changes over time - that why I wanted to have this macro connect to the net.

[Tim Williams]

There are a number of methods you could use in order to fetch the xmlfile
Eg.

API call
XMLHttp object

Lots of examples on both via google

Eg:

Function GetContent(sURL As String) As String
Dim oXHTTP As New MSXML2.XMLHTTP

oXHTTP.Open "GET", sURL, False
oXHTTP.send
GetContent = oXHTTP.responseXML
End Function


Tim


"anjanesh" <[email protected]> wrote in
message news:[email protected]...
>
> Fountainhead - Im actually looking for a macro and not using VB .NET and
> embedding the xls into it. I thought the framework alone would do make
> NET macros !
> Tim - the dropdown will be taken from a remote xml file which I shall
> parse and display. This xml file changes over time - that why I wanted
> to have this macro connect to the net.
>
>
> --
> anjanesh
>
> Freelance Developer
> ------------------------------------------------------------------------
> anjanesh's Profile:
> http://www.excelforum.com/member.php...o&userid=27132
> View this thread: http://www.excelforum.com/showthread...hreadid=526272
>

[anjanesh]

Thanks Tim fot the XMLHttp example.

I just wish .NET was allowed in excel macros - it makes a lot of things easier.
How abt RegExp in VBA ? I know there is one class in .NET which can do the parsing ? But would it be possible in VBA ?

BTW, does Office 12 by any chance support .NET in macros ?

[Tim Williams]

VBscript has a regexp object you can use from VBA. Eg:

http://authors.aspalliance.com/brett...xpressions.asp

Don't know about Office 12 but I would suspect any language other than VBA
will have to be placed in a dll.

Tim.


"anjanesh" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks Tim fot the XMLHttp example.
>
> I just wish .NET was allowed in excel macros - it makes a lot of things
> easier.
> How abt RegExp in VBA ? I know there is one class in .NET which can do
> the parsing ? But would it be possible in VBA ?
>
> BTW, does Office 12 by any chance support .NET in macros ?
>
>
> --
> anjanesh
>
> Freelance Developer
> ------------------------------------------------------------------------
> anjanesh's Profile:
> http://www.excelforum.com/member.php...o&userid=27132
> View this thread: http://www.excelforum.com/showthread...hreadid=526272
>

[anjanesh]

Thanks Tim for your reply.

Don't know about Office 12 but I would suspect any language other than VBA will have to be placed in a dll.

- the .NET framework is compatible on all version of Windows. Its easier to integrate .NET code (when excel supports it) because no dlls or other files need to be installed / copied - the framework alone will do.