visual basic in Access to output as an Excel application

Question

In Access set the worksheet created by "DoCmd.RunCommand acCmdOutputToExcel" as the variable xlsheet.  Must I save the workbook first for this to work?

Answer 1

Please post Access questions to the Access for Developers forum. This forum is primarily for Visual Basic .NET.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 2

Here is code that will put a query called qlkpBusinessType into a spreadsheet.

------------------
Private Sub MyTestForAnswer()
On Error GoTo Err_Handler

    Dim strSQL As String
    
    strSQL = "qlkpBusinessType"
    DoCmd.RunCommand acCmdOutputToExcel

Exit_Handler:
    Exit Sub
Err_Handler:
    MsgBox Err.Number, Err.Description
    Resume Exit_Handler

End Sub
----------------

---

Jeanette Cunningham

Answer 3

Well what are you trying to export to Excel? A query/ Table I presume?

In case of of a Query/ Table you can use the DoCmd.TransferSpreadsheet for that, see below link:

http://msdn.microsoft.com/en-us/library/bb214134%28v=office.12%29.aspx

In case you want to use the DoCmd.RunCommand, you can do something like this:

Private Sub cmdYourCommandButton_Click()
    ' open a query
    DoCmd.OpenQuery "qryYourQueryName"
     ' export the Query
       DoCmd.RunCommand acCmdOutputToExcel
     ' close the query
    DoCmd.Close acQuery, "qryYourQueryName"

End Sub

But you haven't control over where (the file location) the Query will be saved for example (It will be saved in Default file location).

Hope this helps,

---

Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

Please vote an answer helpful if they helped. Please mark an answer(s) as an answer when your question is being answered.