The Problem

I'm trying to export a file with a variable name by using the user defined variables in options. When I try to define my variable as select to_char(sysdate,'mmm yyyy') from dual it puts that statement in the file name rather than the result of the statement. how do I format this in the variable so that it recognizes it as a formula rather than text?

Even thought Toad has made some nice features in variables manipulation, what user wanted, define variable through Sql*Plus commands, is not so clear and obvious how to do it. Reason is that Toad has nowhere exposed application interface for defining such an action. And this was a reason, why no one answered to poster...

The Solution

Because we cannot stay only inside Toad, solution is done in two parts:

Automation Designer

Scripting part on OS file level

Idea is to create one .bat file which will initialize environment variable and then call Automation designer, through it's command line interface.

Automation Designer

In File definition place resulting destination file, which was the main problem, because it must have value derived from sql command. so define it like on the picture

In this way you are telling Toad to use global environment variable which will hold dynamic value (in our case "mm_yyyy" of some date value) for export file name.
After App definition

Right click on chosen App (Do_Export) and use Create Parameter file option.

Save file it in same directory as this file should be create it's export (for easy control). I will name it define_date_value.sql, which in fact looks very simple:

set head off
set timi off
set time off
set ver off
SET ECHO OFF;
set feedback off;
SET SERVEROUTPUT ON SIZE unlimited format wrapped;
SET PAGES 2000;
SET LINESIZE 1000;
SET PAGESIZE 9999;
SET TRIMSPOOL ON;
select to_char(sysdate,'mm_yyyy') from dual;
exit;

Create windows bat file call_export.bat, which will be starter of action with content:

As you see to_char (sysdate,''mm_yyyy') from sql is used as file name what was the main problem at the beginning.
In your case change sql from that file to something what is useful in you case...sql or even PL/SQL.
Once again, the whole solution is run by execution windows bat file call_export.bat.

The Solution2

If you wan to stay fully inside Toad, and run only from Automation designer, then you need to create another App, "DO_Call_Export" which consist of just one action-"Shell Execute". This would be a wrapper which call directly from Toad windows bat file call_export.bat.

With that approach you are fully inside Toad execution.

The End

What is important in this solution is to check that no user variables (in mine case "XXX") are not defined inside Toad.

If XXX variable is defined inside Toad, then this was not working...what I find mre as a small Toad bug (tested inside 12.10 x64) ... but we have to live with it.
Hope this helps someone.