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

C# and OLEDB issue

P: n/a
SAL
Hello,

I am using OLEDB to access Excel, and what I have read you can access Named
Ranges but I am having trouble doing so. The following are lines of code
I've tried in my C# program to access Named ranges but don't work. Is the
something else I need to do?

string sqlCommand = "Select * From [sheet1!DataExtract$]";
string sqlCommand = "Select * From [sheet1!$DataExtract$]";
string sqlCommand = "Select * From [DataExtract$]";

I can access my Excel Spreadsheet like this, string sqlCommand = "Select *
From [Sheet1$]"; WITHOUT any troubles. However, when I do the Select
statement it returns extra rows that are blank because it doesn't know which
cell was last used. If I can access the Named Range I can eliminate this
issue, but I don't if C# or OLEDB is preventing me from accessing the Named
Range. The following is the error it gives me for the 3 scenarios I tried:

"'sheet1!DataExtract$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long."
"'sheet1!$DataExtract$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long."
"'DataExtract$' is not a valid name. Make sure that it does not include
invalid characters or punctuation and that it is not too long."

I have verified the spelling is correct and that the Named Range exists, and
I still get this error message. Has anyone encounter this before? If so,
how did you fix it?

Thanks,
Sep 19 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Hi,

Not sure if you can access a named range using OleDB, you can always try to
use automation

Take a look at : http://support.microsoft.com/kb/302084/
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"SAL" <SA*@discussions.microsoft.comwrote in message
news:B8**********************************@microsof t.com...
Hello,

I am using OLEDB to access Excel, and what I have read you can access
Named
Ranges but I am having trouble doing so. The following are lines of code
I've tried in my C# program to access Named ranges but don't work. Is the
something else I need to do?

string sqlCommand = "Select * From [sheet1!DataExtract$]";
string sqlCommand = "Select * From [sheet1!$DataExtract$]";
string sqlCommand = "Select * From [DataExtract$]";

I can access my Excel Spreadsheet like this, string sqlCommand = "Select *
From [Sheet1$]"; WITHOUT any troubles. However, when I do the Select
statement it returns extra rows that are blank because it doesn't know
which
cell was last used. If I can access the Named Range I can eliminate this
issue, but I don't if C# or OLEDB is preventing me from accessing the
Named
Range. The following is the error it gives me for the 3 scenarios I
tried:

"'sheet1!DataExtract$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long."
"'sheet1!$DataExtract$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long."
"'DataExtract$' is not a valid name. Make sure that it does not include
invalid characters or punctuation and that it is not too long."

I have verified the spelling is correct and that the Named Range exists,
and
I still get this error message. Has anyone encounter this before? If so,
how did you fix it?

Thanks,

Sep 19 '06 #2

P: n/a
SAL
Hi,

Well, I was wondering if it might be OLEDB. I'm tring to you use OLEDB
since I could have multiple excel file reads a day, and with different names,
and writing to SQL Server.

I'm going to look into OLEDB a little further before I take the route you
pointed me to.

Thanks,
"Ignacio Machin ( .NET/ C# MVP )" wrote:
Hi,

Not sure if you can access a named range using OleDB, you can always try to
use automation

Take a look at : http://support.microsoft.com/kb/302084/
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"SAL" <SA*@discussions.microsoft.comwrote in message
news:B8**********************************@microsof t.com...
Hello,

I am using OLEDB to access Excel, and what I have read you can access
Named
Ranges but I am having trouble doing so. The following are lines of code
I've tried in my C# program to access Named ranges but don't work. Is the
something else I need to do?

string sqlCommand = "Select * From [sheet1!DataExtract$]";
string sqlCommand = "Select * From [sheet1!$DataExtract$]";
string sqlCommand = "Select * From [DataExtract$]";

I can access my Excel Spreadsheet like this, string sqlCommand = "Select *
From [Sheet1$]"; WITHOUT any troubles. However, when I do the Select
statement it returns extra rows that are blank because it doesn't know
which
cell was last used. If I can access the Named Range I can eliminate this
issue, but I don't if C# or OLEDB is preventing me from accessing the
Named
Range. The following is the error it gives me for the 3 scenarios I
tried:

"'sheet1!DataExtract$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long."
"'sheet1!$DataExtract$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long."
"'DataExtract$' is not a valid name. Make sure that it does not include
invalid characters or punctuation and that it is not too long."

