In an answer to a prevoius question 'Bean' sugested that Scheduled Tasks would be useful for running a macro at a certain time on a certain day. I have just had it activated on my computer but can only add a task where it opens an application. Can anyone let me know how I run a marco?
Thanks

For "Word" you would pass in /mmacroname. I guess excel would be the same too.


/m by itself disables any startup macro and both disable the autoexec macro.



Can't find how to do it in excel other than calling it Auto_Activate. And that doesn't appear to work anyway.

[Edited by steve_w on 23-06-2003 at 03:05 PM GMT]

i did ".....file.xls/mDoMyMacro"
and it didn't work.
Assuming I can get this to work it is going to lead to one more question. On opening a file with a macro it is going to ask me if I want to allow the macros to work, but since the whole point is that I am not going to be there how do I get the task to run without requesting any user input?

I'll keep looking

You could of course write a vb app to do it.

Showing my ignorance here, but by vb app do you mean a different way of writing/saving of the code - if so could you spread a little light on how I would go about this?

Just found a hint which says call the maco Auto_Open and that will then run on opening, still not found a way of disabling the macro question though.

We'll come back to th vb one later if we're stuck ok as it will take a bit longer.

Just thought of an easy way around it.

When you open up an excel file in vb it does not ask the macro question.

Then we create a vb app which you pass in the excel file name and it opens it.

The function would be some thing like

private sub main dim xlapp as excel.application set xlapp = new excel.application xlapp.workbooks.open command ' where command is the passed in excel file xlapp.run "MacroName" xlapp.visible = true end sub

What do you think ?



[Edited by steve_w on 23-06-2003 at 04:07 PM GMT]

FYI...your other topic...
http://www.andreavb.com/forum/viewtopic.php?TopicID=1499

It was my understanding, and I think you would want to, initiate the EXE query app you have from the Scheduled Task, not the Excel file.

Also, just FYI, you can disable Excel macros by either disabling macro security in Excel on the users computer, or I think you can hold "shift" at file open...neither is a really an option for a developer.


And oh yeah, do you have access to VB?

[Edited by ~Bean~ on 23-06-2003 at 01:48 PM GMT]

____________________________
Eggheads unite! You have nothing to lose but your yolks.

Thinking a little more about it. We could create an exe which you pass parameter 1 as the excel file and parameter 2 could be the macro to run. Then you will only need the one exe as your scheduling exe. Taking Beans point about you having vb, if you don't let me know and I will create it for you. Otherwise I can just help you with the code.

Cheers Steve

VB - isn't that what I am writing my code in, when I am creating a macro?

VBA = VB ... not exaclty...

You are using the VBA (a.k.a. visual basic for applications) that M$ developed for its M$ Office suite of software - specifically, you are using Excel VBA. This used to be a simple "macro language" (hence, this is why we still call Excel code "macros") not any kind of BASIC, but M$ did a splendid job of integrating the easy to use language (BASIC) into its apps. However, each Office app's VBA is a little different - if you wrote a macro in Excel, it may not work in Word or Access, or even VB (mostly because each app has different objects, but some general syntax differs as well). VB (a.k.a. visual basic) is the stand alone IDE/Compiler for the BASIC programming language - this will actually let you create your own programs (.exe's) as opposed to just adding programming functionality to, say, your Excel file. Mostly we (here) use the M$ product here as well...VB 5/6 (or VB.NET).

Perhaps you could outline your project a little more clearly for us...this is what I gather you have:
-An Excel file that receives updated data from a data source; macros are currently saved here that initiate the query EXE.
-An executable (.EXE) that runs a Query and puts the data into the Excel file.
-A data source (database).

You want to schedule the query executable to run at a specific time to execute and retrieve data and save to the Excel file...

is this correct?  

What we were suggesting currently is that you have a program (written in VB) that opens the Excel file and tells it to run the macro that calls the query executable. This VB program is what you would have scheduled as a task. It is unfortunate that the query executable (called brioquery?) does not do this in addition to performing the query...who wrote that .exe?

____________________________
Eggheads unite! You have nothing to lose but your yolks.

Yeah all of of what you said about my project is correct. If you remember from before I had the problem with making shell wait to finish the 'brioquery' before running the excel part. Just for info this worked:
Run Brio Macro
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now())
waitTime = TimeSerial(newHour, newMinute, newSecond) + 30
Application.Wait waitTime
Run Excel Macro

But the problem is making this whole thing automaticly activate every workday before I even have to consider waking up to my alarm. I now understand the difference between VB and VBA. However where do I create and store things in VB, and how do I know if I have it. And I guess lastly but most importantly what would I write.

Thanks guys for all your help so far!

You need a VB compiler to create things in VB...if you don't know if you have it, then chances are you don't have it...i think the Pro version (VB 6) retails for $200 (US).

I think you have some options though...

If this is for your work, then you could have someone in your tech department write you a small program to do what you're asking (doesn't have to be written in VB), or buy VB on the company if you can justify the cost, or you could hire a programmer to write the program, or you could find one nice enough to do it for free. A simple no frills version of what you require would be a very easy thing to write...

not to mention that if you know the connection method to your database, you can easily re-write your query in an Excel sub and call it on demand! thus not haveing to use "brioquery.exe" or Scheduled Tasks or any of that jazz...(look in Excel VBA help under ADO)

____________________________
Eggheads unite! You have nothing to lose but your yolks.

somthing else that could work that I hadn't tried is a Scheduled Task set up with a "RUN" and a "START IN" properties. (similar to configuring shortcuts in Windows) Set its RUN to your excel file and its START IN to the Excel.Exe file. Dunno if this would work for you

____________________________
Eggheads unite! You have nothing to lose but your yolks.