[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 92
  • Last Modified:

any soltion create a connection to import XML file from website

I have this php website link  

previously, i had a link https://treasury.un.org/operationalrates/xsql2XML.php  which was working fine importing the XML data into excel using normal data connection. i had put workbook open event with Refresh.all  and it was updating my data from the XML source link.

somehow it stopped working.  if i open the link https://treasury.un.org/operationalrates/xsql2XML.php  it downloads the XML file, and if i use the downloaded XML file then it work, however, if i put this https://treasury.un.org/operationalrates/xsql2XML.php into data connection to directly fetch data, ideally it should work but it does not.

any help is appreciated.

i don't care whether it is a normal solution or VBA, as long as i can get my data updated from the source https://treasury.un.org/operationalrates/xsql2XML.php

1 Solution
Ray PaseurCommented:
What is the PHP part of this question?  Are you looking for a PHP script that will read the XML document?
excelismagicAuthor Commented:

i am sorry, the topic was suggestion by EE itself. i need solution in Excel.  apparently the website link has a extension of php
i am not looking for PHP script, i need help with Excel or VBA in Excel to be able to export the data from given link into Excel worksheet.


pls try
    ActiveWorkbook.XmlImport URL:= _
        "https://treasury.un.org/operationalrates/xsql2XML.php", ImportMap:=Nothing, _
        Overwrite:=True, Destination:=Range("$A$1")

Open in new window

or Data / From Other Sources / From Xml Data Import
in File Name the website


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now