select file and run macros on it?

[supper's ready]

hi,

I have a macros which i run on a workbook with data in 1 sheet. the macros creates 2 new sheets in that workbook.

what i want to do:
create a workbook soley for the purpose of performing this macros on other workbooks.

So it will be like:

open macros workbook
click button
select excel file
vba code proceeds to perform macros on select excel file and then saves the result as existing file.

Any ideas?

 

[Ruddles]

Perhaps this will get you started: it opens a dialog box which allows you to select an Excel workbook, then it opens it and assigns an object called wkbk to point to it and another called wsh to point to the first worksheet in wkbk (as an example).

[FONT=Fixedsys]Option Explicit[/FONT]
 
[FONT=Fixedsys]Public Sub DoStuff()[/FONT]
 
[FONT=Fixedsys] Dim sFilename As String[/FONT]
[FONT=Fixedsys] Dim wkbk As Workbook[/FONT]
[FONT=Fixedsys] Dim wsh As Worksheet[/FONT]
 
[FONT=Fixedsys] sFilename = Application.GetOpenFilename(FileFilter:="Excel workbooks (*.xl*), *.xl*")[/FONT]
[FONT=Fixedsys] If sFilename = "False" Then Exit Sub[/FONT]
 
[FONT=Fixedsys] Application.EnableEvents = False[/FONT]
[FONT=Fixedsys] Set wkbk = Workbooks.Open(sFileName)[/FONT]
[FONT=Fixedsys] Application.EnableEvents = True[/FONT]
[FONT=Fixedsys] Set wsh = wkbk.Worksheets(1)[/FONT]
 
[FONT=Fixedsys][COLOR=green] ' do stuff to workbook including calling macros and/or saving and/or closing it as required[/COLOR][/FONT]
 
[FONT=Fixedsys]End Sub[/FONT]

Where I've inserted a comment, you would write code to do whatever you wanted to do to the workbook in question, not forgetting to do a wkbk.Save and a wkbk.Close (or just a wkbk.Close SaveChanges:=True) to save your changes.