![]() Wks.Range(wks.Cells(1, 1), wks.Cells(books. If Not author Is Nothing Then results(i, 1) = author.Text Set author = book.SelectSingleNode("author") This method allows you to import data into the workbook from a file path. If Not title Is Nothing Then results(i, 0) = title.Text Set title = book.SelectSingleNode("title") the following code works for importing the file path above. concatenate function gives me the full file path e.g.'C:UsersjoebloggsdesktopINPLACE.xml'. File name to be opened is defined by a cell in the worksheet. 'index to put the values in the right place in the array I have a function built Getdesktop () which successfully and correctly gets the users desktop. Set book = books(i) 'A For Each loop would do this automatically, but we need the 'Iterate through all the book elements, putting the title and author into the array, when available 'Create a two-dimensional array to hold the results ![]() ![]() Set books = oXMLFile.SelectNodes("/library/book") ![]() Then you can use the following code, which iterates through all the book elements, saves the title and author into a 2-dimensional array (if they are available), then pastes the array into an Excel worksheet: Dim oXMLFile As New DOMDocument60ĭim book As IXMLDOMNode, title As IXMLDOMNode, author As IXMLDOMNode Open the Excel file where you want to get the data from the XML file Click the Data tab In the ‘Get & Transform’ data group, click on the ‘Get Data’ option Go to the ‘From file’ option Click on ‘From XML’ In the input data dialog box that opens up, locate the XML file that you want to import and select it Click Import. This will allow you to have typed variables ( Dim book As IXMLDOMNode), which will give you Intellisense. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2022
Categories |