How do I create an executable application for a Excel macro procedure?

Question

I've got two spreadsheets ("Old" and "New") and a VB Script macro. The macro runs in one ("New"), does a comparison of the contents of the other ("Old"), and then outputs the results back on to the "New" spreadsheet.

I'd like to build an executable where I input the two file names and click a run button to simplify this procedure. The current procedure requires opening both files, then cutting and pasting the script into the VB editor of "New" and running it there. I'm looking for advice as to what programming language or tools I can use to create this application in Windows XP to run against spreadsheets created in Excel 2007.

Can I use C# to write a simple application that can do this?

If you could point me to resources about writing C# applications to interact with Excel that would be greatly appreciated.

This is my first attempt to write an application that interacts with Excel. Maybe this is not possible. If that's the case, please let me know that too.

Answer 1

It is possible.

Here are some good tutorials on the matter:

1. http://www.eggheadcafe.com/articles/create_macro_at_runtime_in_dotnet.asp
2. http://support.microsoft.com/kb/306683

Answer 2

Why not just create an excel File called "Compare.Xls" that will use the following code to allow the user to pick the files and run the Compare on them

Dim fd As FileDialog
Dim sOldFile As String
Dim sNewFile As String

Set fd = Application.FileDialog(msoFileDialogOpen)

  fd.AllowMultiSelect = False

  MsgBox "Please Select the Old File"
  fd.Show
  Workbooks.Open fd.SelectedItems(1)
  sOldFile = ActiveWorkbook.Name

  MsgBox "Please Select the New File"
  fd.Show
  Workbooks.Open fd.SelectedItems(1)
  sNewFile = ActiveWorkbook.Name

It could use some further error checking, like making sure the user didn't select Cancel or press escape on the File Dialog, but it should allow you to create a button on 1 file and run the comparison from that file on two user selected files.

EDIT: I just thought about this too, you could add a call to your macro in ThisWorkbook under Workbook_Open and have it auto run whenever someone opens up Compare.Xls.

Answer 3

I had macro defined in Open office org, where I used a batch file to execute the macro from command line. And batch file was executed using C#. use ProcessStartInfo and Process Class. You may know You can run the batch file in hidden mode and also can get the result back (if any) from out put stream.

Answer 4

Maybe you could consider writing "New" as an excel template, which on instantiation runs a macro which opens "Old", compares and saves to the new document.

You could also consider using Old as a template or writing a new template "compare.xlt" as suggested in another answer.

Answer 5

Can anyone tel how to dynamically generate excel 2007 with macros enabled on it and fill the dropdown in excel with asp.netc#