Importing data into Microsoft Office Excel doesn't have to mean manually entering the entire worksheet. Using Visual Basic Access (VBA) saves you much effort, transferring your files without a hitch. It gives you the tools to open files in the Extensible Markup Language (XML), which is used to encode documents with a format that can be read by any compatible application; Excel is one such application. Simply create a DataSet object, export it to an XML file and your data will be waiting for you in Excel.
- Skill level:
Other People Are Reading
Start up "MicrosoftVisualStudio.net"
Click "File," then "New." Select "Project." Choose "Windows Application" from the project types to create Form1.
Click "Toolbox," located on the view menu.
Insert a button into "Form1," and then double-click "Button 1."
Highlight the following code by dragging your mouse from the first word to the last.
Hold "Ctrl" and press "C" on your keyboard to copy it:
Private strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _ "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb;"
Click on "Form1 Class," and press "Ctrl" and "P" together to paste the code.
Copy all of the following code:
'Connect to the data source. Dim objConn As New System.Data.OleDb.OleDbConnection(strConn) Try objConn.Open() 'Fill a dataset with records from the Customers table. Dim strSQL As String Dim objDataset As New DataSet() Dim objAdapter As New System.Data.OleDb.OleDbDataAdapter() strSQL = "Select CustomerID, CompanyName, ContactName, " & _ "Country, Phone from Customers" objAdapter.SelectCommand = New System.Data.OleDb.OleDbCommand( _ strSQL, objConn) objAdapter.Fill(objDataset) ' Create the FileStream to write with. Dim strFilename As String strFilename = "C:\Customers.xml" Dim fs As New System.IO.FileStream(strFilename, _ System.IO.FileMode.Create) 'Create an XmlTextWriter for the FileStream. Dim xtw As New System.Xml.XmlTextWriter(fs, _ System.Text.Encoding.Unicode) 'Add processing instructions to the beginning of the XML file, one 'of which indicates a style sheet. xtw.WriteProcessingInstruction("xml", "version='1.0'") 'xtw.WriteProcessingInstruction( _ ' "xml-stylesheet", "type='text/xsl' href='customers.xsl'") 'Write the XML from the dataset to the file. objDataset.WriteXml(xtw) xtw.Close() MsgBox("Customer data has been exported to C:\Customers.xml.") Catch ex As Exception MsgBox(ex.Message) End Try
Paste the code into the "Button1_Click" handler.
Press "F5" on your keyboard to create and then run the program.
Click "Button1," and this will create the XML file.
Exit "Form1" to close the program.
Start up "Microsoft Office Excel." Click on the "Microsoft Office" button, then "Open."
Find and select the file "C:\Customers.xml."
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for