Importing data to Excel

[cantene]

Dear Memebers,

I wish to ask for your advice on a question:

I am buidling a standalone MDI application which reads data from an Access database and then wants to plot charts on a child form using Excel.

I heard I can use OLE container for controlling Excel for charting. But I first have to import data into it from my application. How can I do that?

I would appreciate very much if you could kindly post a reply. Thanks

[alex_read]

Phew, here you go. You'll need refereences to the DAO project library & the Excel project library from the Project menu...

Private Sub Command1_Click()
    Dim XlsApp As New Excel.Application
    Dim Db As DAO.Database, Rs As DAO.Recordset, Rng As Variant
        Set Db = DAO.OpenDatabase("C:\A_Database_Name_here")
        Set Rs = Db.OpenRecordset("A_Table_Name_Here")
        Set Rng = Range("A1:A10")
    'declare variables for Excel file and Access file, and set up a range of cells to copy to
    
With XlsApp
    .Workbooks.Open ("C:\A_FileName_Here.xls")
    .Worksheets(1).Select
         For Each cell In Rng
                cell.Value = Rs!Column_Name_Here
                Rs.movenext
        Next cell
    .DisplayAlerts = False
    .Workbooks(1).Save
    .Workbooks.Close
End With
    'open excel file, loop through the access table column and
    'copy the value into the above mentioned range

    Set Rng = Nothing
    Set XlsApp = Nothing
    Set Rs = Nothing
    Db.Close
    Set Db = Nothing
    'quit Excel and Access, disregard variables to free memory
End Sub