VBS not executing correctly from FM

[Genx]

Hi,

Just trying to run some vbscripts from FM... with no luck.

I can run the script directly by doubleclicking it, I can run it using (Start-->Run) and specifying the filepath in quotes.

But no matter what i do, i can't get it to run properly using SendEvent and Quote($FilePath) where filepath is the correct path of the vbscript. Yes i'm sure i've got the file reference right because i copied it out of FM to try and execute through the cmd just to check -- no difference.

I've tried almost everything i can think of and it just will not execute properly...

I was under the impression that SendEvent was the equivilent of run... was i wrong?

I've just spent around 6 hours writing a dynamic upload monitor, and i'd rather not have to run through the whole thing again using vb.

Anyway, either way i'd really like to find out why it's misbehaving.

Cheers,

~Genx

[Wim Decorte]

Not enough detail, Genx. What exactly is in your $FilePath variable?

If you copy the content of the variable from the data monitor and paste it into a CLI (dos) window, what kind of error does it give you there?

[Genx]

Ive tried that, and it doesnt give me an error. I used the dataviewer to view the variable while executing the script --> Executes fine.

Seeing as this is an export field sort of thing aswell where the vbs is dynamically generated, exported then run, i also tried opening file after export... No luck there either.

It's very strange, it seems to do something (or it might just be me) but it simply won't execute the script properly (there seems to be some sort of visual indication that the script gets executed but...). The vbscript takes anywhere between 10 seconds and 2 minutes to complete.

Ill have another look now, but i was working remotley all of last night so my eyes hurt.. But will have a look.

[Genx]

Okay,

It turns out it had nothing to do with FM, it was because i was using relative file paths.

Now then, here is my question, how do i go about executing a vbscript in directory c:scriptsx.vbs as if i was actually executing the file at that location.

Would i go through cmd and pushd first? Or could i create a batch file in c:scriptsexecuteX.bat that would then execute as if i was executing from there?

Cheers Wim,

Genx

[Wim Decorte]

I'm having a hard time visualizing what you're doing...

if you want to execute the vbscript at location "c:scriptsx.vbs" then you'd use a Send Event script step with a calculated string of:

cmd /c c:scriptsx.vbs

There's not much that can go wrong in executing the thing. If it takes a long time to execute then it must be something in the VBscript itself.

[Genx]

No no, the issue had to do with relative file paths within the vbs file itself - i.e. FM had nothing to do with it, it was just me being crazy.

The question now has not to do with executing the vbs file, but rather... within the vbs file i use a text file called textfile.txt, however i don't specify a filepath, because im using relative file path (i assumed it was relative to the location of the vbs file). However, relative file path seems to work not from where the vbs file resides, but rather from where it's executed. What i want to know is how to change the directory where "x.vbs" is executed to be c:scriptsx.vbs --> Leading textfile.txt to be an effective file path in c:scriptstextfile.txt.

Don't worry relative filepaths in vbs confuse me, but ill try changing the directory in cmd prior to executing the vbs and see what effect that has on my relative paths.

Anyway, is all good. Cheers Wim

[Genx]

Okay, i've thought of a better way of phrasing my question.

I want to store as a variable in the VBS file im executing, the file path of that VBS file.

E.g.

If im executing C:ScriptsMyScriptsfile.vbs

I want to store the filepath C:ScriptsMyScripts as a variable within file.vbs

Possible?

[Wim Decorte]

First thing that comes to mind is to pass it to the vbscript as a command line argument. inside your VBscript you can then store it in a variable with

thePath = wscript.arguments.unnamed.item(0)

Check the VBscript help file for some good info on named and unnamed arguments.

[Wim Decorte]

Another way of doing it, is not to let the vbscript live outside FM but to dynamically generate it through substitutes in FM. then you export the field content of whatever global you've generated the vbscript syntax in and away you go.

[Genx]

Well thats what i was doing, ... for most of it, i was still at that point under the impression that everything was relative to the location of the file..

Shouldn't there be some internal vbs function for the location of the script file or something. I want to know in general not for FM, already fixed by dynamically generating .

[Wim Decorte]

wscript.ScriptFullName

And from there you can extract the path name...

[Genx]

Ooooh, yay perfect.

Cheers Wim,

Genx

[Kevin Mortimer]

Hi Wim

I am having a similar problem where my VB script will not execute properly. When using the following activation line from filemaker send event

"cmd /c cscript " & Quote ( "C:/Program Files/HRS/vbs/Outlook1.vbs" )

