I just used your xml and did the same as you mentioned and the properties button is available. If I modify the external XML file, save, close, and reopen the excel file, it does not refresh the table.Ĭomment by: Jan Karel Pieterse (13-3-2009 02:14:25) deeplink to this comment Select a cell inside the table in the spreadsheet.Īt this point the CONNECTION PROPERTIES under both the DESIGN and DATA Refresh tabs are grayed out. I accepted all the default property settings. I selected an XML file I had generated from a DataTable: From the DATA tab -> Get External Data section, I chose "From Other Sources/From XML Data Import" How did you tie the xml to the worksheet, what option did you select on the very first step after clicking Open?Ĭomment by: Paul Roewer (12-3-2009 22:55:01) deeplink to this comment Odd that it does not work, it works fine for me. Clicking the REFRESH ALL button does reload the XML data, but as I stated, I want to have it reloaded when I open the excel file.Ĭomment by: Jan Karel Pieterse (12-3-2009 02:58:03) deeplink to this comment I'm assuming this should work for both a macro-enabled workbook as well as a macro-enabled template? I've tried both, but still nothing. I've tried it both in the DATA tab as well as the DESIGN tab. I've tried selecting both the column heading cell and a cell within the column, but neither one enables the External Table Data connection properties settings. You do have a cell inside the table selected?Ĭomment by: Paul Roewer (11-3-2009 07:28:18) deeplink to this comment Any idea why this is happing?Ĭomment by: Jan Karel Pieterse (10-3-2009 22:23:10) deeplink to this comment However, the External Table Data connection properties are all grayed out so I cannot choose the "Refresh data when opening the file" option. What I need is to have the xml data refresh (reloaded) whenever the excel file is opened. I defined an excel workbook as you have described above. Or -in Excel 2003- Simply press the "Import XML"Ĭomment by: Paul Roewer (10-3-2009 12:07:47) deeplink to this comment To import a different file into the same structure, use theĬonnection properties dialog, which you can access by clicking theĭropdown arrow below the refresh button (see figure 11):įigure 11: Changing connection properties to ImportĪfter clicking connection properties, click the Details tab on theĭialog that shows up and you can hit the browse button to access aĭifferent XML file. Seeįigure 9: Button "Refresh XML data"on the "List" toolbar Theĭata from test_en.xml will be read into our newly created template. Tab of the ribbon in Excel 2007 and up, or by clicking the Refresh XMLĭata button on the List toolbar of Excel 2003 (see figure 8 and 9). On the "External Table Data" group of the custom "Table tools, design" Import the data from test_en.xml, simply by pressing the refresh button Which have the same structure as the one we "opened" in Excel. Now a template has been defined which can import data from XML files Shift+click) all elements are placed next to each other as a list:(seeįigure 7: List produced by dragging the "employee" element to the sheet Selecting multiple elements in the taskpane using control+click or Seeįigure 5: Dragging an element to an Excel cellįigure 6: Result after dropping the elementīy dragging an element which contains multiple other elements (or by Simply by dragging them from the task pane to a cell in Excel. The elements of the XML file can now be tied to cells on the sheet,
Excelĭoes not open the XML file at this moment.įigure 4, The structure of the xml file shows up in the taskpane XML file and present the structure in the taskpane (figure 4). Test-en.xml causes a small window to appear (see figure 3).įigure 3: Excel asking what to do with the XML fileīy selecting the third option Excel will analyse the structure of the Then you will see xml file types are shown along with the availableįigure 2: The File, Open window of Excel 2007 and up. You select File (Office button in Excel 2007 and up), Open from Excel,
Excel 2016 import xml data file do not have 365 professional#
Up and in the Microsoft Office Professional or ultimate versions ofĮxcel 2003, or when Excel 2003 is bought as a standalone package. Unfortunately, the XML facilities are only available in Excel 2007 and With Excel 2003/2007/2010/2013 importing XML data is very simple. Let's get a bit more practical and use Excel to do something with XMLįiles.