Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27

    Post Unanswered: Access query outputted to excel

    Hi,

    How do I add a button in an access97 query so that the query data is outputted to an excel97 file.

    what formatting do I have over how it is displayed in Excel.
    can the statistics collected be outputted as a graph.


    hope u can help
    Many thanks
    the one and only ada17

  2. #2
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Private Sub DVDPer_Click()
    DoCmd.TransferSpreadsheet acExport, 8,
    "XLMachineOutputByDate", _
    "J:\Measures\Machines\DVDPerformance.xls", False, ""
    Beep

    End Sub

    hope that helps
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  3. #3
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27

    Post

    Hi, I am a lil confused as where to put this code.

    I mean do I create a form from the query, then add a button on the form, a button with code as u suggested.

    help!!

    Ta.



    Originally posted by m.timoney
    Private Sub DVDPer_Click()
    DoCmd.TransferSpreadsheet acExport, 8,
    "XLMachineOutputByDate", _
    "J:\Measures\Machines\DVDPerformance.xls", False, ""
    Beep

    End Sub

    hope that helps
    the one and only ada17

  4. #4
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27
    Hi, me again.

    I created a form from the quey. Put a button on it, and put your code in the button's click.

    But that doesn't work and produces a Compiler error: Syntax error.

    Can you help any1.

    ta

    Originally posted by m.timoney
    Private Sub DVDPer_Click()
    DoCmd.TransferSpreadsheet acExport, 8,
    "XLMachineOutputByDate", _
    "J:\Measures\Machines\DVDPerformance.xls", False, ""
    Beep

    End Sub

    hope that helps
    the one and only ada17

  5. #5
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    that can sit in any item you want as long as it has event traping (ie not a query) the example i've given was on a buttons on_click event (the button also cantains a hyperlenk to that opens the file.

    btw that method is to export into a workbook not export as a workbook
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  6. #6
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    post the entire code from your

    sub <button_name>_Click()
    end sub

    and i'll see if i can see whats wrong
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  7. #7
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27
    I don't really know what event traping is. As for the code in the button, all i typed was what you suggested, just chaned it a little, thus

    Private Sub command5_Click()

    DoCmd.TransferSpreadsheet acExport, 8,
    "XLMachineOutputByDate", _
    "C:\MyDocuments\cust\cust.xls", False, ""
    Beep

    End Sub


    I didn't know what else goes in the code.

    thanks

    Originally posted by m.timoney
    post the entire code from your

    sub <button_name>_Click()
    end sub

    and i'll see if i can see whats wrong
    the one and only ada17

  8. #8
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    in the mean time the code should look like this

    Private Sub <buttonname>_Click()
    DoCmd.TransferSpreadsheet acExport, 8, "<table/queryname>", "<Workbook Location", False, ""
    Beep

    End Sub

    with <> filled in with the relevant data for your database
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  9. #9
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    well there's the problem unless your table's called XLMachineOutputByDate, oh and kill the enter key before it (typo)

    Originally posted by ada17
    I don't really know what event traping is. As for the code in the button, all i typed was what you suggested, just chaned it a little, thus

    Private Sub command5_Click()

    DoCmd.TransferSpreadsheet acExport, 8,
    "XLMachineOutputByDate", _
    "C:\MyDocuments\cust\cust.xls", False, ""
    Beep

    End Sub


    I didn't know what else goes in the code.

    thanks
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  10. #10
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27
    I have tried both these ways:

    n both times i get run-time error 3044

    path is not a valid path. make sure that the path name is spelled correctly and that u are connected to the server that it resides on.

    They are correct, I dont use a server, it's just my local drive, yet it dont work.

    help

    Private Sub command5_Click()
    DoCmd.TransferSpreadsheet acExport, 8, "qeuryname", "C:\MyDocuments\cust\cust.xls", False, ""
    Beep

    End Sub

    and

    Private Sub command5_Click()
    DoCmd.TransferSpreadsheet acExport, 8, "tblCustomerQuestionnaire", _
    "C:/MyDocuments/cust/cust.xls", False, ""
    Beep
    End Sub
    the one and only ada17

  11. #11
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    that problem relates to this message
    Originally posted by m.timoney
    btw that method is to export into a workbook not export as a workbook
    so if the spread sheet doesn't excist you need to use

    sub Macro1()
    DoCmd.OutputTo acTable, "tblCustomerQuestionnaire","MicrosoftExcelBiff8(*. xls)", _
    "C:\MyDocuments\cust\cust.xls", False, "", 0
    end sub
    Last edited by m.timoney; 12-17-02 at 11:17.
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  12. #12
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    FYI the '_' is a meesage to to the complier to ignour the new line charactor that follows it ASAT
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  13. #13
    Join Date
    Dec 2002
    Location
    London, UK
    Posts
    27
    Sorry, but I am not sure where this goes.

    ada

    Originally posted by m.timoney
    FYI the '_' is a meesage to to the complier to ignour the new line charactor that follows it ASAT
    the one and only ada17

  14. #14
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    Originally posted by m.timoney
    that problem relates to this message

    so if the spread sheet doesn't excist you need to use

    sub Macro1()
    DoCmd.OutputTo acTable, "tblCustomerQuestionnaire","MicrosoftExcelBiff8(*. xls)", _ '<--this bit
    "C:\MyDocuments\cust\cust.xls", False, "", 0
    end sub
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

  15. #15
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126

    Query export to Excel

    ada, this could be easier for you but you don't have much control over formatting. Here are the steps:
    1) Create report from you query
    2) Now add the button you mentioned to your original form
    3) Wizard Gives you an option for "Report Operation"
    4) And on of these choices then are "Send the report to a file" and then it's easy

    It's not the most economical solution (extra step with a report0 but it works.

Posting Permissions

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