Solved

cannot call macro rtn in Excel within .NET application

Posted on 2011-09-22
3
262 Views
Last Modified: 2013-12-17
I am trying to execute a macro using the opened workbook object in the code
but it cannot find the macro's function.  The macro itself is an add-in.  I suspect when I open the workbook (in code), all the initialization steps have not been
taken since I do not see the excel menubar option with the macro functions listed.  Oddly, when I open an excel instance on my desktop, it does initialize properly and
I do see the menubar pulldown in excel.
 
 
 My code looks like this:
 
app = new Microsoft.Office.Interop.Excel.Application();

app.Visible = true;

try

{

wbk = app.Workbooks.Open(uploadfilewbk, 3, false, 5, "", "", true, XlPlatform.xlWindows, "", true, false, 0, true, false, false);



Worksheet wks = (Worksheet)wbk.Sheets["PositionLink"];

int recindx = 8;

// write new positions form riskpos struct

foreach (string thekey in riskpos.Keys)

{

Dictionary<string, string> therec = riskpos[thekey];

wks.Cells[recindx,1] = therec["Portfolio"];

++recindx;

}


wbk.Save();

}

catch (Exception ee)

{

System.Console.WriteLine("Main Error " + ee.Message);

}

// Trigger Macro

try

{

app.Run("EMPositionLink_AddEquitiesTransactions",

Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,

Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

}

catch (Exception eee)

{

System.Console.WriteLine("ERROR: {0}", eee.Message);

}

 Also, I was able to set a break right after the Open call and was able to trigger the initialization after pushing one of the buttons that calls this macro.  I was then able to see it in the visual basic editor within the .xla module it was contained in

0
Comment
Question by:cchu1
  • 2
3 Comments
 

Author Comment

by:cchu1
ID: 36582532
Please help me with the problem above.  Thanks.
0
 
LVL 14

Accepted Solution

by:
Daniel Junges earned 250 total points
ID: 36586678
other ways to call a macro:

app.RunOld("EMPositionLink_AddEquitiesTransactions");

app.GetType().InvokeMember("Run"
  , System.Reflection.BindingFlags.Default |
    System.Reflection.BindingFlags.InvokeMethod
  , null
  , app
  , "EMPositionLink_AddEquitiesTransactions"
);
0
 

Author Comment

by:cchu1
ID: 36587147
Thanks, I found my problem.  Needed to state the full path of the routine..  but I can use your suggestion in the future.

app.Run("'C:\\Program Files....\\<file>.xla!EMPos..._AddEquitiesTransactions",Missing.Value,Missing.Value, ...);
0

Featured Post

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question