OLEDB run time error

[KeerthanaJegann]

Hello,

I am using GET COLLECTION action in OLEDB VBO to run a query. So excel file in which I'm running the query is located in a teams folder. So when I step over it in the debug mode, run query action in OLEDB works fine. But when I run it from control room, its throwing an error saying - "Shift Info$' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long". -> Here Shift Info is the excel sheet name. 

So the interesting thing is, its working fine in the debug mode, only at the run time, I get the issue. Even the SQL query is correct with no syntax error. Otherwise, it would not work in the debug mode. Can someone please help me with it. 

Thanks!

[PabloSarabia]

Hello,

¿Are you running your process in the same machine as you debug? If not, check if the OLEDB library (dlls) are correctly instaled.

Can you open with the file using Excel and check the Sheet name? 

Other thing that you can try is to open always the first sheet of the book with this code:

using (OleDbConnection conn = new OleDbConnection(connString))
{
    conn.Open();
    dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
    Sheet1= dtSchema.Rows[0].Field<string>("TABLE_NAME");
}

I paste here an extra links to help you:
https://forums.asp.net/t/1995972.aspx?error+Items+is+not+a+valid+name+Make+sure+that+it+does+not+include+invalid+characters+or+punctuation+and+that+it+is+not+too+long+


Bye :)

[KeerthanaJegann]

I have tried all the options, it didn't work. Even i tried changing the sheet name, but the new sheet name is not working as well.

[PabloSarabia]

Hello,

Oh... that's to bad.

Check if exist any "hidden" sheet in your file.

If you want, send me the Excel without your data to try to execute in my own robot.


Bye :)

[KeerthanaJegann · Accepted solution]

I got it worked.  There were two reasons causing this error - 1) While debugging, every time when I open the OLEDB connection, I don't close the connection. 2)Opening excel instance before connecting to oledb is the second issue.