6 Replies - 2349 Views - Last Post: 26 May 2010 - 08:00 AM Rate Topic: -----

#1 gusgus   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 24-May 10

Writing data to excel via a start button and timer_tick event

Posted 25 May 2010 - 09:52 AM

Using a Windows Form written in C#, I would like to create a new Excel file upon clicking the Go button. This button also starts a timer. Each tick event for the timer should first write the values of data found in Label. Text to a cell, and then offset the range of that cell so the range is advanced for the next timer tick event. Below is the code I have. The error message I get is that the workbook instance doesn't exist. I know I'm close, I actually had it working then I changed a bunch of stuff and now it doesn't :P Any help would be greatly appreciated!

using System.Reflection;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;


namespace excel_data_recording_test_code
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
            timer1.Interval = 5000;

          
        }


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

            tickcounter.Text = "0"; //just used for debugging, shows how many timer ticks have successfully occurred

            
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); //create new excel instance

            if (xlApp == null)
            {
                MessageBox.Show("EXCEL could not be started. Check that your office installation and project references are valid.");
            }
            xlApp.Visible = true; //show excel instance

            Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); //create workbook object
            Worksheet ws = (Worksheet)wb.Worksheets[1]; //create worksheet object

            if (ws == null) //check to see that worksheet object exists
            {
                MessageBox.Show("Excel worksheet could not be created. Check that your office installation and project references are valid.");
            }
        }

        public void timer1_Tick(object sender, EventArgs e)
        {

            //get reference to Excel.Application from the ROT
            Excel.Application oxlApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

            //Excel and other Office applications register themselves in 
            //ROT (background runtimes?) when their top-level window loses focus. Having a MessageBox 
            //forces Excel to lose focus and then register itself in the ROT:

            MessageBox.Show(oxlApp.ActiveWorkbook.Name); //display the name of the workbook object
            //Marshal.GetActiveObject("Excel.Application");
            //MessageBox.Show(oxlApp.ActiveCell.Value2.ToString());

            if (oxlApp == null)
            {
                MessageBox.Show("There was no running Excel session, or the active excel session was not successfully grabbed. Check that your office installation and project references are valid.");
            }

            Excel.Workbook owb = (Excel.Workbook)System.Runtime.InteropServices.Marshal.BindToMoniker(textBox2.Text);
            Worksheet ows = (Excel.Worksheet)owb.ActiveSheet;

            if (ows == null)
            {
                MessageBox.Show("Excel worksheet could not be created. Check that your office installation and project references are valid.");
            }


            //begin writing cell values:

            //write tempval
            Range tempRange = ows.get_Range(TempRangeLabel.Text, Missing.Value); //get tempRange from TempRange label

            if (tempRange == null)
            {
                MessageBox.Show("Could not get a range. Check to be sure you have Office 2007 or later installed.");
            }

            Object[] tempargs = new Object[1]; //create argument object
            tempargs[0] = TempLabel.Text; //set argument
            tempRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, tempRange, tempargs); //write argument to cell
            //aRange.Value2 = TempLabel.Text; //change the cell value to TempLabel.Text

            //write voltval
            Range voltRange = ows.get_Range(VoltRangeLabel.Text, Missing.Value);

            if (voltRange == null)
            {
                MessageBox.Show("Could not get a range. Check to be sure you have Office 2007 or later installed.");
            }

            Object[] voltargs = new Object[1];
            voltargs[0] = VoltLabel.Text;
            voltRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, voltRange, voltargs); 

            
            //write currentval
            Range currentRange = ows.get_Range(CurrentRangeLabel.Text, Missing.Value);

            if (currentRange == null)
            {
                MessageBox.Show("Could not get a range. Check to be sure you have Office 2007 or later installed.");
            }

            Object[] currentargs = new Object[1];
            currentargs[0] = CurrentLabel.Text;
            currentRange.GetType().InvokeMember("Value", BindingFlags.SetProperty, null, currentRange, currentargs);



            //now to increment the columns by one for each range for the next timer tick:

            tempRange.get_Offset(1,0); //of the form get_Offset(x,y), advances column of temp range by 1
            TempRangeLabel.Text = Convert.ToString(tempRange);



            voltRange.get_Offset(1, 0); //of the form get_Offset(x,y), advances column of volt range by 1
            VoltRangeLabel.Text = Convert.ToString(voltRange);



            currentRange.get_Offset(1, 0); //of the form get_Offset(x,y), advances column of current range by 1
            CurrentRangeLabel.Text = Convert.ToString(currentRange);
        }

    }
}




