Link to home
Start Free TrialLog in
Avatar of piva1970
piva1970

asked on

Crystal Reports - Excel file that could change the name

My Crystal report is based on Excel and I am supposed to select Excel file name from the list of Excel files based on Parameter value entered in Crystal.
I am not sure is it possible.

If not then is there any way to execute .bat file from Crystal Reports and then that .bat file can rename Excel file according to entered Parameter value.
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Can't do this by parameter in CR although it is possible to change the data source using the menus. (Database>Set Datasource Location)
Avatar of Mike McCracken
Mike McCracken

Is it a fixed list of spreadsheets or does the list change with time?

mlmcc
Excel file is part of the connection. With crystal reports you cannot change the connectiuon on the fly. However instead of opening directly the excel files you can create linked tables in Access or SQL server, base the report on this database and create a dynamic query which is going to read one or another table depending of the parmeter value. This query should be used in a command or SQLStored procedure.
Avatar of piva1970

ASKER

It is a fixed list of spreadsheets. They just change the name of the Excel file.
Client doesn't like Access or SQL server solution.
Access will be just a layer to consolidate the excel files in one connection. Since the tables will be linked user will not even know that access is involved. The data still will be inserted in excel.
Another option is to create one report per excel file and add all report in a batch. Then depending of the parameter value the batch will run just one of the reports. You will need a viewer which supports report batches and also to create multiple reports which doffer just by the connection. I doubt your client will like this.
A couple of weeks ago I investigated the option to create one report per excel file but client doesn't like it because currently we would have let say 20 reports and then in one year maybe twenty more.
Why they don't like Access ?
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
MLMCC thanks. This idea could help. I have to test it and I'll let you know. Thank you for your help.
It will be  a bit of trouble and you will have to modify the Excel spreadsheets to include a file indicator.

mlmcc
One of the 3rd-party Crystal Reports desktop schedulers (see list at http://kenhamady.com/bookmarks.html) provides a command line argument for specifying the data source file used used in a native connection to MS Access, Excel, or Pervasive (ddf).

This should address your use scenario.