First, here is the complete code for the save to Excel button:
Private Sub tsbtnSaveToExcel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsbtnSaveToExcel.Click, tsbtnSaveToXML.Click Try Dim fileName As String Dim response As DialogResult sfdSaveToExcel.InitialDirectory = excelFileLocation 'this was set ahead of time sfdSaveToExcel.FileName = "myFile" response = sfdSaveToExcel.ShowDialog() If response = Windows.Forms.DialogResult.OK Then fileName = sfdSaveToExcel.FileName If fileName <> String.Empty Then fileName = SetFileType(fileName, ".xml") 'this is a method i created that will make sure the filename doesnt have an extension on it, then adds the xml extention. If Not IO.File.Exists(fileName) Then Dim fs As System.IO.FileStream Dim xtw As System.Xml.XmlTextWriter myDataTable.TableName = "MyTable" fs = New System.IO.FileStream(fileName, IO.FileMode.Create) xtw = New System.Xml.XmlTextWriter(fs, System.Text.Encoding.Unicode) xtw.WriteProcessingInstruction("xml", "version='1.0'") xtw.WriteProcessingInstruction("mso-application", "progid='Excel.Sheet'") myDataTable.WriteXml(xtw) xtw.Close() tsslMessagesRight.Text = "File Successfully saved." Else tsslMessagesRight.Text = "That file already exists. Please try again with a unique file name." End If End If End If Catch ex As Exception MessageBox.Show(ex.Message) End Try End Sub
As you can see, the code is not that complicated. Basically, you open a file stream to stream all of the information to the file you want. The file stream creates the desired file, then the xml text writer goes to work. You add the xml version information via the WriteProcessingInstruction method, which would be auto generated if you only used the WriteXML method of the DataTable class. The next processing instruction is the key that will unlock the Excel functionality. Basically, xtw.WriteProcessingInstruction("mso-application", "progid='Excel.Sheet'") tells the system that this xml file is meant for use in Excel. When you double click the file or open it from the Excel file menu, you will get a small popup box that will ask you how you want to open the xml file. You choose the option which best suits your needs, usually the “As an XML table” option.
That's it. Pretty easy, eh? Hope this helps someone out there avoid the difficulty of piecing together the information through the same several day process I had to go through. Special thanks to my co worker BK for his assistance.