Problem Note 32722: SAS/ACCESS® Interface to OLEDB fails to create new .xlsx files correctly and generates invalid-format errors when they files are opened by Excel 2007
 |  |  |  |
When you use the Microsoft Access Connectivity Engine (ACE) to open new .xlsx files that are created by SAS/ACCESS Interface to OLEDB, the following error is generated:
Excel cannot open the file (yourfile.xls) because the
file format or file extension is not valid. Verify that
the file has not been corrupted and that the file
extension matches the format of the file.
This problem occurs when you use SAS/ACCESS Interface to OLEDB to create a new .xlsx file, as shown in the following example:
libname mylib oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=C:\users\book2.xlsx;
Extended Properties=Excel 12.0";
data mylib.'class'n;
set sashelp.class;
run;
libname mylib clear;
To circumvent the problem do one of the following:
- Change the EXTENDED PROPERTIES= parameter to use XML properties that are listed in the INIT_STRING= option so that it looks similar to the following:
libname mylib oledb init_string="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\users\book2.xlsx; Extended Properties='Excel 12.0 xml'"; data mylib.'class'n; set sashelp.class; run; libname mylib clear;
- Create a new .xlsx workbook in Excel. Then save the .xlsx file.
- Close the Excel workbook.
- Append SAS tables using the same code example shown previously.
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to OLE DB | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.1 TS1M3 | |
| Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.1 TS1M3 | |||
| Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.1 TS1M3 | |||
| Microsoft Windows XP 64-bit Edition | 9.1 TS1M3 | |||
| Microsoft® Windows® for x64 | 9.1 TS1M3 | |||
| Microsoft Windows 2000 Advanced Server | 9.1 TS1M3 | |||
| Microsoft Windows 2000 Datacenter Server | 9.1 TS1M3 | |||
| Microsoft Windows 2000 Server | 9.1 TS1M3 | |||
| Microsoft Windows 2000 Professional | 9.1 TS1M3 | |||
| Microsoft Windows NT Workstation | 9.1 TS1M3 | |||
| Microsoft Windows Server 2003 Datacenter Edition | 9.1 TS1M3 | |||
| Microsoft Windows Server 2003 Enterprise Edition | 9.1 TS1M3 | |||
| Microsoft Windows Server 2003 Standard Edition | 9.1 TS1M3 | |||
| Microsoft Windows XP Professional | 9.1 TS1M3 | |||
| Windows Vista | 9.1 TS1M3 | |||
When you use SAS/ACCESS Interface to OLEDB with the ACE provider, SAS/ACCESS Interface to OLEDB creates an .xlsx file. When you open this file in Excel, an invalid-format error is generated. However, you can successfully add a table to an existing .xlsx file.
| Type: | Problem Note |
| Priority: | medium |
| Date Modified: | 2008-07-22 14:58:59 |
| Date Created: | 2008-07-21 14:10:31 |