Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Thanked 0 Times in 0 Posts

    Can i export string to Excel ? (Access 2000)

    I am exporting a query called "qryAcc" to Excel with the help of the following command :

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryAcc", "c:beAccounts.xls"

    However i cannot export a string with the same command. itried the following:
    Dim Bas As String

    Bas = "SELECT DISTINCTROW Orders.paymentid, Orders.invoicedate, Orders.PaymentMethodID,

    Customers.Customerid, Customers.CompanyName, products.Productid, products.grade, products.code,

    [order details].UnitPrice, [order details].Quantity, Customers.afid, Customers.kindid, Customers.bulstat,

    Customers.taxid " & _
    " FROM products INNER JOIN (Customers RIGHT JOIN (Orders INNER JOIN [order details] ON

    Orders.orderid = [order details].OrderID) ON Customers.Customerid = Orders.customerid) ON

    products.Productid = [order details].ProductID " & _
    " WHERE (((Orders.paymentid) = True)) ORDER BY Orders.invoicedate;"

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, Bas, "c:beAccounts.xls"

    I receive the following mesage :
    The table name you entered doesnt follow Access object naming rules.

    Can somebody help me ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 28 Times in 28 Posts

    Re: Can i export string to Excel ? (Access 2000)

    The TableName argument of the TransferSpreadsheet method must be the name of a table or of a stored query; SQL statements are not allowed. So you must create a query with the specified SQL, then export the query. If necessary, you can delete the query afterwards.

    If you want to do all this in code, you can use DAO to create and delete the query. You need to set a reference to the Microsoft DAO 3.6 Object Library in Tools/References... (from the Visual Basic Editor) for this. The code could look lie this:

    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim Bas As String

    Bas = "SELECT ..."
    Set dbs = CurrentDb
    Set qdf = dbs.CreateQueryDef("qryTemp", bas)

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, qdf.Name, "C:beAccounts.xls", True

    dbs.QueryDefs.Delete qdf.Name
    Set qdf = Nothing
    Set dbs = Nothing

Posting Permissions

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