Can you save an external macro?

  • Thread starter Thread starter genEus
  • Start date Start date
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
 
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.
 
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"
 
Back
Top