Issues with excel object....

Im getting a couple of errors....working with this excel object

Any Help would be much appreciated.

You can see a better looking example of the code here:
http://share.codelove.org/BlahBlah-2BdWFb9Q.html

The errors are:

error CS1501: No overload for method 'Open' takes '2' arguments
error CS1955: Non-invocable member 'Microsoft.Office.Interop.Excel._Workbook.Sheets' cannot be used like a method.

[code]using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;


namespace CSharpToMySQL
{
public partial class frmMain : Form
{
private System.Data.Odbc.OdbcConnection OdbcCon;
private System.Data.Odbc.OdbcCommand OdbcCom;
private System.Data.Odbc.OdbcCommand OdbcComAlt;
private System.Data.Odbc.OdbcDataReader OdbcDR;
private Microsoft.Office.Interop.Excel.Workbook wb;
private Microsoft.Office.Interop.Excel.Worksheet sh;
private Microsoft.Office.Interop.Excel.Range rng;
private string ConStr;
private Form frmAbout;

public frmMain()
{
InitializeComponent();
}

private void btnExcelValue_Click(object sender, EventArgs e)
{

if (OdbcCon.State == ConnectionState.Open)
{
wb = Microsoft.Office.Interop.Excel.Workbooks.Open(@c: est.xls,1);
sh = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets(1);
rng = sh.get_Range("A1","A1");
rng.Value2.ToString();
txtLog.AppendText("The Value is " + rng.Value2.ToString());
}
}[/code]


Comments

