0

All,

Apologies if this is a very basic question and has been asked before, I predominately write in VBA / JAVA. However a project I am working on requires a C# script. Which carries out 3 simple steps:

  1. Target a excel workbook which is already open. File path:

    \Csdatg04\psproject\Robot\Peoplesoft To LV\Master Files - Do not use\Transactions into LV Template.xlsm

  2. Populate cells A1,A2 & A3 with three variables already retrieved earlier in the automation.

  3. Run a macro stored within the filepath mentioned above Macro name "ControlMacroACT"

The code I have developed is below, however in each stage identified above I am encountering errors (Probably basic errors).

Error 1: This line of code is to open a workbook I would like this to target an already active workbook.

Error 2: Worksheet not found

public void RunActualsMacro(string Filepath, string Period, String FiscalYear)
    {
        //~~> Define your Excel Objects
        Excel.Application xlApp = new Excel.Application();

        Excel.Workbook xlWorkBook;

        //~~> Start Excel and open the workbook.
        //Error 1
        xlWorkBook = xlApp.Workbooks.Open("\\Csdatg04\\psproject\\Robot\\Peoplesoft To LV\\Master Files - Do not use\\Transactions into LV Template.xlsm");

        // Populat Cells A1,A2,A3 with string variables
        // Error 2 Worksheet not found
        worksheet.Rows.Cells[1, 1] = Filepath;
        worksheet.Rows.Cells[2, 1] = Period;
        worksheet.Rows.Cells[3, 1] = FiscalYear;


        //~~> Run the macro ControlMacroAct
        xlApp.Run("ControlMacroACT");

        //~~> Clean-up: Close the workbook
        xlWorkBook.Close(false);

        //~~> Quit the Excel Application
        xlApp.Quit();

    }

Any help would be much appreciated.

  • must we guess what the errors are, or would you like to speed things up by telling us precisely what's happening, since you already know? – ADyson Feb 5 at 13:54
  • @ADyson I have edited the question to include what the errors are, apologies. – SB999 Feb 5 at 14:26
  • @SB999 - I think you already identified the issues; 1) close the darn workbook; 2) identify the worksheet you are using. Fix that and then update the question. – Sam Feb 5 at 15:59
1

You need to use Marshal.GetActiveObject, and this code is roughly right, but cannot test right now.

public void RunActualsMacro(string Filepath, string Period, String FiscalYear)
{
    //~~> Define your Excel Objects
    Excel.Application xlApp = null;

    Excel.Workbook xlWorkBook;

    //~~> Start Excel and open the workbook.
    //handle errors below
    try {
        xlApp = (Excel.Application) System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
    } catch {
        //perhaps exit - or throw??
    }

    xlWorkBook = xlApp.Workbooks["Transactions into LV Template.xlsm"];

    // Populat Cells A1,A2,A3 with string variables
    Excel.Worksheet ws = xlWorkBook.Worksheets["Sheet1"] //what the tab name of sheet
    ws.Cells[1, 1] = Filepath;
    ws.Cells[2, 1] = Period;
    ws.Cells[3, 1] = FiscalYear;


    //~~> Run the macro ControlMacroAct
    xlApp.Run("ControlMacroACT");

    //~~> Clean-up: Close the workbook
    xlWorkBook.Close(false);

    //~~> Quit the Excel Application
    xlApp.Quit();

}
  • Thanks works well I just had to change one line - Excel.Worksheet ws = xlWorkBook.Worksheets["Sheet1"] to Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets["Sheet1"]; – SB999 Feb 6 at 9:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.