30 Day free trial interim questions

Silver7 · Post by **Silver7** » Sat Aug 12, 2006 12:46 am

Hello all,
this has been an ongoing search for a solution and I ended up here.

I need to know if I can learn to write a script that will detect if a particular log file has increased in byte size, and when it does it will then Activate a specific macro in Excel ?
The log file is created by a Server at random.

Thanks

Bob Hansen · Post by **Bob Hansen** » Sat Aug 12, 2006 3:11 am

If I correctly understand what you wantto do, then Macro Scheduler can do that for you. That could be probably be done in less than 10 lines of script.

A simple rule of thumb will be, if you can do it using the keyboard and/or the mouse then MAcro Scheduler can do it. It can also do many things that are based on conditions and not requiring kybd/mouse actions.

Good documentation, food tutorials, plenty of examples, great user forum, great tech support, everyone willing to help.

Welcome to the Macro Scheduler family.

pgriffin · Post by **pgriffin** » Sat Aug 12, 2006 3:41 am

To get you started, check out the FileSize> command. I would suggest some sort of looping program which might look like.

Code: Select all

label>BaseLine
filesize>FileName,sizeresult

label>WaitTime
Wait>60 (to wait one minute)
Filesize>FileName,sizecheck
if>sizeresult<sizecheck>DoSomething
else>
   goto>Wait60
endif>

Label>DoSomething

....your code to call a Macro in Excel....probably would be done with VBScript which is embedded in MacroScheduler. OR you could open Excel with MacroScheduler and run the macro the old-fashioned keystroke way.

Let us know if this is clear as mud.

pgriffin · Post by **pgriffin** » Sat Aug 12, 2006 3:53 am

Sorry, no matter what I did I could not get the previous post to post correctly.

If you have questions please post them.

the if statement should be something like;

Code: Select all

if>sizeresult<sizecheck>DoSomething
else>
   goto>WaitTime
endif>

Bob Hansen · Post by **Bob Hansen** » Sat Aug 12, 2006 4:12 am

Thanks Skunkworks for initial sample code

Replace section starting at IF line:

Code: Select all

IF>%sizeresult%<>%sizecheck%,DoExcel
GoTo>WaitTime

Label>DoExcel
Run Program>Path\excel.exe filename.xls

=========================
Create the macro you want Excel to run and save it as an Auto_Open macro.
Now when Macro Scheduler starts Excel. the Auto_Run macro will execute.

Or you can write more complex coding in VBA using the Open event of the workbook to run instead of an Auto_Run macro.

Note, the code above is not tested, just done freeform as example

Silver7 · Post by **Silver7** » Sat Aug 12, 2006 5:42 am

OK,
here we go, I will try to explain.

My excel workbook/macro is fine except for this final phase as to why I am here !

The process "logic" is this:

Server uses Live data feed going to Server's database.
Script in Server ( ironicaly similar to your product) does stuff and the product is the log files.
Excel uses the information from those log files to "do stuff"
Server IS suppose to activate excel, BUT, there are "issues" with the programmer of the Server. He won't teach me how to write the script !
I use excel to workout my formula and then was supposed to be scripted.
I only have limited use of the server and there is mathematics and other test scenarios I need to do in excel so I can one day learn to script the Server.
It seems, from what I have read here, I can eventually replace the Server's current script editor and start fresh using a your script editor WITH support.
But for now, the workaround is to get the macro to activate when the server updates the log file, and the only way I can think of is to "detect" when that log file has increased in byte size.
It is no use doing a timed method of checking at intervals, I have tried this and "murphy's law" says it will check when the server is writing something new in the log file.
If I can get started on this interim project so I can get a hang of exactly what I need to do, then I am sure my current Excel workbook will be a standalone program instead of waiting and getting no where with this programmer who I paid for my script to be scripted, but has not provided help files nor support to take my projects further.

The server I use is Firbird, can your product work with Firebird Server for it's script editor to ?

http://www.firebirdsql.org/

My ultimate goal is this;
to have a script editor to utilize MY potential ideas using this server/database and some 3rd party plug-ins to do the same calculations Excel does, but get a standalone version happening and I then do not have to rely on the programmer's product.

It has been a long 4 years + and I am really stuck.
I am not a programmer, I only can do VBA macro and recordings.
From a recorded macro I tweak it as need be to eliminate mouse kliks.

The only mouse kliks that stands in the way now is to Copy and Paste a new section of the log file to a dummy log file, excel imports and the rest is fully automated. ( this should have been scripted)
So, about 3-4 mouse kliks stand in the way for 100% hands free number cruncher that crunches new live data every 5 minutes or so.

make sense ?

