Error in Analyzing Report with MS Excel (1 Viewer)

[spaceball12]

I have a report that is 82 pages long that I'd like to send to Excel. When I choose to "Analyze It with MS Excel", it says it's outputting page 2, 4, 6, 8, 10, 12, 14 and then just stops. No Excel file is created and I am returned to the print preview view of the form.

I thought that perhaps the length of the report was the cause of this, so I created a second report that is formatted so that it is only 16 pages long. When I try to analyze it in Excel, it says it's outputting page 1, 2, 3, then stops. Again, no Excel file is created.

I tried Exporting the reports into Excel as well and I get the same results. It just seems like these reports won't go into Excel.

I have sent many reports, even in the same database, to Excel without any problems. I am at a loss for ideas on how to fix this problem. Any suggestions?

Thanks!

 

[RCurtin]

I'm actually having the same problem. I can click Analyze with Excel with any of my reports except this one.

Its only 6 pages long and is based on a query. Going to File/Save as and trying to export it to Excel doesn't work either.

I don't get any error message - just teh screen flashes up to say it is outputting it and then nothing!

Any ideas why this might be?

 

[RCurtin]

Analyze with Excel doesn't work when tables are linked!?

OK - I am going to list the steps I took.

Ok I tried doing this in VB instead using the following code:
DoCmd.OutputTo acReport, "Job Numbers and Positions", acFormatXLS

First I was getting strange errors like "2455 You entered an expression that has an invalid reference to the property |". I decompiled the database a few times and that stopped that error.

I exported the report, relavent queries to a new database called db5.mdb as I was going to post it here on the forum. I exported the relavent tables from the BE to the same new database (db5.mdb). Then it worked!

So then I thought it might be a corruption issue and exported everything to a new database but it didn't work when I did this. When I ran the code above it opened the Save As dialog box but did not save the Excel sheet. As before when I tried 'Analyzing with Excel' or exporting to Excel nothing happened.

So finally in the new database I tried replacing the linked tables with the actual tables and then it worked!

Has anyone any ideas what could be going on with this?

 

[Dennisk]

have you tried exporting the query to a csv file then importing that into Excel.

You could have a problem with the versions of the text drivers used by the Jet Engine when exporting.

 

[RCurtin]

Thanks for that Dennisk. I did try your suggestion and I was able to import it fine.

Anyways I just found the problem - there was a combo box in the report - its rowsource was a look-up table. I changed it to a textbox and changed the underlying query to include the data from the lookup table. That solved it.

I've been trying to figure this out for hours! I guess it makes since when you remember that you can't export subreports using the analyze to Excel feature. This is probably a similar issue.