  • : Im getting a couple of errors....working with this excel object
    :
    : Any Help would be much appreciated.
    :
    : You can see a better looking example of the code here:
    : http://share.codelove.org/BlahBlah-2BdWFb9Q.html
    :
    : The errors are:
    :
    : error CS1501: No overload for method 'Open' takes '2' arguments
    : error CS1955: Non-invocable member
    : 'Microsoft.Office.Interop.Excel._Workbook.Sheets' cannot be used
    : like a method.
    :
    : [code]: using System;
    : using System.Collections.Generic;
    : using System.ComponentModel;
    : using System.Data;
    : using System.Drawing;
    : using System.Text;
    : using System.Windows.Forms;
    : using Microsoft.Office.Interop.Excel;
    : using System.Runtime.InteropServices;
    :
    :
    : namespace CSharpToMySQL
    : {
    : public partial class frmMain : Form
    : {
    : private System.Data.Odbc.OdbcConnection OdbcCon;
    : private System.Data.Odbc.OdbcCommand OdbcCom;
    : private System.Data.Odbc.OdbcCommand OdbcComAlt;
    : private System.Data.Odbc.OdbcDataReader OdbcDR;
    : private Microsoft.Office.Interop.Excel.Workbook wb;
    : private Microsoft.Office.Interop.Excel.Worksheet sh;
    : private Microsoft.Office.Interop.Excel.Range rng;
    : private string ConStr;
    : private Form frmAbout;
    :
    : public frmMain()
    : {
    : InitializeComponent();
    : }
    :
    : private void btnExcelValue_Click(object sender, EventArgs e)
    : {
    :
    : if (OdbcCon.State == ConnectionState.Open)
    : {
    : wb = Microsoft.Office.Interop.Excel.Workbooks.Open(@c: est.xls,1);
    : sh = (Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets(1);
    : rng = sh.get_Range("A1","A1");
    : rng.Value2.ToString();
    : txtLog.AppendText("The Value is " + rng.Value2.ToString());
    : }
    : }[/code]:
    :
    :
    :

    The Workbooks.Open method has 15 arguments. IIRC they all have to be included when calling via the office interop however I'm not 100% sure about that. Anyway here is the method call that I use for Excel 2003:
    [code]
    app = new Microsoft.Office.Interop.Excel.ApplicationClass();
    wb = app.Workbooks.Open(
    filePath,
    0,
    false,
    5,
    System.Reflection.Missing.Value,
    System.Reflection.Missing.Value,
    false,
    System.Reflection.Missing.Value,
    System.Reflection.Missing.Value,
    true,
    false,
    System.Reflection.Missing.Value,
    false,
    false,
    false);
    [/code]
    Frankly I don't remember what all the parameters do. I normally just copy/paste it out of convenience. Here is a link to the [link=http://msdn.microsoft.com/en-us/library/bb179167.aspx]Excel object reference[/link] which may be of some help to you.

    The second error you're receiving is due to the fact that the Worksheets property is a collection. You should be able to get the first worksheet via wb.Sheets[0] and possibly wb.Sheets["name"].
  • Thanks for the reply, I got the file open, i guess it was just that i needed to add all of the arguments.

    I now have a separate problem,

    I'm having a issue when the app gets to this line it crashes.

    rng2 = sh.get_Range("D" + p, "D" + p);

    The first string rng1 will get and write properly, I'm assuming that the 2nd string overwrites the first ?

    also I declare rng, rng1, rng2 all in the same line. will this cause a problem.

    [code]private Microsoft.Office.Interop.Excel.Range rng, rng1, rng2;

    ///////////////////////////////////////////////////////////////////////////////////////////////
    // /
    // /
    ///////////////////////////////////////////////////////////////////////////////////////////////
    private void btnAutomate_Click(object sender, EventArgs e)
    {

    for (int i = 6, p = 6, n = 0; n < 7; i++, p++)
    {
    txtLog.AppendText("Inside Automate
    ");
    rng1 = sh.get_Range("A" + i,"A" + i);
    txtLog.AppendText("Inside Automate Got Range 1
    ");
    rng1.Value2.ToString();
    txtLog.AppendText("Inside Automate Got string 1
    ");


    rng2 = sh.get_Range("D" + p, "D" + p);
    txtLog.AppendText("Inside Automate Got Ranges 2
    ");
    rng2.Value2.ToString();
    txtLog.AppendText("Inside Automate Got Strings 2
    " + rng1.Value2.ToString());
    if (rng1.Value2.ToString()== null)
    {
    txtLog.AppendText("Inside Automate/IF Before n
    ");
    n += 1; //null tracker
    continue;
    }
    n = 0;
    txtLog.AppendText("Inside Automate Calling Databasewrite
    ");
    DatabaseWrite();
    }
    wb.SaveCopyAs(@C:CompleteUpdate.xls);
    }
    ///////////////////////////////////////////////////////////////////////////////////////////////
    // /
    // /
    ///////////////////////////////////////////////////////////////////////////////////////////////
    private void DatabaseWrite()
    {

    if (OdbcCon.State == ConnectionState.Open)
    {
    OdbcCom = new System.Data.Odbc.OdbcCommand("UPDATE `products` SET `products_price`='" + rng2.Value2.ToString() + "' WHERE `products_model`='" + rng1.Value2.ToString() + "'", OdbcCon);
    OdbcDR = OdbcCom.ExecuteReader();
    OdbcComAlt = new System.Data.Odbc.OdbcCommand("SELECT `products_price` FROM `products` WHERE `products_model`='" + rng1.Value2.ToString()+ "'", OdbcCon);
    OdbcDR = OdbcComAlt.ExecuteReader();
    while (OdbcDR.Read())
    {

    if(OdbcDR[0]==null)
    {
    rng1.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
    txtLog.AppendText("There is no item for " + rng1.Value2.ToString() + "
    ");
    return;
    }
    txtLog.AppendText("The New Price for " + rng1.Value2.ToString() + " is " + OdbcDR[0] + "
    ");
    }

    }
    }
    ///////////////////////////////////////////////////////////////////////////////////////////////
    // /
    // /
    ///////////////////////////////////////////////////////////////////////////////////////////////[/code]
  • I'm going to guess that your loop is checking ranges which are outside the bounds of the Excel file. I see that the loop only terminates once it finds six null values in a row. I would step through the loop and check the i and p values on each loop iteration along with the data in each cell to determine if it is in fact null.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories