How to Transfer Data by VBA

Written by martin cole
  • Share
  • Tweet
  • Share
  • Email

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:


  1. 1

    Start up ""

  2. 2

    Click "File," then "New." Select "Project." Choose "Windows Application" from the project types to create Form1.

  3. 3

    Click "Toolbox," located on the view menu.

  4. 4

    Insert a button into "Form1," and then double-click "Button 1."

  5. 5

    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;"
  6. 6

    Click on "Form1 Class," and press "Ctrl" and "P" together to paste the code.

  7. 7

    Copy all of the following code:

        'Connect to the data source.
        Dim objConn As New System.Data.OleDb.OleDbConnection(strConn)
        '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)
        ' Create the FileStream to write with.
        Dim strFilename As String
        strFilename = "C:\Customers.xml"
        Dim fs As New System.IO.FileStream(strFilename, _
        'Create an XmlTextWriter for the FileStream.
        Dim xtw As New System.Xml.XmlTextWriter(fs, _
        '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.
        MsgBox("Customer data has been exported to C:\Customers.xml.")
        Catch ex As Exception
        End Try
  8. 8

    Paste the code into the "Button1_Click" handler.

  9. 9

    Press "F5" on your keyboard to create and then run the program.

  10. 10

    Click "Button1," and this will create the XML file.

  11. 11

    Exit "Form1" to close the program.

  12. 12

    Start up "Microsoft Office Excel." Click on the "Microsoft Office" button, then "Open."

  13. 13

    Find and select the file "C:\Customers.xml."

Don't Miss

  • All types
  • Articles
  • Slideshows
  • Videos
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the site, you consent to the use of cookies. For more information, please see our Cookie policy.