save tables the current directory

Posted on 2011-10-14
Last Modified: 2012-05-12

I want to record the result of a request and to  save it like a excel file.
Hence I use that function:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "ShowList_Request", "list.xls"

But it saved it in c:\Data. I would like to save it in the current directory, or in the proper one, (correct workspace of the user whatever the computer used).
Could you help me?
Thank you in advance!
Question by:Noero
    LVL 7

    Accepted Solution


    You may want to try out with, you will probably need to add some safty mechanism to this string operations.

    Dim strPath As String
    strPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "ShowList_Request", strPath + "list.xls"


    Author Comment

    Thank you Rahul, great clue!
    LVL 2

    Expert Comment

    In the DoCmd commamd you can provide entire path instead of 'list.xls' like "d:\myFolder\link.xls"

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now