cyimxtck
asked on
Activate worksheet C# Excel
I have tried many things to try and get Excel to have a selected sheet in an Add-In. So everything works in my code until I get to line: xlApp.ActiveWorkbook.Sheet s[1].Activ ate();
Passing the name of the sheet to activate would be the best route to success but at this point I am trying anything to get this to work.
Here is some of the code that I have tried so far:
const String Config = "Config";
private Microsoft.Office.Interop.E xcel.Appli cation app = null;
private Microsoft.Office.Interop.E xcel.Works heet worksheet = null;
/// <summary>
/// Read the config sheet cells
/// </summary>
/// <param name="Row">Row to Read</param>
/// <param name="Col">Column to Read</param>
/// <returns></returns>
public String CellValueStr(
Int64 Row
, Int64 Col
)
{
String cellValue = String.Empty;
String CurrentSheet = Config;
var xlApp = new Microsoft.Office.Interop.E xcel.Appli cation();
Excel.Workbooks xlWorkbooks = xlApp.Workbooks;
xlApp.ActiveWorkbook.Sheet s[1].Activ ate();
xlWorkbooks.Sheets[1].Acti vate();
xlSheet.Activate();
xlWorkbook = xlWorkbooks.Open(@"C:\file name.xlsx" );
//Excel.Worksheet sheet = (Excel.Worksheet)this.app. Worksheets [CurrentSh eet];
//Excel.Worksheet sheet = (Excel.Worksheet)ActiveWor kbook.Shee ts["Sheet2 "];
//((Excel.Worksheet)this.a pp.ActiveW orkbook.Sh eets[1]).S elect();
//((Excel.Worksheet)this.A pplication .ActiveWor kbook.Shee ts[1]).Sel ect();
//sheet.Select(Type.Missin g);
//Worksheet sheet = (Worksheet)Workbook.Sheets ["ASD"];
//sheet.Select(Type.Missin g);
System.Data.OleDb.OleDbCon nection MyConnection;
MyConnection = new System.Data.OleDb.OleDbCon nection("P rovider=Mi crosoft.AC E.OLEDB.12 .0;Data Source=" + tbFileName.Text + ";Extended Properties=Excel 12.0;");
Microsoft.Office.Interop.E xcel.Appli cation xlApp = new Microsoft.Office.Interop.E xcel.Appli cation();
//get the workbook
Microsoft.Office.Interop.E xcel.Workb ook excelBook = xlApp.Workbooks.Open(tbFil eName.Text );
//get the first worksheet
Microsoft.Office.Interop.E xcel.Works heet wSheet = excelBook.Sheets.Item[1];
var xls = new Microsoft.Office.Interop.E xcel.Appli cation();
xls.ActiveWorkbook.Sheets[ CurrentShe et].Activa te();
xls.ActiveWorkbook.Sheets[ 1].Activat e();
xls.ActiveSheet();
cellValue = (string)(worksheet.Cells[C ol, Row] as Excel.Range).Value;
return cellValue;
}
Visual Studio 2015 and Excel 2016, 2013 and 2010
Please let me know if I am losing my mind as it seems none of the examples on this site work out? (More coffee?)
So as an end result I would like to retrieve various values in certain sheets to perform actions based on these values. The business currently has this functionality but it is written in VBA which is not going to work long term for us.
Thanks in Advance!
Passing the name of the sheet to activate would be the best route to success but at this point I am trying anything to get this to work.
Here is some of the code that I have tried so far:
const String Config = "Config";
private Microsoft.Office.Interop.E
private Microsoft.Office.Interop.E
/// <summary>
/// Read the config sheet cells
/// </summary>
/// <param name="Row">Row to Read</param>
/// <param name="Col">Column to Read</param>
/// <returns></returns>
public String CellValueStr(
Int64 Row
, Int64 Col
)
{
String cellValue = String.Empty;
String CurrentSheet = Config;
var xlApp = new Microsoft.Office.Interop.E
Excel.Workbooks xlWorkbooks = xlApp.Workbooks;
xlApp.ActiveWorkbook.Sheet
xlWorkbooks.Sheets[1].Acti
xlSheet.Activate();
xlWorkbook = xlWorkbooks.Open(@"C:\file
//Excel.Worksheet sheet = (Excel.Worksheet)this.app.
//Excel.Worksheet sheet = (Excel.Worksheet)ActiveWor
//((Excel.Worksheet)this.a
//((Excel.Worksheet)this.A
//sheet.Select(Type.Missin
//Worksheet sheet = (Worksheet)Workbook.Sheets
//sheet.Select(Type.Missin
System.Data.OleDb.OleDbCon
MyConnection = new System.Data.OleDb.OleDbCon
Microsoft.Office.Interop.E
//get the workbook
Microsoft.Office.Interop.E
//get the first worksheet
Microsoft.Office.Interop.E
var xls = new Microsoft.Office.Interop.E
xls.ActiveWorkbook.Sheets[
xls.ActiveWorkbook.Sheets[
xls.ActiveSheet();
cellValue = (string)(worksheet.Cells[C
return cellValue;
}
Visual Studio 2015 and Excel 2016, 2013 and 2010
Please let me know if I am losing my mind as it seems none of the examples on this site work out? (More coffee?)
So as an end result I would like to retrieve various values in certain sheets to perform actions based on these values. The business currently has this functionality but it is written in VBA which is not going to work long term for us.
Thanks in Advance!
ASKER
Same exception I get with 1 or 0...
System.NullReferenceExcept ion was unhandled by user code
HResult=-2147467261
Message=Object reference not set to an instance of an object.
Source=ExcelUpload
StackTrace:
at ExcelUpload.Helper.CellVal ueStr(Int6 4 Row, Int64 Col) in C:\Users\Train Man\documents\visual studio 2015\Projects\ExcelUpload\ ExcelUploa d\Helper.c s:line 37
at ExcelUpload.Master.Cell() in C:\Users\Train Man\documents\visual studio 2015\Projects\ExcelUpload\ ExcelUploa d\Master.c s:line 31
at ExcelUpload.Master.testwri te() in C:\Users\Train Man\documents\visual studio 2015\Projects\ExcelUpload\ ExcelUploa d\Master.c s:line 39
at ExcelUpload.Upload.button1 _Click(Obj ect sender, RibbonControlEventArgs e) in C:\Users\Train Man\documents\visual studio 2015\Projects\ExcelUpload\ ExcelUploa d\Upload.c s:line 20
at Microsoft.Office.Tools.Rib bon.Ribbon PropertySt orage.Cont rolActionR aise(IRibb onControl control)
at Microsoft.Office.Tools.Rib bon.Ribbon PropertySt orage.Butt onClickCal lback(Ribb onComponen tImpl component, Object[] args)
at Microsoft.Office.Tools.Rib bon.Ribbon ManagerImp l.Invoke(R ibbonCompo nentCallba ck callback, Object[] args)
at Microsoft.Office.Tools.Rib bon.Ribbon ManagerImp l.System.R eflection. IReflect.I nvokeMembe r(String name, BindingFlags invokeAttr, Binder binder, Object target, Object[] args, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParameters)
InnerException:
System.NullReferenceExcept
HResult=-2147467261
Message=Object reference not set to an instance of an object.
Source=ExcelUpload
StackTrace:
at ExcelUpload.Helper.CellVal
at ExcelUpload.Master.Cell() in C:\Users\Train Man\documents\visual studio 2015\Projects\ExcelUpload\
at ExcelUpload.Master.testwri
at ExcelUpload.Upload.button1
at Microsoft.Office.Tools.Rib
at Microsoft.Office.Tools.Rib
at Microsoft.Office.Tools.Rib
at Microsoft.Office.Tools.Rib
InnerException:
The notion of Active... works only when the Excel application is visible.
Reference the workbook directly instead.
cellValue = (string) xlApp.Workbooks[1].Sheets[ 1].Cells[C ol, Row] as Excel.Range).Value;
If you have many operations to perform in the same sheet, you might want to define variable that you will reuse for each operation
var mySheet = xlApp.Workbooks[1].Sheets[ 1];
cellValue = (string) mySheet.Cells[Col, Row] as Excel.Range).Value;
Reference the workbook directly instead.
cellValue = (string) xlApp.Workbooks[1].Sheets[
If you have many operations to perform in the same sheet, you might want to define variable that you will reuse for each operation
var mySheet = xlApp.Workbooks[1].Sheets[
cellValue = (string) mySheet.Cells[Col, Row] as Excel.Range).Value;
In future please say what error you get should you have one, I expected the code to be running but not doing what you wanted. My fault for assuming :-(
You currently have:
Try opening a workbook first, does this change help?
You currently have:
xlApp.ActiveWorkbook.Sheets[1].Activate();
xlWorkbooks.Sheets[1].Activate();
xlSheet.Activate();
xlWorkbook = xlWorkbooks.Open(@"C:\filename.xlsx");
Try opening a workbook first, does this change help?
xlWorkbook = xlWorkbooks.Open(@"C:\filename.xlsx");
xlApp.ActiveWorkbook.Sheets[1].Activate();
xlWorkbooks.Sheets[1].Activate();
xlSheet.Activate();
I am guessing that you are converting a recorded VBA macro into C#. If so, the first thing you should realize is that recorded macros include a large number of .Activate and .Select statements--none of which belong in a professionally written macro. Please post the VBA macro so it can be optimized before you try to convert to C#.
You should also be aware that Microsoft is pushing JavaScript as the macro language of the future. JavaScript macros will eventually be able to run on any Excel endpoint (iOS, Android, Windows desktop, Mac OS X, Excel Online). Right now, you can use Visual Studio to develop JavaScript macros that work on Windows desktop in Excel 2016.
You should also be aware that Microsoft is pushing JavaScript as the macro language of the future. JavaScript macros will eventually be able to run on any Excel endpoint (iOS, Android, Windows desktop, Mac OS X, Excel Online). Right now, you can use Visual Studio to develop JavaScript macros that work on Windows desktop in Excel 2016.
ASKER
@Jacques here is the error I get with your code:
Severity Code Description Project File Line
Error CS0039 Cannot convert type 'string' to 'Microsoft.Office.Interop. Excel.Rang e' via a reference conversion, boxing conversion, unboxing conversion, wrapping conversion, or null type conversion ExcelUpload C:\documents\visual studio 2015\Projects\ExcelUpload\ ExcelUploa d\Helper.c s 40
Severity Code Description Project File Line
Error CS0039 Cannot convert type 'string' to 'Microsoft.Office.Interop.
ASKER
@byundt
I cannot upload the VBA because it is something that I don't have. Business requirements and a "locked" spreadsheet is what I have.
The task is pretty straight forward it seems but getting this to work is WAY more difficult than I would have ever expected.
Thanks,
B
I cannot upload the VBA because it is something that I don't have. Business requirements and a "locked" spreadsheet is what I have.
The task is pretty straight forward it seems but getting this to work is WAY more difficult than I would have ever expected.
Thanks,
B
If the workbook can be opened, but the VBA is password protected, I might be able to remove the password protection for you.
ASKER
I appreciate the help along those lines and would take you up on that but....if that ever got out that happened (yikes)....would violate the terms of my contract. :(
If I can just get this to read the cells I think I can pull it together based on the information that they provided me.
If I can just get this to read the cells I think I can pull it together based on the information that they provided me.
In that case, be aware that there is commercial software that can remove the password protection on VBA code, and you can find it easily with a Bing or Google search. The software should cost you less than an hour of your billing rate.
I did not spot the As Range at the end of the command. Try the following:
cellValue = (string) xlApp.Workbooks[1].Sheets[ 1].Cells[C ol, Row].Value;
cellValue = (string) xlApp.Workbooks[1].Sheets[
ASKER
Here is what I am getting now:
An exception of type 'System.Runtime.InteropSer vices.COME xception' occurred in ExcelUpload.dll but was not handled in user code
Additional information: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
xlApp.Workbooks[1].Sheets = 'xlApp.Workbooks[1].Sheets ' threw an exception of type 'System.Runtime.InteropSer vices.COME xception'
An exception of type 'System.Runtime.InteropSer
Additional information: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))
xlApp.Workbooks[1].Sheets = 'xlApp.Workbooks[1].Sheets
ASKER
I am a solid ADO guy so this isn't in my wheel house obviously....is there a "better way" to do this? (if that is a dumb question, please disregard) LOL
COM always seems to have problems in my experience (which, admittedly, is very little). This is very perplexing that the difficulty in what seems to be a fairly trivial task is staggering.
Thanks for all your help so far and very quick answers.
COM always seems to have problems in my experience (which, admittedly, is very little). This is very perplexing that the difficulty in what seems to be a fairly trivial task is staggering.
Thanks for all your help so far and very quick answers.
An invalid index exception on xlApp.Workbooks[1].Sheets seems to indicate that there is no Worksheet in the Workbook.
You can verify that by running the Excel application visible (xlApp.Visible = True)
You can verify that by running the Excel application visible (xlApp.Visible = True)
ASKER
There is a sheet there....I am running this from the IDE
1) run the project (F5)
2) Excel pops up and I select the saved spreadsheet
3) click in the Add-In ribbon and click the button I placed in there
4) walk through the code and let VS tell me what happens (F10/11, hovering, immediate window, etc.)
That error has popped up and not sure what is happening?
So the file is saved, which will eliminate some non-sense that "Hey I cannot read that unsaved file" The spreadsheet has 2 worksheets in there so it wouldn't choke on a zero based index....same error when I did this as well:
cellValue = (string)xlApp.Workbooks[0] .Sheets[0] .Cells[Col , Row].Value;
1) run the project (F5)
2) Excel pops up and I select the saved spreadsheet
3) click in the Add-In ribbon and click the button I placed in there
4) walk through the code and let VS tell me what happens (F10/11, hovering, immediate window, etc.)
That error has popped up and not sure what is happening?
So the file is saved, which will eliminate some non-sense that "Hey I cannot read that unsaved file" The spreadsheet has 2 worksheets in there so it wouldn't choke on a zero based index....same error when I did this as well:
cellValue = (string)xlApp.Workbooks[0]
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
xlApp.ActiveWorkbook.Sheet
to
xlApp.ActiveWorkbook.Sheet
make a difference (zero based indexing)