By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,805 Members | 1,273 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,805 IT Pros & Developers. It's quick & easy.

OpenDataSource fails from worksheet name starts with a number in xls file

P: 12
Hi,

I can access data from xls file using OpenDataSource. But if the worksheet name starts with numeric character it gives error. Scripts are as follows:

If the worksheet name is 'Sheet1' it works fine with script below:
Expand|Select|Wrap|Line Numbers
  1. select * from OpenDataSource 
  2.     ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";Extended properties=Excel 8.0')...[Sheet1$]
  3.  
But if the worksheet name is '2007data' the script below gives error:
Expand|Select|Wrap|Line Numbers
  1. select * from OpenDataSource
  2.     ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";Extended properties=Excel 8.0')...[2007data$]
  3.  
Can anyone give me a solution? I need to keep the worksheet name starting with numeric character.

Thanks.
Mar 2 '08 #1
Share this Question
Share on Google+
1 Reply


P: 12
Hi,

I got a solution for worksheet namely '2007data' below and works fine:

Expand|Select|Wrap|Line Numbers
  1. select * from OpenDataSource
  2.     ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";Extended properties=Excel 8.0')...['2007data$']
  3.  
But strange again!! Script below does not work for worksheet namely 'Sheet1' in the same way:
Expand|Select|Wrap|Line Numbers
  1. select * from OpenDataSource
  2.     ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";Extended properties=Excel 8.0')...['Sheet1$']
  3.  
Please come anyone and give a unique solution for both cases.

Thanks.





Hi,

I can access data from xls file using OpenDataSource. But if the worksheet name starts with numeric character it gives error. Scripts are as follows:

If the worksheet name is 'Sheet1' it works fine with script below:
Expand|Select|Wrap|Line Numbers
  1. select * from OpenDataSource 
  2.     ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";Extended properties=Excel 8.0')...[Sheet1$]
  3.  
But if the worksheet name is '2007data' the script below gives error:
Expand|Select|Wrap|Line Numbers
  1. select * from OpenDataSource
  2.     ( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";Extended properties=Excel 8.0')...[2007data$]
  3.  
Can anyone give me a solution? I need to keep the worksheet name starting with numeric character.

Thanks.
Mar 2 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.