Results 1 to 8 of 8
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    Export error


    All using 2016..I'm getting an error: msaccess can't save the output data to the file you've selected..

    Code:
    Private Sub Command9_Click()
    Dim qryname As String
    Dim theFilePath As String
    qryname = "qryPrintrecord"
    theFilePath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\Output\"
    theFilePath = theFilePath & reportname & "_" & Format(Date, "yyyy-mm-dd") & ".xls"
     
    DoCmd.acOutputQuery, qryname, acfortmatXLS, theFilePath, True
    MsgBox "Look on your desktop for the file in the output folder"
    Can anyone assist pls

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    i suspect this is your problem

    acfortmatXLS

    try

    acformatXLS

    edit: I think you mean to use docmd.outputto

    https://docs.microsoft.com/en-us/off...docmd.outputto

  3. #3
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    Thanks for replying..typo..tried acFormatXLSX also..still error

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Thanks for replying..typo.
    you do realise that by not providing your exact code you are introducing red herrings which means the chances of getting a sensible answer are significantly reduced. Not to mention time wasted by others chasing those red herrings. Plus it's a lot quicker to copy/paste. By doing this, I now have to wonder if you have excluded some lines of code.


    tried acFormatXLSX also..still error
    since you have not provided your exact code I cannot comment other than your file type would appear to be a .xls. Another typo? or two?

    and you don't appear to have read my edited first post.

  5. #5
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I apologize..I was typing on my phone in the forum...Here's the code; copied and paste from my laptop:

    Code:
    Private Sub Comman9_Click()
    
    Dim qryname As String
    Dim theFilePath As String
    
    qryname = "qryPrintRecord"
    theFilePath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\Output\"
    theFilePath = theFilePath & qryname & "-" & Format(Date, "yyyy-mm-dd") & ".xlsx"
    DoCmd.OutputTo acOutputQuery, qryname, acFormatXLSX, theFilePath, True
    MsgBox "Look on you desktop for the file in the output folder"
       
    End Sub]
    Read the edited post about the outputto..This is what I used...

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I see the button name has changed as well!

    your filepath does not look correct to me, perhaps an old system?

    Code:
    theFilePath = "C:\Documents and Settings\" & Environ("UserName") & "\Desktop\Output\"
    it may be you have a different setup but I would use

    Code:
    "C:\Users\" & Environ("UserName") & "\Desktop\Output\"
    Alternatively suggest maybe the file already exists and/or is open. or perhaps there is not an output folder on the users desktop.

    It may be that the user does not have the required write access to the folder so temporarily remove the filepath from the outputto command so you are prompted where to save and navigate to the appropriate folder. If that works, then reinstate the filepath but go for a different directory. If they both work then the implication is there is something wrong with your filepath, although I don't see anything obvious other than as mentioned above

  7. #7
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727
    I tried your suggested code, using "users" instead but still get the same error. If I take the "Output" off, it exports the query to excel. The users would just have to format and save. Thanks for your help..

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    tried your suggested code, using "users" instead but still get the same error.
    use the windows navigator to go to the output directory and then click on the navigation bar - you will see the full path.

    it exports the query to excel.
    and can you do that by going to the output folder?

    only other thought is using the hyphen char in the file name. It's OK in win 10, don't know about XP, it may have been an illegal character back then

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Export To Excel Error
    By RayMilhon in forum Programming
    Replies: 10
    Last Post: 03-14-2019, 12:06 PM
  2. Replies: 1
    Last Post: 07-26-2016, 06:34 AM
  3. export error
    By slimjen in forum Forms
    Replies: 1
    Last Post: 04-08-2013, 02:33 PM
  4. Error Attempting to Export Data to IE9
    By Angrybox in forum Programming
    Replies: 9
    Last Post: 06-29-2012, 03:50 PM
  5. Macro export to excel error
    By Andy_d in forum Import/Export Data
    Replies: 7
    Last Post: 04-15-2011, 09:54 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums