programatically accessing Macros in worksheets

[Patrick]

We have a vendor that has supplied an excel spreadsheet. Information
entered on the spreadsheet is uploaded to web application. We want to
automate some of these features.

I have a .net application that will open the workbook. Within the work
book there are 5 worksheets with macros and constants within them. I
can access the macros at a workbook level with the following example:

My question is.....can you access the worksheet macros and constants
from a .net application? I have tried several variations of the
xlapp.run. Being able to access variables and the procedures within
the functions would be a big benefit. Thanks for your help!

Sub Main()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim szResult As String
Set xlApp = New Excel.Application
Set xlBook = xlApp.Workbooks.Open("E:\Book1.xls")
szResult = xlApp.Run("Book1.xls!MyFunc", 10, "Some String")
MsgBox szResult
xlBook.Close False
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
End Sub

 

[Jim Rech]

This syntax works for running sheet code from another workbook in Excel.
Perhaps you can adapt it to your situation.

Run "book1.xls!Sheet1.Sheet1Macro"

--
Jim Rech
Excel MVP
| We have a vendor that has supplied an excel spreadsheet. Information
| entered on the spreadsheet is uploaded to web application. We want to
| automate some of these features.
|
| I have a .net application that will open the workbook. Within the work
| book there are 5 worksheets with macros and constants within them. I
| can access the macros at a workbook level with the following example:
|
| My question is.....can you access the worksheet macros and constants
| from a .net application? I have tried several variations of the
| xlapp.run. Being able to access variables and the procedures within
| the functions would be a big benefit. Thanks for your help!
|
| Sub Main()
| Dim xlApp As Excel.Application
| Dim xlBook As Excel.Workbook
| Dim szResult As String
| Set xlApp = New Excel.Application
| Set xlBook = xlApp.Workbooks.Open("E:\Book1.xls")
| szResult = xlApp.Run("Book1.xls!MyFunc", 10, "Some String")
| MsgBox szResult
| xlBook.Close False
| Set xlBook = Nothing
| xlApp.Quit
| Set xlApp = Nothing
| End Sub