Cannot select a .XLSX file 1

[huytonscouser]

I have crystal 2011. Yet do not see an option to use a .xlsx file as a data source, when i select create new connection/access&excel
I am only able to see .xls files.

Yet when i export, i am able to export to a .xlsx file using "Microsoft Excel Workbook data only"

i'd attach a screen shot, but i am behind a firewall(i could place somewhere ?)


How do i select a .xlsx file as a datasource ?

 

[kray4660]

I see nothing in the documentation saying that you can use an .xlsx file as a data source.

I hope this helps.

 

[IdoMillet]

Set up an ODBC DSN to connect to the .xlsx file.
Then, use that DSN as the data source for your Crystal report.

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[huytonscouser]

I have created an odbc connection (using Microsoft excel driver *.xls *.xlsx *.xlsm *.xlsb)

But when i try an create new connection in crystal, i do not see the sheet or workbook to move to the right pane(selected tables).

i have tried all version of excel (12, 3, 4, 5.0, 97/2000) when i set up the odbc using the .xlsx file.

what i am missing to see the sheet/workbook form the .xlsx file ?

 

[kray4660]

I created a ODBC for an .xlsx file. But in Crystal in the database expert, it is only giving the option of adding a command for that data source.

 

[huytonscouser]

That's what i see also, i do not see any worksheets to move to the selected tables pane.

 

[shelby55]

Hi

I was able to do as suggested to link to an Access 2007 db but maybe that is different than linking Excel? In Crystal under create new connection I selected ODBC (RDO) and then found the data source that I had created, selected it and was able to see all the tables from that database.

 

[huytonscouser]

Thanks, that's a workaround i can look into.

But original question still remains, how do i use a .xlsx file as a datasource.

 

[IdoMillet]

In excel, did you specify named ranges for the "tables" in the excel file?

hth,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[huytonscouser]

no it's a straight vanilla .xlsx file.

 

[IdoMillet]

With ODBC, you must name the range or ranges you wish Crystal to recognize as Tables.

This is part of the procedure for using ODBC as a connection method to excel files. If you google the topic you'll find detailed descriptions.

Cheers,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[huytonscouser]

Ido, i defined a named range via :-

Formulas/define name specified the whole worksheet

=Sheet1!$A:$H/


saved as .xlsx

created an odbc using Microsoft excel driver *.xls *.xlsx *.xlsm *.xlsb

when i create new connection in Crystal(ODBC RDO) i still do not see the sheet to select.

 

[IdoMillet]

Don't select the whole worksheet. Select the tabular area that contains column headers and rows.

Cheers,
- Ido

view, export, burst, email, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[huytonscouser]

Ido, thanks that worked, i can now see the .xlsx file as an ODBC connection and select the sheet.

i created the named range via =Today!$A$1:$H$7594

However, each day this file may change, and have less or more rows, do i have to recreate the named range each day ?

 

[AndyMc]

When I've set up these kinds of connections, I just make sure that my range is big enough to handle what would be the largest number of records I would expect.

 

[huytonscouser]

Well that's not good, as if i compare two tables(from different .xlsx) then i will get a lot of noise/nulls.


Anyone kmow why .XLSX files can not be used directly as a datasource ?

 

[zenmind]

Did you try 'Save As' in Excel to convert your xlsx file to xls format?

 

[huytonscouser]

Yes, i can save as .xls.

Question is why can i not use a .xlsx file directly, without jumping through hoops, see above.