Solved

Creating an xls in Access 2010 VBA not working REVISITED

Posted on 2014-12-01
10
466 Views
Last Modified: 2014-12-04
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
Comment
Question by:alevin16
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 23

Assisted Solution

by:Michael74
Michael74 earned 167 total points
ID: 40475017
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
 

Author Comment

by:alevin16
ID: 40475024
Thank you I am going to give that a shot
0
 

Author Comment

by:alevin16
ID: 40475084
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
 
LVL 23

Expert Comment

by:Michael74
ID: 40475087
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
 

Author Comment

by:alevin16
ID: 40476689
Hello Michael,

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

Thanks
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 23

Expert Comment

by:Michael74
ID: 40477279
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
 
LVL 18

Assisted Solution

by:bonjour-aut
bonjour-aut earned 166 total points
ID: 40478480
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40478561
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
 

Author Comment

by:alevin16
ID: 40478831
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 167 total points
ID: 40479373
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

863 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

23 Experts available now in Live!

Get 1:1 Help Now