Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2004
    Posts
    26

    Unanswered: Export Data to Excel

    Hello,

    I have created a button on my form which when pressed does the following :

    1) Run a pre-defined Macro, which is picking up a query and running my data to excel.

    However, I need the data to export into Excel in a certain format, i.e it needs to begin importing at cell A4, and in truth it would be great if I could get the data to populate a pre-designed excel worksheet.

    would anyone be able to help me do this please ?

    Thanks,

    J.

  2. #2
    Join Date
    Mar 2004
    Location
    www.scirocco.ca
    Posts
    346
    You can automate Excel through Access then go to the exact column and row and then add the data to the exact cell. Here is some sample code:

    Code:
    Dim exlApp As Excel.Application
    Dim wSheet As Excel.Worksheet
    Dim wBook As Excel.Workbook
    Dim iCol As Integer
    Dim iRow As Integer
    Dim rs As ADODB.Recordset
    
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = "ConnectionString"
    rs.Source = strSQL
    rs.Open
    
    If Not (rs.BOF And rs.EOF) Then
        
        Set exlApp = New Excel.Application
        exlApp.Visible = False
        Set wBook = exlApp.Workbooks.Add
        Set wSheet = wBook.Sheets(1)
        
        'Column Titles
        For iCol = 0 To intNumberOfFields - 1
            wSheet.Cells(1, iCol + 1) = rs.Fields(iCol).Name
        Next iCol
            
        For iRow = 2 To rs.RecordCount
            For iCol = 0 To intNumberOfFields - 1
                        wSheet.Cells(iRow, iCol + 1) = rs.Fields(iCol).Value
            Next iCol
            rs.MoveNext
        Next iRow
        
        wBook.SaveAs frmSSR016.dlgMain.FileName
        exlApp.Quit
        Set exlApp = Nothing
    http://www.scirocco.ca/images/banner...occobanner.gif

    Download for FREE the ADO/DAO Data Controls that makes life EASIER developing database applications in: VB, FoxPro, Access, VC++, .NET etc... Navigate, Add New, Delete, Update, Search, Undo and Save your changes. Supports Disconnected Recordsets and Transactions!

    Or try our Ask An Expert service to answer any of your questions!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •