problem with excel - vbscript

[wee wullie]

I'm trying to use the excel vbscript from Ken Rae's http://www.sonsothunder.com/devres/revo ... iac001.htm site,

i get an error when i test it, does anyone know if this code needs to be updated?

[SparkOut]

Hi Wee Wullie
There could be various things that need updating, or such. It may be that the temporary file is being prevented from being written to the root of the C: drive, as scripted. Or that you haven't included the "q" function to quote strings in the script. Or... etc.
Have you any more information? Or specific error messages.
In any event, being able to "do ... AS vbscript" is probably a cleaner way to go nowadays.
A bit more info and I'm sure we can help

[wee wullie]

Hi Sparkout, i'm trying to test the code from Ken Rae's awesome Sonsothunder site, for sending to and getting from excel, i am probably missing something obvious here, i have attached the rev file as follows:

XLtest.rar

send2/getFromXLtest

(1.5 KiB) Downloaded 355 times

any help would be very appreciated.

Kind regards
WeeWullie

[SparkOut]

Ken uses a function he alludes to in the first part of the section q() which just wraps the input in double quotes, which is missing from the script you copied.
Add

Code: Select all

function q pWhat
  return quote & pWhat & quote
end q

and the next problem is the handler is hard coded to write the temporary vbscript file to the C:\ drive, which in more recent Windows versions is a protected location. Change the line where the vbscript is written to the file to

Code: Select all

put specialFolderPath("temporary") & "/VBSTemp.vbs" into tFile

and because the file path may have spaces etc in, the full file path should also be wrapped in quotes in the get shell line

Code: Select all

get shell("cscript.exe //nologo" && q(tFile))

Then it should work - BUT an Excel worksheet must be open beforehand, this script will not start the Excel application and a new worksheet if one is not already in use on the computer.
Ken's script really could do with being updated to take advantage of being able to "do ... as vbscript" without writing out temporary files and deleting them. I may be able to have a look soon, but the above changes should make things start to work.

[wee wullie]

Sparkout, you're a star!, works perfectly both sending to and receiving from,
VERY MANY THANKS INDEED!!!

kindest regards

WeeWullie

[SparkOut]

For info, zryip theSlug (yes! really) has released this open source Excel library http://www.aslugontheroad.com/download/ ... en-sources

with also a commercial version available.

[wee wullie]

Hi Sparkout, zryip theSlug - yeah,
I have an ADO vbscript to write to excel and it works fine from my vbs editor, the problem is that i'm trying to use the replace fld "First" with fld "First" in tScript as i do in access, however the field in the script has rs.("first"), is there a way to lose the rs. and the () leaving only the "First", if you know what i mean.

wullie

[SparkOut]

Hi wullie, I'm not sure I do understand you. In which script is the field you're trying to replace? At what stage? Is it in the vbs script or a Livecode script?
You can use

Code: Select all

replace "rs.(" & quote & "first" & quote & ")" with "fld" && quote & "First" & quote in tScript

in a Livecode script, but I'm not sure where you want whatever it is to be replaced.

[wee wullie]

Hi Sparkout,
I didn't see your post, so I used this method instead:

Code: Select all

put "rs(" & QUOTE & "First" & QUOTE &  ")" & " " & "=" & " " & QUOTE & fld "First" &  QUOTE into tFirst  
   put tFirst into line 15 of tScript

i works perfectly and it does pretty much the same thing as your post, it just goes about it in a different way, Sorry if i didn't explain properly in the first place.

Cheers