Nothing happens. Then when I double click the actual vbs file I get an error of

Line 1

Character 9

Expected end of statement.

The vbs is all on one line seperated by little squares in the text file but when I seperate out the one line into its induvidual lines by replacing the square with a carriage return, then the script runs fine.

What is happening here. I have used other scripts that are all on one line and the script runs fine. Here is the vbs code once it is seperated out onto individual lines.

Kind Regards

Kev

Dim App

Dim Mail

Set App = CreateObject("Outlook.application")

Set Mail = App.CreateItem(0)

Mail.To = "[email protected]"

Mail.Cc = ""

Mail.Bcc = ""

Mail.Subject = "CV Sent"

Mail.Body ="Dear Courtney"+chr(13) _

+""+chr(13) _

+"Further to our conversation regarding the candidates for the role of General Manager, please find attached the following CVs, James Sutcliffe, Mel Gibson."+chr(13) _

+""+chr(13) _

+"If you have any queries, please do not hesitate to contact me."+chr(13) _

+""+chr(13) _

+""+chr(13) _

+"Kind regards "+chr(13) _

+""+chr(13) _

+""+chr(13)

Mail.Attachments.Add("G:/HRS Documents/CVs/JamesSutcliffe.doc")

Mail.Attachments.Add("G:/HRS Documents/CVs/MarkPuertolas.doc")

Mail.Save

Set Mail = Nothing

Set App = Nothing

[Wim Decorte]

Hi Kevin,

The little squares you're seeing in your script are not the problem. Those are because FM treats text internally as Mac Text (uses only LF as the end-of-line character, whereas Windows uses CRLF). So when you open the VBS in Windows, Windows doesn't see CRLFs and puts everything on one line. The Windows Script host however knows how to handle it, so it's not a problem.

The problem with your script is indeed on line 9:

Mail.Body ="Dear Courtney"+chr(13) _

should be

Mail.Body ="Dear Courtney" & vbcrlf _

So replace all the "+chr(13)" with "& vbcrlf"

[Kevin Mortimer]

Hi Wim

Thanks for that. I have amended the script so it now looks like this. It still does the same as before. When the vbs is in one line it does not run but gives the same error as before. But when I put each VBS line on its own line it runs fine.

Any further thoughts

Dim App

Dim Mail

Set App = CreateObject("Outlook.application")

Set Mail = App.CreateItem(0)

Mail.To = "[email protected]"

Mail.Cc = ""

Mail.Bcc = ""

Mail.Subject = "CV Sent"

Mail.Body ="Dear Courtney"& vbcrlf _

+""& vbcrlf _

+"Further to our conversation regarding the candidates for the role of General Manager, please find attached the following CVs, James Sutcliffe, Mel Gibson."& vbcrlf _

+""& vbcrlf _

+"If you have any queries, please do not hesitate to contact me."& vbcrlf _

+""& vbcrlf _

+""& vbcrlf _

+"Kind regards "& vbcrlf _

+""& vbcrlf _

+""& vbcrlf

Mail.Attachments.Add("G:/HRS Documents/CVs/JamesSutcliffe.doc")

Mail.Attachments.Add("G:/HRS Documents/CVs/MarkPuertolas.doc")

Mail.Save

Set Mail = Nothing

Set App = Nothing

Regards

Kev

[Wim Decorte]

I guess i wasn't clear enough:

Do away with all the "+" and use the "&" instead.

Not sure what you're trying to do with:

+""& vbcrlf _.

If you need two returns just do:

"something" & vbCrLf & vbCrLf & "something else"

There is also a limit as to the # of code line breaks "_" you can use, so try to limit those.

[Kevin Mortimer]

Hi Wim

Thanks for your suggested advice. I have made the neccessary changes and the script works fine when it is seperated out into individual lines but it still will not run as a single line. Below is the VBS when it is all in one line. I seperate it out by replacing the square blocks wirh returns and then it works fine.....why is this and how can get it to work as one line?

Dim AppDim MailSet App = CreateObject("Outlook.application")Set Mail = App.CreateItem(0)Mail.To = "test111111"Mail.Cc = ""Mail.Bcc = ""Mail.Subject = "CV Sent"Mail.Body ="Dear Courtney"& vbcrlf& vbcrlf&"Further to our conversation regarding the candidates for the role of General Manager, please find attached the following CVs, James Sutcliffe, Mel Gibson."& vbcrlf& vbcrlf&"test testr"& vbcrlf& vbcrlf&"Regards"& vbcrlf&"Kev"& vbcrlf& vbcrlf&""& vbcrlfMail.Attachments.Add("G:/HRS Documents/CVs/JamesSutcliffe.doc")Mail.Attachments.Add("G:/HRS Documents/CVs/MarkPuertolas.doc")Mail.SaveSet Mail = NothingSet App = Nothing

