problem with excel - vbscript
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
-
- Posts: 78
- Joined: Fri Oct 03, 2008 10:13 am
problem with excel - vbscript
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?
i get an error when i test it, does anyone know if this code needs to be updated?
Re: problem with excel - vbscript
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
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
-
- Posts: 78
- Joined: Fri Oct 03, 2008 10:13 am
Re: problem with excel - vbscript
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:
Kind regards
WeeWullie
any help would be very appreciated.Kind regards
WeeWullie
Re: problem with excel - vbscript
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.
Addand 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 toand because the file path may have spaces etc in, the full file path should also be wrapped in quotes in the get shell line
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.
Add
Code: Select all
function q pWhat
return quote & pWhat & quote
end q
Code: Select all
put specialFolderPath("temporary") & "/VBSTemp.vbs" into tFile
Code: Select all
get shell("cscript.exe //nologo" && q(tFile))
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.
-
- Posts: 78
- Joined: Fri Oct 03, 2008 10:13 am
Re: problem with excel - vbscript
Sparkout, you're a star!, works perfectly both sending to and receiving from,
VERY MANY THANKS INDEED!!!
kindest regards
WeeWullie
VERY MANY THANKS INDEED!!!
kindest regards
WeeWullie
Re: problem with excel - vbscript
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.
with also a commercial version available.
-
- Posts: 78
- Joined: Fri Oct 03, 2008 10:13 am
Re: problem with excel - vbscript
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
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
Re: problem with excel - vbscript
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 usein a Livecode script, but I'm not sure where you want whatever it is to be replaced.
You can use
Code: Select all
replace "rs.(" & quote & "first" & quote & ")" with "fld" && quote & "First" & quote in tScript
-
- Posts: 78
- Joined: Fri Oct 03, 2008 10:13 am
Re: problem with excel - vbscript
Hi Sparkout,
I didn't see your post, so I used this method instead:
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
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
Cheers