Must open, 'save as' excel files from Crystal 9 Ent for Access to read

[blkhills]

Several reports are scheduled in Crystal Enterprise 9 to create to excel files overnight. A batch job runs to read these excel spreadsheets in to MS Access data warehouse. The data warehouse will only successfully open and import the data in the spreadsheets that have just 1 worksheet. Any excel file with multiple worksheets must be manually opened in Excel, no changes made, just re-saved with the 'Save As' command. Then the data warehouse macros read the data fine and import correctly into MS Access. Anybody have any ideas? Absolutely nothing is changed inside of the multiple worksheet files, just re-saved with 'Save As'.

Thanks

 

[synapsevampire]

Data Warehouse and MS Access in the same sentence is very odd.

At any rate, you can easily create a macro to open and do a save as if that's your fix.

You can also have MS Access read additional worksheets within an Excel workbook using VBA, so I don't understand the issue.

Another thing that may help is applying the latest service pack:

http://support.businessobjects.com/downloads/updates/service_packs/default.asp

-k

 

[blkhills]

The issue is why can't a Microsoft product - Access - open a file cleanly from another Microsoft product. I'm wondering if there is something I don't know about Crystal Enterprise where the report is generated from that would allow the use of worksheets 2 and 3 without having to open the file and do a 'Save As'. What is effectively happening when a Save As is done that then allows Access to recognize the additional worksheets? I have no experience with VBA so at least at this point in time that's not an option. I started pursuing the macro to open and do a save as and will probably continue down that road.

 

[synapsevampire]

I think that the problem may be in Crystal's export of the file in that it's not a clean Excel file according to your import dll in Access.

I would consider applying the service packs to Crystal and Access, this may resolve all of this.

-k

 

[blkhills]

Thanks!