Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    London,UK
    Posts
    54

    create an excel spreadsheet using vba code in access

    can this be done?

    I have made a database and a front end to go with it. I want the user to be able to export the data to excel. i have currently only allowed to export in csv but would like to do the same in excel.

    Any guidence much appreciated.

    jas

  2. #2
    Join Date
    Oct 2003
    Posts
    311

    Re: create an excel spreadsheet using vba code in access

    Originally posted by tinkxtheminx
    can this be done?

    I have made a database and a front end to go with it. I want the user to be able to export the data to excel. i have currently only allowed to export in csv but would like to do the same in excel.

    Any guidence much appreciated.

    jas
    This will get u started,, if u need more lemeno

    Sub main()
    Dim sql As String
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset


    sql = "SELECT Deal.Deal, Deal.Valid, Deal.DropDown FROM Deal WHERE (((Deal.Valid)=""V"") AND ((Deal.DropDown)=""R"")) OR (((Deal.Valid)=""V"") AND ((Deal.DropDown)=""C""));"

    rs.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText

    XL rs

    Set rs = Nothing




    End Sub
    Sub XL(rs As ADODB.Recordset)
    Dim XL As Excel.Application
    Dim Book As Excel.Workbook
    Dim wks As Excel.Worksheet

    Set XL = New Excel.Application
    XL.Visible = True
    Workbooks.Open fileName:= _
    "c:\CurrentDealList.xls"

    Sheets("DealList").Activate
    [a1].Select
    SelectAll
    Selection.Delete
    Range("A1").CopyFromRecordset rs
    Set XL = Nothing
    ' garbage XL
    End Sub
    Sub SelectAll()
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select

    End Sub


    Note i dont dipose of exl app or something like that so there can be memory leak or something like that,,, i think so if lots of ppl are going to be doin this u need to keep that in mind - im the only person that uses this code so its good enuf 4 me

    M~

Posting Permissions

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