C# and OLEDB issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,
 
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 ) said:
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 said:
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,
Click to expand...
Click to expand...
 
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 ) said:
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 said:
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,
Click to expand...
Click to expand...
 
Hi,


Good to know it worked for you.


--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

SAL said:
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 ) said:
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 said:
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,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top