C# Excel Workbook Open

[nilu_2005]

Hello,

I want to open excel work at form load. I define excel application as global then define excel workbook at form load. But When I access this excelWorkbook in button1_click event for define sheets , I am not able to do this. You can see my code below. I want to open workbook at form load then use button1_click event to activate sheet in button1_click event. I want to open workbook just once.

I am getting this error:
The name 'excelWorkbook' does not exist in the current context

here is my code :

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;
using System.IO;

namespace ExcelApplicationOpen
{


public partial class Form1 : Form
{


ApplicationClass excelApplication = new ApplicationClass();


public Form1()
{
InitializeComponent();
string exlpath = "C:\\Users\\Rahul\\Documents\\Desktop\\C#.xlsm";
Workbook excelWorkbook = excelApplication.Workbooks.Open(exlpath, 3, false, 5, "", ""
, true, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
}



public void button1_Click(object sender, EventArgs e)
{
timer1.Enabled = true;

Final: while (timer1.Enabled == true)
{


excelApplication.Visible = true;






Sheets excelSheet = excelWorkbook.Worksheets;
string currentSheet = "Sheet1";
Worksheet excelWorksheet = (Worksheet)excelSheet.get_Item(currentSheet);

FileStream file1 = File.OpenRead(@"C:\Documents and Settings\HP\My Documents\iMacros\Downloads\_+TOP50FUTSTK1");
StreamReader fileread = new StreamReader(file1);
FileStream file2 = File.Open(@"C:\Documents and Settings\HP\My Documents\iMacros\Downloads\TOP50FUTSTK.txt", FileMode.Open);
StreamWriter filewrite = new StreamWriter(file2);


for (int i = 0; i < 38; i++)
{
fileread.ReadLine();
}

for (int j = 0; j < 50; j++)
{

string ReadString1 = fileread.ReadLine();
string ReadString2 = fileread.ReadLine();
string ReadString3 = fileread.ReadLine();
string ReadString4 = fileread.ReadLine();
filewrite.WriteLine(ReadString1 + "\t" + ReadString2
+ "\t" + ReadString3 + "\t" + ReadString4);
}
file2.Flush();
file2.Close();





goto Final;

}

}



private void timer1_Tick(object sender, EventArgs e)
{


}
}






////public static DateTime PauseForMilliSeconds(int MilliSecondsToPauseFor)
////{


//// System.DateTime ThisMoment = System.DateTime.Now;
//// System.TimeSpan duration = new System.TimeSpan(0, 0, 0, 0, MilliSecondsToPauseFor);
//// System.DateTime AfterWards = ThisMoment.Add(duration);


//// while (AfterWards >= ThisMoment)
//// {
//// System.Windows.Forms.Application.DoEvents();
//// ThisMoment = System.DateTime.Now;
//// }


//// return System.DateTime.Now;
////}




}

[mariocatch]

First, use [ code][ /code] tags please.

Second, you're declaring the 'excelWorkbook' variable inside of a method. Therefore, it has no scope outside of the method.

In your Constructor:

Workbook excelWorkbook = excelApplication.Workbooks.Open(exlpath, 3, false, 5, "", ""
, true, XlPlatform.xlWindows, "", true, false, 0, true, false, false);

In your button1_Click:

Sheets excelSheet = excelWorkbook.Worksheets; // Fails since excelWorkbook is not defined in the scope of this method.

You should make excelWorkbook a class member instead.

[nilu_2005]

Thanks mariocatch for reply.


I have declare that in same button1_click. But everytime I click button1 extra excel workopen due to excelApplication.Workbooks.Open . My goal is to open workbook just once then run macro from timer sections . Because timer will eventually call excelApplication.Workbooks.Open at predefined interval set by me.
I just want run remaining part int say timer event after


sheets excelSheet = excelWorkbook.Worksheets;


Can you please suggest how do I access opened workbook for worksheet defination ?

[mariocatch]

It sounds like you didn't do as I suggested. You only want the workbook to open once, so don't move the workbook open to your button_Click event. Keep it in your forms constructor. But, move the variable declaration outside of the constructor as a class member.

ie:

    public class Foo
    {
        public Foo()
        {
            // Only open workbook once!
            mWorkbook = new object();
            mWorkbook.Open();
        }

        // Declare Workbook as class member.
        private object mWorkbook;

        private void button1_Click()
        {
            Sheets excelSheets = mWorkbook.Sheets;
        }
    }

[nilu_2005]

Ok, I will try your code. Let you know results how this gone.

[nilu_2005]

Hello mariocatch, thanks for your code help. It works perfect ! I moved applicationclass, workbook & sheet class out side of constructor as class member. Now I am able to open excelworkbook only once.