pgriffin · Post by **pgriffin** » Sat Aug 12, 2006 2:41 pm

I checked out the page for Firebirdsql. They claim ANSI standard features, so you should be able to create (or have your friendly programmer create) a connection to your server database (aka DSN via ODBC driver) which would allow you to get data directly from the server to MacroScheduler and either send the data to Excel or just do the calculations you need in MacroScript.

So, look at how you can get an ODBC connection to the server. This site has TONS of examples for this.

You might, for now, try to do a CopyFile> on your log file, then read it into MacroScheduler to try to avoid "collisions" with the server programs. I think there is a way to use VBScript to detect whether or not the log file is being accessed, if you want a more certain method to prevent reading the file while it is being written to.

I know you are not a programmer (in your words) but if you give it a try, the users on this forum will help you along.

Silver7 · Post by **Silver7** » Sat Aug 12, 2006 8:59 pm

Thanks SkunkWorks;

So, look at how you can get an ODBC connection to the server. This site has TONS of examples for this.

Are these examples specific to Firebird Server or generaly to any server ?
What do I look for in this forum as in terminologies for search to read up a bit on doing these OBDC connections ?

You might, for now, try to do a CopyFile> on your log file, then read it into MacroScheduler to try to avoid "collisions" with the server programs.

This is not required (CopyFile), the Import Macro is ready to go.
Although I have tried.

Just a "detector" to detect the file has been created or accessed, then activate Excel "Macro1" and the rest is already in place.

Even if the log file is deleted after Import is ok because the server will create new file of the same file name.
According to Macro Scheduler Demo I guess I can re-create the Server's original log file if need be from Excel.
So, for now, how do I create a "detector" if this is possible and "delete after Import ?

Thanks for the info. so far

BTW
One of the Macro Sceduler examples that does the Excel log file demo. is where I am at with Excel, at that very stage after all my macro processes are complete after Import.
It's seems a similar task the current server does infact !

So imagine that log file in your demo. is the server writing it, that is the file Excel would Import/ Delete after Import/Activate Macro2/Detect for new log file/Macro1 and so on.

The Demo Script that does the email thing, it fits perfectly with the rest of my "wish list" in the bigger picture of things to, if certain parameters are met after Import and the calculations in Excel.
Once those calculations prove correct, then I suppose the "Standalone Program" phase would be ready to start on in future.

My program, would not be for sale. I am not looking to make programs to sell, I just want numbers to crunch and trade my calculated numbers, IF the callculations prove vialble in the first place from Excel; hoping this will clarify why I am at odds with "friendly programmer", it's the "calculations" , let's assume "magic lotto numbers"

Silver7 · Post by **Silver7** » Sun Aug 13, 2006 12:29 am

Thankyou for the welcome to,
Bob Hansen and SkunkWorks.

Going by the current server's scripting language, in structre it all looks similar.
I know it starts with:

sub DefineGlobals

const("xyz",varBoolean,True)
const("xyz",varString,"Demo_WRKSHP")
const("REFERENCE",varString,"Demo")
const("TESTING",varBoolean,True) 'if False, then connects to 3rd party plugin

const("PRINTING",varBoolean,False)'if True, then print to DOT-MATRIX

const("xyz",varDouble,0)

and the only bit of code I understand and "adjust" accordingly
is infact this:

function RoundUp(abc)

dim x
x = Frac(abc)
if (x = 0.25) and (x = 0.75) then x = 1 end if
RoundUp = Int(abc) + x
end function

The precise point where a macro should have been activated
is where I add sounds at certain parts of the script.
This is after pleading with the programmer at the time to add something other than the printer to let me know a calculation has been logged.
the code for sound is:

sound("soundABC.wav",True)

it is at THAT very point wherever I have "sound"
I would use:

