Video: How Do I: Use Custom XML Parts with Excel?

Presenter:Steve Hansen

This video demonstrates how you can use custom XML parts to store critical business data and keep the data in sync with ranges from the workbook.


The following text is a software generated transcript of the video. Click on a minute link to jump to a location within the video

Minute 0
By my name is Steve Hansen with office of a duck out of this video is how to use custom XML parts with Excel 2007 for work and at here is an invoice template in Excel and what I d like to do is create a solution that has an invoice template has an embedded custom XML part containing data for the invoice and as changes are made to be template like to keep that custom XML in sync so by doing this after the invoice is created using system I without packaging you could extract the XML and send it off to a number system or use it in some other manner likewise you could modify the XML using system I know that packaging without opening Excel
Minute 1
And have those changes appear in Excel will be showing today due to time is just really the one way of keeping the XML and sink rather than keeping the worksheet and sink as changes are made to be XML nonetheless I would be really easier exercise to do after you learn how to do the one way unconventional here today so I m going to close this template down before it into the project known as I ve got an empty invoice XML file and these are the notes of them going to buy into named ranges in my template from ahead and create a new project
Minute 2
Using Visual Basic office 2700 trading in Excel template in a college is very long name here and attack in an existing template that authority created the invoice template that I showed the beginning of the video and if you think you point out for small I ve named the range is that I m going to buy into my custom XML nodes got invoice date invoice number customer name customer address customer enters to phone and on Arafat and invoice total cell
Minute 3
Possible to avoid having to change the numeric date value in Excel to string is going to format the solace tax to begin with and mixing them to do is add the VoiceXML to the project in an existing file and fats changed this to all files certain schema invoice XML file
Minute 4
As you do is win the day template is opening the door new instance of the template is created in Excel I want to add the editing invoice customer XML part to the defile so I m going to view the code associated with this workbook and then at start up and going to create a procedure called at invoice XML them also going to use any public very will appear
Minute 5
Emulate a public so that it can use it from the sheet world class so in added invoice XML I need to do is set the part equal to the result of calling the add method and custom XML parts and the XML but I want to use is indeed resources he invoice resource and I m going to just not worry about the second parameter here
Minute 6
Nor to provide a way to verify that part has been added or to view the XML associate with part and going to just add a button to the document surface here than in the butt one click event I m just going to show the XML associated with my customer XML part
Minute 7
A photo head in and added a Windows form to the project and as a tech stocks set the multi lined to true
Minute 8
In the so set the text of text by sworn equal to the XML associated with my XML part one of them build us and then run it and make sure that the part gets added correctly to the document and then make sure that we can see the XML in the form
Minute 9
Society you invoice XML do see the invoice has been edited correctly a selection to do is to watch the change event associated with all the named range is that I d like to associate with my custom XML part in the change event go ahead and update the appropriate note so viewed a designer for sheet one wants us double click on all of the of the main ranges to generate the event handlers to
Minute 10
To be a cool change of a really do any to do here because there is a form of error in the change event doesn t fire when the worse when the cell is changed because of the result of consolation so rather than use the change event for that one would want to use the calculate event on the worksheet
Minute 11
Saw changed the changeover seat Warren and then double click in the calculate property here to generate the event handler and delete the invoice total change them suck out all of the event handler event am assigning Stockton tonight is need to go ahead and implement them so in the invoice date changed one to do is declare in a variable as an office that custom XML node
Minute 12
Illicit equal to the appropriate node in my custom XML part so to get that no of them going to use these like single node method associated with the XML via custom axonal part sucking you select single node and in what I need to pass in as a parameter is the ex pat to the appropriate node went to have a reference to the node news that the node text equal to the value of the value of the cell
Minute 13
Value of this element comes from the target parameter which it represents the named range here for the invoice date mainframes and I can use value or value to the menus feature string methods surfing is a slow code snippet for all the other event handlers as for the calculate event was a need to deliver different and ongoing abuses update the ex Baath
Minute 14
If she will delay in the lead is a little different
Minute 15
The difference is that instead of using target and need to specify the range and since I got a name that amused me that should be invoiced total and in value to two string smell go ahead and run it to be in VoiceXML no such as got the XML there but no values so no hadn t fill out the invoice
Minute 16
And you know the boys and I thought I is I go then click view and VoiceXML noticed that my custom XML or is then filled in with all of the values that I ve entered for the boy presumably you could sin the invoice off to a customer St Ives strip out the important XML data and send that to some other system that concludes this video on how to use custom XML parts with Excel thanks frosting a great day

Click here to go to original video page