PDA

View Full Version : Solved: Automating a Macro



Jarlisle
03-15-2010, 01:12 PM
I would like to be able to automate a macro to run daily during the week using Task Scheduler or some other method. I don't really want to put the macro in the open event if I don't have to because the macro I've built will close the file when done, so if it's in the open event it will open, run the macro then close. This could make it difficult for revision.

Any ideas on how to set this up?

Bob Phillips
03-15-2010, 02:37 PM
Try writing it as a VBScript file, and rune it from the windows scheduler.

Jarlisle
03-15-2010, 02:52 PM
Sorry, I'm not really familiar with a VBScript file, how is that done?

Bob Phillips
03-15-2010, 04:07 PM
It is very similar to VBA, except all variables have no type (Dim LastRow, rng) and you have to use late binding if you have automation (which you are bound to have because you have to connect to or create an Excel instance).

Jarlisle
03-15-2010, 04:14 PM
I'm very sorry, but I still don't know what VBScript is. Do I need another application for that or do I create a text file with the code in it and change it to a different extension similar to a bat file? I'm still very new to VB and macros so as much explanation as possible would be great.

Bob Phillips
03-15-2010, 04:47 PM
You should have everything you need now. Just create a file say called Test.vbs with just this line of code

MsgBox "hello"

and save it somewhere, then double click the file and if you get a MsgBox you have everything that you need. It is just VB/VBA in a script file.

GTO
03-15-2010, 09:18 PM
Just to add to what Bob has already provided, presuming you have 2000 or newer...

Open Excel and from the vb toolbar, find the script editor button and open MDE. Open Help in MDE and from the contents tab find the vbscript language reference. You can see what all is included and what is not.

Bob Phillips
03-16-2010, 01:36 AM
Mark,

I have to admit I do not know what you are referring to. Can you elaborate, I would like to see this?

GTO
03-16-2010, 02:07 AM
Good Morning :-)

At work now so cannot check 2000. Not exactly sure if I typo'd the 'D' in MDE or if 'Development' is in the caption someplace in 2000.

Anyways, I meant that from viewing the sheet, if one clicks the Microsoft Script Editor button :blush, once in script editor, the help file there has info/topics on vbscript and jscript. Specific for the OPs getting started, I found the info on what's not included in vbs to be helpful (in 2003, under contents tab|VBScript|VBScript Language Reference|Feature Information).

Mark

Bob Phillips
03-16-2010, 02:19 AM
Morning to you.

It is that Microsoft Script Editor button that is what I am unaware of. Where is it?

GTO
03-16-2010, 02:32 AM
I would say it looks like a colorful lazy-eight. For me, its at the right end of the vb toolbar.

GTO
03-16-2010, 02:34 AM
Here; I hope this shows up correctly...

edit: ACK! Try again...

Bob Phillips
03-16-2010, 04:55 AM
I've never used that, n fact it wasn't even installed. Interesting!

Unfortunately, it is not a developer tool supported with Excel 2007.

GTO
03-16-2010, 08:08 AM
I've never used that, ...

Clunk, Thunk, Crash! Er, sorry 'bout that, had to pick myself up off the floor and set the chair upright. Still a little dizzy/woozy, but ventricular fibrillation seems to have subsided and pulse rate seems okay.:stars: (Hee-Hee)

Of course web stuff is a bit beyond me so I've never used it except to fool around trying to learn enough to maybe set up a few local pages for say, instructions/help over a particular topic. Extremely rudimentary attempts, but for me, I did find the extra helpfile a bonus that's helped me with a simple script or two.

Bob Phillips
03-16-2010, 09:33 AM
I don't doi web stuff much either, over-rated IMO.

But VBScript is just a language that can be used in ASP, it can also stand alone, which is what I was suggesting.

Jarlisle
03-19-2010, 11:30 AM
I figured it out. Thanks for the ideas from all who helped on this, but I got the actual code from another site.

I put the code below into a text file and then renamed the extension to .vbs and then I used windows scheduler to have it run daily.

Dim objExcel
Dim objWorkBook
Set objExcel = CreateObject("EXCEL.APPLICATION")
Set objWorkBook = objExcel.Workbooks.Open(" Name and Path of workbook ")
On Error Resume Next
objExcel.application.visible = False
objExcel.DisplayAlerts=False
objexcel.run(" name of macro ")
objExcel.DisplayAlerts=True
objExcel.workbook.close
objExcel.Quit
Set objWorkBook = Nothing
Set objExcel = Nothing

Bob Phillips
03-19-2010, 05:09 PM
I put the code below into a text file and then renamed the extension to .vbs and then I used windows scheduler to have it run daily.

Which is what I suggested right at the start.