Results 1 to 4 of 4

Thread: Access-Excel

  1. #1
    Join Date
    Apr 2002
    Posts
    168

    Unanswered: Access-Excel

    I am having a report that starts on a new page for each customer ID. I group by customer ID. So, basically, the records contains customer ID and product ID. For example,

    CustomerID ProductID
    A 001
    A 002
    B 001 etc

    So the report shows :

    Customer ID : A

    ProductID
    001
    002

    etc


    So, now, I want to transfer my database to excel.

    So, the excel file will have as many as customers I have. For example,

    First sheet will only have customer A information, second sheet will only have customer B infomation and so on.



    Does anyone know how to do this ? Thanks

  2. #2
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27
    hi,

    If you have already created the report in access, it is quite simple to export it to excel.

    use the following command:


    Docmd.OutputTo(ObjectType, ObjectName, OutputFormat, OutputFile, AutoStart, TemplateFile, Encoding)

    e.g.

    Private Sub PrintExcel(ReportName As String)
    Dim rstParameters As New ADODB.Recordset
    Dim strPath As String

    DoCmd.OpenForm "frmOpen"
    strSQL = "SELECT P_Value FROM T_Parameter WHERE P_ID = 'PATH EXCEL REPORTS'"
    rstPara.Open strSQL, CurrentProject.Connection
    strPath = rstParameters.Fields(0)
    rstParameters.Close
    DoCmd.OutputTo acOutputReport, ReportName, acFormatXLS, _
    strPath & "ListOfContacts.xls", True
    DoCmd.Close acForm, "frmOpen"

    Exit Sub
    ErrorHandler:
    msgbox "Error in PrintExcel" & err.Description

    End Sub

    Hope it helps!!!

    anna

  3. #3
    Join Date
    Apr 2002
    Posts
    168
    Thanks.

    I used the DoCmd.OutputTo in macro, but funny things happened. If I use existing excel file, then my excel file suddenly gone from my drive after the macro finishes. When I use a new excel file, it does not appear. Do you know why ?

  4. #4
    Join Date
    Feb 2002
    Location
    Leuven, Belgium
    Posts
    27
    try setting Autostart to True
    See what happens
    Normally the file should open after the macro

    In my experince this works just fine. I do not use the macro however but I put this in vba code.

    anna
    Last edited by annavp; 04-24-02 at 04:01.

Posting Permissions

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