I have verified the spelling is correct and that the Named Range exists,
and
I still get this error message. Has anyone encounter this before? If so,
how did you fix it?

Thanks,


Sep 19 '06 #3

P: n/a
SAL
Turns out you can do it the way I'm doing it, but DO NOT USE the $ sign at
the end when calling a Named Range. You still need it when calling a
worksheet. I.E:

For calling a Worksheet
string sqlCommand = "Select * From [sheet1$]";

For calling Named Range
string sqlCommand = "Select * From [NamedRange]";

Once I changed it to what I just showed, everything worked like a charm.

Thanks,

"Ignacio Machin ( .NET/ C# MVP )" wrote:
Hi,

Not sure if you can access a named range using OleDB, you can always try to
use automation

Take a look at : http://support.microsoft.com/kb/302084/
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"SAL" <SA*@discussions.microsoft.comwrote in message
news:B8**********************************@microsof t.com...
Hello,

I am using OLEDB to access Excel, and what I have read you can access
Named
Ranges but I am having trouble doing so. The following are lines of code
I've tried in my C# program to access Named ranges but don't work. Is the
something else I need to do?

string sqlCommand = "Select * From [sheet1!DataExtract$]";
string sqlCommand = "Select * From [sheet1!$DataExtract$]";
string sqlCommand = "Select * From [DataExtract$]";

I can access my Excel Spreadsheet like this, string sqlCommand = "Select *
From [Sheet1$]"; WITHOUT any troubles. However, when I do the Select
statement it returns extra rows that are blank because it doesn't know
which
cell was last used. If I can access the Named Range I can eliminate this
issue, but I don't if C# or OLEDB is preventing me from accessing the
Named
Range. The following is the error it gives me for the 3 scenarios I
tried:

"'sheet1!DataExtract$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long."
"'sheet1!$DataExtract$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long."
"'DataExtract$' is not a valid name. Make sure that it does not include
invalid characters or punctuation and that it is not too long."

I have verified the spelling is correct and that the Named Range exists,
and
I still get this error message. Has anyone encounter this before? If so,
how did you fix it?

Thanks,


Sep 19 '06 #4

P: n/a
Hi,
Good to know it worked for you.
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"SAL" <SA*@discussions.microsoft.comwrote in message
news:F5**********************************@microsof t.com...
Turns out you can do it the way I'm doing it, but DO NOT USE the $ sign at
the end when calling a Named Range. You still need it when calling a
worksheet. I.E:

For calling a Worksheet
string sqlCommand = "Select * From [sheet1$]";

For calling Named Range
string sqlCommand = "Select * From [NamedRange]";

Once I changed it to what I just showed, everything worked like a charm.

Thanks,

"Ignacio Machin ( .NET/ C# MVP )" wrote:
>Hi,

Not sure if you can access a named range using OleDB, you can always try
to
use automation

Take a look at : http://support.microsoft.com/kb/302084/
--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation
"SAL" <SA*@discussions.microsoft.comwrote in message
news:B8**********************************@microso ft.com...
Hello,

I am using OLEDB to access Excel, and what I have read you can access
Named
Ranges but I am having trouble doing so. The following are lines of
code
I've tried in my C# program to access Named ranges but don't work. Is
the
something else I need to do?

string sqlCommand = "Select * From [sheet1!DataExtract$]";
string sqlCommand = "Select * From [sheet1!$DataExtract$]";
string sqlCommand = "Select * From [DataExtract$]";

I can access my Excel Spreadsheet like this, string sqlCommand =
"Select *
From [Sheet1$]"; WITHOUT any troubles. However, when I do the Select
statement it returns extra rows that are blank because it doesn't know
which
cell was last used. If I can access the Named Range I can eliminate
this
issue, but I don't if C# or OLEDB is preventing me from accessing the
Named
Range. The following is the error it gives me for the 3 scenarios I
tried:

"'sheet1!DataExtract$' is not a valid name. Make sure that it does not
include invalid characters or punctuation and that it is not too long."
"'sheet1!$DataExtract$' is not a valid name. Make sure that it does
not
include invalid characters or punctuation and that it is not too long."
"'DataExtract$' is not a valid name. Make sure that it does not
include
invalid characters or punctuation and that it is not too long."

I have verified the spelling is correct and that the Named Range
exists,
and
I still get this error message. Has anyone encounter this before? If
so,
how did you fix it?

Thanks,



Sep 20 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.