Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

Creating an xls in Access 2010 VBA not working REVISITED

Hey Everyone,  

I recently posted the question below and thought I had it working.  I put the code recommended into the program but when I published it as an ade I got the format error again.  When I run the adp the program works and it creates the xls file, but as an ade it does not.  Is there something about ade's that prevent them from running this?

Thank you all again.  Here is the original question:

I have an Access 2010 program that at one point allows the user to create an excel export file from the data (well originally I had the program creating a csv file).  Someone is helping me update this program so it works cleaner and in conjunction with the cloud so they changed the code slightly.  Now I get an error box that says "The format in which you are attempting to output the current object is not available"

 Here is the original code that worked:

   DoCmd.TransferText acExportDelim, "", "Export Plan Selection and Deduction To Send Sorted", "\\HOST33\ShareFile\CLIENTS\3CTRPS\Export for " & UCase(txtClientID.Value) & " Date-" & Replace(CStr(Date), "/", "-") & " Time-" & Replace(CStr(Time), ":", "-") & ".csv", True, ""

 Here is the code that he created:

 DoCmd.OutputTo acOutputReport, stdocname, acFormatXLSX, strFilename

 (the variables produce the same name for the file as the first one does).

 I tried changing it to read acFormatXLS also and it still did not work.

 Any help is really appreciated!
0
alevin16
Asked:
alevin16
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
Michael FowlerSolutions ConsultantCommented:
Are you using Access 2007

Prior to Service Pack 2, you could export a table or query in Excel format, but not a report. Both OutputTo and SendObject would fail with error 2282: The format in which you are attempting to output the current object is not available. Applying SP2 should address this.
0
 
alevin16Author Commented:
Thank you I am going to give that a shot
0
 
alevin16Author Commented:
Hey Michael74,

Are you talking about the Access SP2 Runtime?  When I did a search for SP2 that came up.  I tried loading it on the computer but it said it was already loaded.

Did I get the right one?

Thanks
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Michael FowlerSolutions ConsultantCommented:
No the MS Office 2007 Service Pack 2

But there is actually Service Pack 3 now so better off to use that
http://support.microsoft.com/kb/949585
0
 
alevin16Author Commented:
Hello Michael,

I am actually using Access 2010.  WOuld this still work?

Thanks
0
 
Michael FowlerSolutions ConsultantCommented:
No. It seems that the bug was carried into 2010, there are number of people getting the same error but no definitive solutions I have seen. Some possible solutions

1/ Re-install Access
2/ Use acFormatXLS  - Which did not work for you!
3/ If you are using a db created in an earlier version of Access you could try recreating your db in 2010.

These are just guesses and will take some time to implement. May be time to "Request Assistance" and see if any other experts can jump in and help as I am at a loss
0
 
bonjour-autCommented:
I am not quite sure, if it is a bug or a feature.
The excel export works fine for a query. The previous csv export would have also been a table or query as source. So why change to report. A report has several elements as page texts, group headers and footers, which are not excel friendly at all. So if it is just for viewing, you might export directly to pdf alternatively :   DoCmd.OutputTo acOutputReport, stdocname, acFormatPDF, strFilename
0
 
Jeffrey CoachmanMIS LiasonCommented:
Your first code is exporting a csv file. (working)
Your second code is exporting a Report to an Excel file. (Not working)
...so I am a bit confused as to what your ultimate goal is here...

In any event exporting "reports" to excel can be problematic if your report is anything but "simple"
Will any report Export to Excel with similar code?
0
 
alevin16Author Commented:
Hello Everyone,

I really appreciate all the suggestions and help.  To answer Jeffrey, it was a csv file but when I handed it over to my "helper" he changed it to an xls.  I actually have a call into him and will be asking him why (I agree with you that there was no reason to change it from a csv).

I will have to put in some test code to see if the problem is for all excel exports or just this one.

Hopefully I will know more today.

Thanks All!
0
 
Jeffrey CoachmanMIS LiasonCommented:
In defense of your "helper", there may have been a good reason for changing the output format.
The kicker is that changing it to a Report and exporting he report is not working for you...
;-)
Remember that Excel can easily be set to open CSV files (just like it opens XLS files)

...again, can you export a simple report successfully to an Excel file?

I am not sure abut the ADE aspect of your question here,  ...but you stated that you saved it as an ADE file.
You also stated that you are using Access 2010.
Have you tried saving the db file as a .accde file instead?

JeffCoachman
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now