Sudden Issue Exporting Reports to Excel

[EternalMyrtle]

All of the sudden, when I go to export my reports to Excel I get an output error (see pic).

I have done this exact same thing dozens of times in the past without issue.

I tried repairing Access but it did not work.

There is no code being used to output the reports.

Has anyone ever seen this before and know of a solution?

 

[EternalMyrtle]

By the way, PDF works fine. This is just an Excel issue.

 

[vbaInet]

It would be useful to know how you're exporting the reports??

 

[EternalMyrtle]

Sorry, some of them have a button with a simple macro (see pic)

The rest I am just going up to the Ribbon and selecting External Data > Export>Excel.

NONE of the reports are exporting to Excel. It doesn't matter whether they have a button or not. Some of them have no code at all anywhere in the report so I don't think it is really a code issue in spite of the wording of the error message.

Queries and tables are exporting just fine (right click, then select Export >Excel) after the repair.

This was working a couple of weeks ago.

Unfortunately, I think it may be a problem with Access itself because I checked the backups and they are not working now either. I really don't want to uninstall and reinstall because I am using a standalone copy and can only do that so many times before they won't let me install again.

 

[vbaInet]

Have you tried exporting to one of the newer versions Excel?
And have you tried doing this in code instead of a macro?

 

[EternalMyrtle]

In a macro you can only export to the old versions (I am using Access 2007). I will try using code instead and post back. It is worth a shot.

 

[vbaInet]

I can't believe the Access macro will make the exports restrictive to older models of Excel. Isn't it Access 2007 you're using?

 

[EternalMyrtle]

Yes 2007. I know, I thought it was strange too but I checked and rechecked because I had the same idea.

I tried using code and get a different error. When I try putting in the newer Excel format (acFormatXLSX) I get a message that says "the format in which you are attempting to output the current object is not available".

Ugh, it seems like there is always some sort of problem I am chasing :\ .

Thanks for your suggestions. They were good. Unfortunately, I am starting to think a reinstall may be my only option...

 

[vbaInet]

Can I see the code you used.

 

[EternalMyrtle]

Used simplest possible code:

Private Sub btnExcel_Click()
DoCmd.OutputTo acOutputReport, , acFormatXLS
End Sub

If I change acFormatXLS to "acFormatTXT", it works fine. Once back to XLS or XLSX I get output errors.

I am about ready to give up for the moment. It seems very odd that this crept up out of nowhere.

 

[vbaInet]

What do you mean by "output errors"? You need to be more specific.
Also try this:

DoCmd.OutputTo acOutputReport, "Your report name", "Microsoft Excel (*.xls)", "Path to file"

What version of Access is the database itself? Not the version of Access you're using to open it but the version it was made.

 

[EternalMyrtle]

I made it myself in Access 2007 start to finish including all the code.

Here is the exact error message when I alter the code according to your suggestions (incidentally, the same error as before with my code).

All I can say is that with or without code the function of exporting a report to Excel has suddenly stopped working. I really don't think it is related to code.

 

[vbaInet]

Does it affect everyone or just yourself?

I think this link addresses your problem:
http://allenbrowne.com/Access2007.html#Gone

Search for the phrase "Prior to Service Pack 2" and you'll find it under Compatibility.

 

[EternalMyrtle]

Thank you for finding this info.

It's strange that it was working fine on my computer up until now but the Service Pack issue seems like a likely culprit. To be honest, the last time I outputted a report to Excel it was on a coworker's computer (different OS); however I have done it from mine in the past.

My work computer has Windows 7 and it the Windows update page says the SP1 is the latest for that OS http://windows.microsoft.com/en-us/windows/service-packs-download#sptabs=win7 Not sure where that leaves me but I will see what I can do, if anything, when I return to the office tomorrow.

Thanks for all your help

 

[EternalMyrtle]

Ok, so I found this SP2 update for Office 2010 (the rest of my office software is 2010): http://www.microsoft.com/en-us/download/details.aspx?id=39667

Hopefully this will fix the problem. I have high hopes because my coworker's computer has Office 2013 so maybe that's why it worked from her machine.

I will let you all know tomorrow. Thanks again!

 

[EternalMyrtle]

I may also need this one for 2007

http://www.microsoft.com/en-us/download/details.aspx?id=5

Will see tomorrow and let you know

 

[vbaInet]

I'll wait for your updates.

 

[EternalMyrtle]

So, I read through Allen Browne's informative page more carefully and discovered the Service Pack 2 update he is referring to is indeed the Office 2007 one. Well, that was already installed long ago. I also noticed that my error messages are not the same. If it were related the the Office 2007 SP2, the error message would say: "Run-time error '2282': The format in which you are attempting to output the current object is not available" but mine is different, as you can see in the images: Error & ErrorVBA.

I tried to install the SP2 update for Office 2010 and got an error message saying "there are no products affected by this package installed on the system". Not sure why since I have 2010 software but my updates are installed automatically so...

I may try to workaround this by outputting the query that the report is based on rather than the report itself but i am still :banghead: wondering why this issue would pop up out of nowhere. Perhaps related to another update if I had to guess...

 

[EternalMyrtle]

Ran downstairs and outputted a report to Excel on my coworker's computer (running Office 2013 and Access 2007) and it worked fine.

So, it is either something with my Office 2010 software or perhaps the operating system (hers is Windows 8, mine is Windows 7). Not sure. Either way I am not going to stress about it much more since I don't think I will have much luck chasing down a solution.

 

[vbaInet]

Perhaps an update that you may have installed earlier may have caused the issue. You may need to re-install Access on your machine.