Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Jaffa is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2020
    Posts
    8

    acFormatXLS 'not available' on new computer - Access 2007

    Greetings,

    For several years, I have been using Access 2007 to generate a report and export it to Excel. I have done this primarily from two computers. One of those computers recent crashed, so I had to prepare a replacement.

    Unfortunately, I'm now getting a runtime 2282 error when I try to run the report: "The format in which you are attempting to output the current object is not available". When I click debug, this is the highlighted line:

    DoCmd.OutputTo acOutputReport, "Best Seller", acFormatXLS, "(my document)"



    NOTE: The line says XLS. I understand that Access cannot export as xlsx.

    The strange thing is, the report still works fine from the other computer that can run it. Both computers have Access 2007 and Excel 2010, but one of them is flagging this 'format not available' error. I'm guessing there is an issue with my settings for Access, but I'm not sure where to look.

    Any advice would be much appreciated. Thank you for your time!

  2. #2
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Try replacing the constant with its value ("Microsoft Excel (*.xls)"):

    DoCmd.OutputTo acOutputReport, "Best Seller", "Microsoft Excel (*.xls)", "(my document)"

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Jaffa is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2020
    Posts
    8
    Thank you for the suggestion. Unfortunately, this still triggers the same error.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You are probably missing a reference on the new pc. Check all the references in your project for any that are missing or broken.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I understand that Access cannot export as xlsx.
    - you can, using acformatxlsx which is a constant for

    Microsoft Excel Workbook (*.xlsx)

    to see all the options available to you, in the vba window, open the object browser (hit the F2 key) and search for acformat

    Just to confirm your setup, each user has their own copy of the access file - i.e. they are not sharing the same file

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Here is an enumeration of the OutputFormat for the VBA command DoCmd.OutputTo method (Access)

    Name Value Description
    acSpreadsheetTypeExcel3 Excel 3.0 format
    acSpreadsheetTypeExcel4 6 Excel 4.0 format
    acSpreadsheetTypeExcel5 5 Excel 5.0 format
    acSpreadsheetTypeExcel7 5 Excel 95 format
    acSpreadsheetTypeExcel8 8 Excel 97 format
    acSpreadsheetTypeExcel9 8 Excel 2000 format (*.xls)
    acSpreadsheetTypeExcel12 9 Excel 2007 format (*.xlsx)
    acSpreadsheetTypeExcel12Xml 10 Excel 2010 format (2010 and later) (*.xlsx)

    See https://ss64.com/access/acspreadsheettype.html

    I have used acSpreadsheetTypeExcel12Xml (a couple of times) with no problems......(IIRC)

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I regularly use the 12xml type. It works fine.

    Has anyone else noticed oddities in the Value field in the enums.
    The numbers aren't sequential and there are repeated values … presumably the format is identical in each case despite the name differences

    BTW the official MS guide has slightly different descriptions for the last two lines https://docs.microsoft.com/en-us/off...preadsheettype
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Just to point out, acSpreadsheetType is used with transferspreadsheet, not OutputTo

    but using transferspreadsheet instead might be an alternative to using outputto

  9. #9
    Jaffa is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2020
    Posts
    8
    Quote Originally Posted by Micron View Post
    You are probably missing a reference on the new pc. Check all the references in your project for any that are missing or broken.
    There are only five references checked, and they are checked on both machines. None of them are marked missing or broken.

    Quote Originally Posted by Ajax View Post
    - you can, using [/COLOR]acformatxlsx which is a constant for

    Microsoft Excel Workbook (*.xlsx)

    to see all the options available to you, in the vba window, open the object browser (hit the F2 key) and search for acformat

    Just to confirm your setup, each user has their own copy of the access file - i.e. they are not sharing the same file
    acFormatxlsx doesn't work either. Based on what I read on other support threads, I thought that was a common issue.

    To clarify, the database and destination file are located on a shared network and accessed by both computers. So yes, they are sharing the same file, although they do not use it at the same time for obvious reasons.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    acFormatxlsx doesn't work either.
    did you do what I suggest - go to the vba object browser and see if it is there?

    So yes, they are sharing the same file, although they do not use it at the same time for obvious reasons.
    unless one user is on it from 9 to 5 and the other 6 to midnight, you still run a risk. Only takes the second user to open the front end by mistake at the wrong time and you have the potential for corruption. Users should never share the front end. Applications should be split, each user having their own copy of the front end on their local machine and the the shared backend on the server.

    Chances are they have both been on at the same time and you now have corruption. Recommend you create a brand new back end on the server and import all the tables. Then create a brand new front end and import all the forms/reports/queries/code/macros, link it to the backend and provide each user with a copy. If you have got corruption, hopefully that will cure it.

  11. #11
    Jaffa is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2020
    Posts
    8
    Quote Originally Posted by Ajax View Post
    did you do what I suggest - go to the vba object browser and see if it is there?
    I did. Sorry, I forgot to respond to this piece.

    Both acFormatXLS and acFormatXLSX are listed.



    Quote Originally Posted by Ajax View Post
    unless one user is on it from 9 to 5 and the other 6 to midnight, you still run a risk. Only takes the second user to open the front end by mistake at the wrong time and you have the potential for corruption. Users should never share the front end. Applications should be split, each user having their own copy of the front end on their local machine and the the shared backend on the server.

    Chances are they have both been on at the same time and you now have corruption. Recommend you create a brand new back end on the server and import all the tables. Then create a brand new front end and import all the forms/reports/queries/code/macros, link it to the backend and provide each user with a copy. If you have got corruption, hopefully that will cure it.
    To clarify, we do not actually have multiple individual users using this database at all. I am the only actual person that uses it. I have it installed on two computers because the computers are shared workstations, so I like to have a backup available in case one of them is being used by someone else (for something else). So I'm pretty confident I did not accidentally run this from two computers at the same time.

    That being said, I suppose it is possible (thought very unlikely) that someone else may have opened Access by mistake. That being the case, I though, I'm still a little confused. If something in the process got corrupted, wouldn't it stop working entirely? Keep in mind that the second computer can still run this without any issues whatsoever. It is only the new computer, freshly set up, that is having problems.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    do both pc's have the same version and build of Access?
    did you set up a trusted location for the new pc and the db is in it?
    Not disagreeing with the multi user/non split db comments, but I don't think corruption is your issue.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  13. #13
    Jaffa is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2020
    Posts
    8
    Quote Originally Posted by Micron View Post
    do both pc's have the same version and build of Access?
    did you set up a trusted location for the new pc and the db is in it?
    Not disagreeing with the multi user/non split db comments, but I don't think corruption is your issue.
    I do have the db's location registered as a trusted location.

    They are both Access 2007.

    I hadn't thought to check the build, but it does appear they are different.

    Working PC is 12.0.6735.5000, SP3 MSO (12.0.6785.5000).

    PC with errors is 12.0.4518.1014, MSO (12.0.6020.5000).

    Not sure how this occurred, since the same installation media was used for both PCs. Do you think this is the issue?

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If something in the process got corrupted, wouldn't it stop working entirely?
    not necessarily, it can start small - something might stop working, then something else doesn't work as expected - and a bit later everything stops working - and if your db is not split, not only do you lose your forms/reports etc, you lose your data as well.

    but I don't think corruption is your issue.
    possibly not - it works from one machine but not the other, but the setup lends it to being a possibility since both computers have the same OS, same version of Access, same version of Excel. Only oddity is it is access 2007 and excel 2010 - normally you would have the same version number since they would come from the same version of Office. But it works on one computer, so it should work on the other.

    So I'm pretty confident I did not accidentally run this from two computers at the same time
    So the scenario of you are on workstation A, go to lunch, find someone else is working at that workstation on your return so you go to workstation B is not possible?

  15. #15
    Jaffa is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2020
    Posts
    8
    Quote Originally Posted by Ajax View Post
    So the scenario of you are on workstation A, go to lunch, find someone else is working at that workstation on your return so you got to workstation B is not possible?
    Because they are shared workstations, I am in the habit of closing out of everything I am working on when I go to lunch. So, yes, I might theoretically go to lunch and come back to find that someone else had taken over the workstation. But they wouldn't have any reason to be using Access, and I wouldn't have left it open. And if for some reason I did leave it open (for example, if I thought I would be alone in the office and someone showed up unexpectedly), I would ask them to close it.

    Of course, nothing is impossible. I'm not perfect, so I could theoretically have left something open. But given the fact that I just installed Access on a new machine and that machine is the only one having issues, it seems more likely to me that there is an issue with my setup of the new machine. The alternative is that I coincidentally caused corruption using a system that I have used for several years. If the consensus is that corruption is the likely issue, I'm willing to take steps to explore that (I know you all know a lot more about this than I do, and appreciate your insight!), but if there are other possibilities, I would prefer to examine those first.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. migrating mdb 2007 access to a new computer
    By amilliondaves in forum Access
    Replies: 3
    Last Post: 01-09-2019, 02:40 AM
  2. Replies: 6
    Last Post: 10-09-2015, 09:29 AM
  3. Replies: 4
    Last Post: 11-29-2012, 12:45 AM
  4. Replies: 10
    Last Post: 08-30-2011, 04:49 PM
  5. acFormatXLS not working
    By Callahan in forum Forms
    Replies: 6
    Last Post: 07-27-2011, 02:45 PM

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