Can you save an external macro?

G

genEus

Is it possible to save a macro all by itself, so that it acts sort of
like a batch file?

I want to have two things in my working directory, a text file and this
standalone macro (or something to the likes of it). I want to be able
to click once and have the macro (or whatever) import the text file
into Excel, format the file to my liking and save it under the filename
of the original text file with the extension .xls, of course.

Can I do all this with a macro? And, back to the original question, can
I actually have a standalone macro that will let me do this?

Thanks
 
D

Dave Peterson

Macros in excel live in workbooks. So just create a "importmytextfile.xls"
workbook that contains your macro.

Put your code in a macro called "auto_open" in a general module.

Then right before your macro ends, add

thisworkbook.close savechanges:=false

(Make sure you save the macro before running tests--else it won't get saved.)

Then starting the excel file will run your macro and close the workbook when
it's done.
 
A

Andy Wiggins

Is it possible ....? Sort of.

Take a look at VBS. Here's an example. Save it in a standard text file with
a .VBS extention.
This one creates an Excel object, opens a workbook, sets a value, runs a VBA
routine, then tidies up and closes down.

Set xlObj = CreateObject("Excel.application")
xlObj.Workbooks.Open "C:\Byg\textfile\TextFileReformat.xls"
xlObj.Range("NoOfCharsPerLine").Value = 50
xlObj.Run "ReadFromTextFile"
xlObj.ActiveWorkbook.Saved = True
xlObj.ActiveWindow.Close
xlObj.Quit


--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"
 
Top