Home > How-To Library > MS Office

How to take data from a VB database application and place it into an Excel spreadsheet

****************************************************************
*       © 2007 CodeItBetter http://www.codeitbetter.com        *
*          This notice MUST stay intact for legal use          *
****************************************************************

Private Sub Excel_Spreadsheet(rst As Recordset)
    'Declare all Excel objects
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Dim xlWS As Excel.Worksheet

    Set xlApp = New Excel.Application
    Set xlWB = xlApp.Workbooks.Add
    Set xlWS = xlWB.Worksheets.Add

    'Fill cells using recordset
    xlWS.Cells(1, 1).Value = rst("Field1")
    xlWS.Cells(2, 1).Value = rst("Field2")

    'save spreadsheet
    xlWS.SaveAS "mysheet.xls"
    xlApp.Quit

    'Free memory
    Set xlWS = Nothing
    Set xlWB = Nothing
    Set xlApp = Nothing
End Sub

If you would like to submit your code here please us. Do not forget to mention your name. We are always thankful to each and everyone of you who submitted their code here.