Is This A Good Question/Topic? 0
  • +

Replies To: Writing data to excel via a start button and timer_tick event

#2 FlashM   User is offline

  • D.I.C Lover
  • member icon

Reputation: 383
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Writing data to excel via a start button and timer_tick event

Posted 25 May 2010 - 10:02 AM

I didn't take time to go completely through your code, but here's the way I'd do it:

1. You can write to excel file using the OleDb driver, which means your excel file acts just like it would be a database table and you can insert your values using the SQL statements.

2. Instead of writing directly to excel file, you can write your values into text file using the StreamWriter. Be sure to use one of the delimiters for example comma, tab, semi column or some other (I suggest using the semi column).
Then you can simply import this text file into Excel.

This way, you don't need to use the COM objects for generating your excel file.

Hope this helps...
Was This Post Helpful? 0
  • +
  • -

#3 gusgus   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 24-May 10

Re: Writing data to excel via a start button and timer_tick event

Posted 25 May 2010 - 11:29 AM

Thanks for the reply. Unfortunately I'm a chemist with an engineering background. This is my first C# application, and it has taken me over 10 hours just to produce the above code because I'm that unfamiliar with what I'm doing. I'm unfamiliar with SQL statements. I have to write the data to Excel rather than a txt file because of manipulations that will occur later in the program, and I would rather write to the excel file and save it with each write in order to minimize data loss that may occur due to the application or method being ended prematurely (I know this will happen from time to time in the production environment). I'd really rather just move forward with the code I have rather than rewrite things from scratch using another technique. I know this is close, surely someone can recognize what's going wrong here.

This post has been edited by gusgus: 25 May 2010 - 11:33 AM

Was This Post Helpful? 0
  • +
  • -

#4 gusgus   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 24-May 10

Re: Writing data to excel via a start button and timer_tick event

Posted 25 May 2010 - 12:05 PM

I know that the first line of the stuff below doesn't actually do what I want, and I think that's where the problem is. Unfortunately I haven't been able to find how to select the visible workbook that was opened in the gobutton_click event. I think if I replace that line with something that selects the active workbook that's already open then it should work...

   Excel.Workbook owb = (Excel.Workbook)System.Runtime.InteropServices.Marshal.BindToMoniker(textBox2.Text);
   Worksheet ows = (Excel.Worksheet)owb.ActiveSheet;

   if (ows == null)
   {
    MessageBox.Show("Excel worksheet could not be created. Check that your office installation and project references are valid.");
   }


Was This Post Helpful? 0
  • +
  • -

#5 gusgus   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 24-May 10

Re: Writing data to excel via a start button and timer_tick event

Posted 26 May 2010 - 06:47 AM

No one is able to help? (bump)
Was This Post Helpful? 0
  • +
  • -

#6 FlashM   User is offline

  • D.I.C Lover
  • member icon

Reputation: 383
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: Writing data to excel via a start button and timer_tick event

Posted 26 May 2010 - 07:02 AM

I'm really sorry, but really busy for the past few days... Maybe later or tomorrow...

This post has been edited by FlashM: 26 May 2010 - 07:02 AM

Was This Post Helpful? 0
  • +
  • -

#7 BigR1983   User is offline

  • D.I.C Head

Reputation: 57
  • View blog
  • Posts: 226
  • Joined: 12-April 10

Re: Writing data to excel via a start button and timer_tick event

Posted 26 May 2010 - 08:00 AM

are you getting an error message or is your message box coming up?

If it is an error message, can you post what is being said in the error message? (info on what line it is crashing on)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1