Question Excel automation run macro

grao

New member
Joined
Jan 13, 2014
Messages
4
Programming Experience
Beginner
Hi

If i make mistakes in posting this I apologize in advance. I am new to this forum.

Before I begin

I am a beginner in C# I went through some web videos in two days and wrote this script (below).

Objective:

The Script has to monitor a particular folder, if the folder contains a file then it has to invoke a excel instance and run the macro located in the file. Wait for the Macro to complete, once complete close the excel instance, purge the values and then again resume to monitor the folder. I have given a time interval of 30 seconds

Problem Statement:

When I run it in the local machine it works perfectly, the script waits for the macro to complete and then purges the excel instance and then resumes the timer. But when I run it on the server (Windows Server 2008 R2 Enterprise). It does not wait for the macro to complete but just jumps to the next line and closes the excel, thus not giving time for the macro to execute. Since the server does not have a Visual Studio 2012 I cannot check why is this jumping the step.

The Script:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Microsoft.Office.Interop.Excel;


namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        int i = 0;
       
        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook xlBook;
        

        private void timer1_Tick(object sender, EventArgs e)
        {
            i++;
            lblu.Text = i.ToString() + " Seconds";


            if (i == 10)
            {
                System.IO.DirectoryInfo dir = new System.IO.DirectoryInfo(@"Q:\qbrDone");
                int lpc = dir.GetFiles().Length;
                timer1.Enabled = false;
                timer1.Stop();



                if (lpc > 0)
                {
                    RunMacro(xlApp);

                }
                else
                {

                    lblu.Text = "";
                    i = 0;
                    timer1.Start();
                    timer1.Enabled = true;
                }
            }
        }

         private void RunMacro(object oApp)
        {
            timer1.Stop();

        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Open(@"C:\Users\dp\Documents\Eternal Loop.xlsb");
        Microsoft.Office.Interop.Excel.Worksheet xlsht;
        xlApp.Visible = true;
        xlBook.Activate();
        xlsht = (Microsoft.Office.Interop.Excel.Worksheet)xlBook.Worksheets.get_Item(1);
        xlsht.Activate();
        RunMacro1(xlApp, new Object[] { "starter" });
        xlBook.Close(false);
        releaseObject(xlBook);
        xlApp.Quit();
        releaseObject(xlApp);
                
        }

         private void RunMacro1(object oApp, object[] oRunArgs)
         {
             System.Windows.Forms.Application.DoEvents();
           oApp.GetType().InvokeMember("Run", System.Reflection.BindingFlags.Default | System.Reflection.BindingFlags.InvokeMethod, null, oApp, oRunArgs);
           
         }      
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
                
            }
            catch (Exception ex)
            {
                obj = null;
                
            }
            finally
            {
                GC.Collect();
                lblu.Text = "";
                i = 0;
                timer1.Start();
                timer1.Enabled = true;
            }
        }
    }

}


Script Written: Visual Studio 2012 Professional

This is my first script in C# and I am not able to figure out where I am going wrong. It would be great if anybody could help me out.

Thanks!
Grao
 
Last edited by a moderator:
Top Bottom