excel("macro1.xls",(True)

Hope you all see the picture, for me to know WHEN to copy and paste my data from the log files, I wait for sound.
It's the pits, believe me.

There is no other interface except for the Server settings to set up each script and viewing it's database through a Window Exlporer thing.
No direct access to the database either, I rely on the script's product of the log files.
Thats my project, that's what I require to fix up one and for all so I can quietly get on with my hobby that was meant to venture to a business.

Having said this, if I have a script I know is working, would it be correct in saying, all I have to do is covert the current script language to your product's script editor language, and by the time I do all this and tweak it all, I should be able to write my own scripts ?

Will this idea work to ?

Silver7 · Post by **Silver7** » Sun Aug 13, 2006 12:51 am

OK,
Bob Hansen, according to your code as posted and corrected by SkunkW.

Interim Test;
I have set up a special Excel Workbook and all it has is a "counter" macro
So each time the log file changes, the "do something" is to Activate
the counter macro in that Workbook.This is to get it all working right and then simply change to code from thje counter macro reference to the Import macro.

Name of Excel Workbook is:
counter1

Name of Macro is:
Sub TimerTest1()

Workbook resides in :

C:/Program Files/Folder1/Folder2/counter1.xls

Any help to get this test to work is much appreciated.

Silver7 · Post by **Silver7** » Sun Aug 13, 2006 2:21 am

Finaly,
I found this link
http://www.mjtnet.com/forum/viewtopic.p ... 8a5ad8cd72

Makes sense somehow, where do I go and alter the code to get connected to Firbird and see what's inside Firebirds database because i will require to constrruct a database so the info goes somewhere in from a specific web page.
Once the live data feed is in the database and all working properly, scripting can commense I guess, that's the way I see it for now.

hints and tips or doers to get me started most welcome.

Thanks.

Bob Hansen · Post by **Bob Hansen** » Sun Aug 13, 2006 2:24 am

Rename the macro "Sub TimerTest1()" in Excel to "Auto_Run", without the quotes. That should then run as soon as the workbook "counter1" is opened up.

Perhaps modify that macro to exit Excel when it is done. Once Excel closes then Macro Scheduler can continue, can go back into a loop waiting again for the next file change to trigger the process again.

The Run Program in Macro Sceduler will look like this:

Code: Select all

Run Program>C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE C:\Program Files\Folder1\Folder2\counter1.xls

You will need to replace the Excel.exe path with the correct one for your system.

Silver7 · Post by **Silver7** » Sun Aug 13, 2006 4:07 am

OK
Workbook is always open. because I need to see the result of the calculation in Excel, it's like a "GUI" type of thing.

'Your Code:
Run Program>C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE C:/Program Files/Folder1/Folder2/counter1.xls

Excel macro is ( replaces the Import macro for now)

Sub Auto_Run()

Sheets("Counter").Range("F2").Value = Sheets("Counter").Range("F4").Value
End Sub

Actual Excel path is :

C:\Program Files\Microsoft Office\Office\EXCEL.EXE

Question 1
'Should there now be a string path leading to activate macro ?

Question 2
Does it matter if the Workbook is not in the same folder as MSOFFICE.
I have had this problem before die a OLE something or rather unless Mac.Sched product has taken care of it OLE

Question 3
Forward and Backslashes don't seem right, but I could be wrong ?

If I put all this in it's right syntax in Mac.Sched from what I have gathered so far;

label>BaseLine
filesize>Silver7_script,sizeresult

label>WaitTime
Wait>60 (to wait one minute)
Filesize>Silver7_script,sizecheck
if>sizeresultDoSomething
else>
goto>Wait60
endif>

Label>DoSomething

if>sizeresultDoSomething
else>
goto>WaitTime
endif>

I don't understand your syntax,yet.. What is "DoSomething" replaced with ?
Am I missing something ?

Thanks.

Bob Hansen · Post by **Bob Hansen** » Sun Aug 13, 2006 7:41 pm

You should have renamed your Excel macro to Auto_Run.
Your gathering of notes re Macro Scheduler, with values you have provided, should currently look something like this:

Code: Select all

label>BaseLine
filesize>FileName,sizeresult

label>WaitTime
Wait>60 
Filesize>FileName,sizecheck

IF>%sizeresult%<>%sizecheck%,DoExcel
GoTo>WaitTime

Label>DoExcel
Run Program>C:\Program Files\Microsoft Office\Office\EXCEL.EXE  C:\Program Files\Folder1\Folder2\counter1.xls
GoTo>WaitTime

You can see from this that workbook can be in different location than excel.exe. You are now introducing a new issue, that Excel is already open. So now you need to modify the DoExcel code to deal with that. I cannot do this now but here is a quick outline:
Label>DoExcel
If Excel is not open, Run Program>Excel with Auto_Run macro to run as already done above.
If Excel is open, Auto_Run need to be called from within Excel, so use Macro Scheduler to SetFocus on the workbook and Send> keystrokes to call the macro Auto_Run. Or send the keys to Copy F4 to F2. Then continue the Macro Sceduler script, returning to WaitTime.

Re "DoSomething", although not used now, it is just our way of saying put in whatever code you want, we don't have enough info to put in details.

You will also need to deal with when/how you call the MAcro Scheduler script. You need a method to tell the Macro Scheduler script when you are done in Excel. If the Excel macro exits Excel, then control will return to the script following the Run Program line. But if you are not using the Run Program line, because Excel was already open, then the Macro Scheduler script will continue after the Send> commands.

30 Day free trial interim questions

30 Day free trial interim questions

Script Test 1