[Wim Decorte]

Hi Kevin,

I really don't follow what you're doing...

If you have the full VBscript syntax in a FM text field with returns after each line and you do a "export field contents" script step to a VBS file and then execute the VBS file on the hard disk it should just run.

Not sure what you're doing when you say "it will still not run as a single line".

The Windows script host expects a hard return after each line.

[Kevin Mortimer]

Hi Wim

Yes this is exactly true. I have the vbs code in a a filemaker field with each line seperated by a carriage return. When I export the field to a .vbs file and then run it I get an error

Line one

Charater nine

Expected end of statement

When I open the .vbs in notepad. All the code is on one line with squares where the carriage returns were. If I then replace the the squares with carriage returns then the script runs fine.

This is only part of the initial question. Sometines when I execute the .vbs using the send event (cmd /c cscript " & Quote ( "C:/Program Files/HRS/vbs/Outlook1.vbs") Nothing happens, no error message no result but when I double click on the .vbs file I get the error listed above. I just can not figure out why this script will not run.

I have other scripts which I export from filemaker using the same method and they work fine. Here is the script once it is exported from filemaker.In the actual .vbs text file the carriage returns are squares but here they are funny little arrows.

Dim AppDim MailSet App = CreateObject("Outlook.application")Set Mail = App.CreateItem(0)Mail.To = "[email protected]"Mail.Cc = ""Mail.Bcc = ""Mail.Subject = "CV Sent"Mail.Body ="My email text."& vbcrlf& vbcrlf&"Regards"& vbcrlfMail.SaveSet Mail = NothingSet App = Nothing

I hope this helps....sorry if I am not making much sense......it is sometimes difficult to try and explain your problem.

[Wim Decorte]

The squares are a normal by-product of how FM stores text internally; it uses Ascii 10 (Lf) as a return instead of CrLf (Assci 13+10) that Windows does. The Ascii 10 characters shows up as a square in NotePad.

But I've never seen the Windows Script host choke on it though. What version of WSH do you have on your machine? Run this in a VBscript: MsgBox wsh.Version

There is another method you can use:

- make a separate table in your file

- make a scripted routine that will create one record in that table for each line in your VBscript (by parsing out the full syntax field)

- export the records (carefully choose the format, you don't want quotes around the data) to a VBS file

- run it

- delete the records in the new table

(this can get a bit complex in a multi-user environment where more than user is using the functionality at the same time)

[Wim Decorte]

or of course you can use the VBscript plugin :)

[Kevin Mortimer]

Hi Wim

I went back and check my filemaker scripts. I overlooked the export script step. Instead of exporting field contents I had export records....doh

So now it works fine.

Thanks again for your time and help...........and I think at some point our company will be getting that VBS plugin to.

Regards

Kev

[Genx]

This is completely off the current topic, but when executing in the traditional method of exporting the file and opening after export, i will in 3 out of 14 computers every once in a while get the error "the file "file.vbs" could not be found" where file.vbs is the file i'm exporting and opening on export.

Seeing as i don't use it any more, it's not really an issue, but i'm wondering if you've experienced this before developing your plugin wim?

[Genx]

Kevin get it, I'm 100% satisfied with it :)

[Wim Decorte]

When you did it that way, Genx did you include a pause script step after the export and before the execute?

If not the execute may be coming too fast, trying to run the file when the OS is still writing to the file...

[Wim Decorte]

Glad you got it working, Kev.

Happy VBscripting!

[Genx]

Hey wim,

I had considered that, but i also figured that FM would only try to execute when the file had finished getting written to?

Nevertheless, the drawback with the pause step is that if you are running a complex script behind the scenes, generally on backend type layouts, you use the freeze window step, if you pause, the window is refreshed...

Anyway, it doesn't really matter.

[Daniel Farnan]

Late update to this, I know, but I was trying to use the export field contents technique and was getting processing errors from the vbs code being all on one line.

 

I fixed it by appending a call to a custom function, CRLF, to the end of each line of vbs code in my FMP calc. The custom function:

Char ( 13 ) & Char ( 10 )

For example:

"Set objClient = CreateObject ( "Outlook.Application" )" & CRLF &
"Set objMsg = objClient.CreateItem(0)" & CRLF

Hope this helps someone.