On Sat, 3 Mar 2007 18:43:33 +0900, Martin Blackman wrote: > Ken > I see there's a nice example of writing to Excel on your website using > vbscript. Is this the sort of thing that might trigger anti-virus > programs ? I just tried it on 2 machines successfully with no > anti-virus problems myself.
It depends on the level of sensitivity of the antivirus software. Most (all?) will not stop all vbscript executions, but look for things like access to the file system in the code to be run to determine whether to bring up a warning or not. Since the Excel example is just for communications with Excel, it is very unlikely that it would trigger antivirus software (unless there is a generic "Don't allow VBScript files to run" option in the AV software that's turned on). > Would you be good enough to expand on that example to setting/getting > data from named cells or ranges ? Sure... this is based on modifying the "put ... into tScript" portion of the SendToXL handler that is described on the Tip page (http://www.sonsothunder.com/devres/revolution/tips/iac001.htm). The relevant portion is copied below: on mouseUp SendToXL "500","B3" end mouseUp on SendToXL pWhat,pCell put "Dim ObjXL" & cr & \ "Set ObjXL = GetObject(," & q("Excel.Application") & ")" & cr & \ "ObjXL.Range(" & q(pCell) & ").Value =" && q(pWhat) into tScript -- Put the script into a file on disk put "C:\VBSTemp.vbs" into tFile put tScript into url("file:" & tFile) -- Run the file set the hideConsoleWindows to true get shell("cscript.exe //nologo" && tFile) -- Now, delete the file; best way is to give it 1 second to complete -- before deleting, so I'll use the "send in " method to -- do this: send "delete file" && q(tFile) to me in 1 second end SendToXL If you have a named cell, all you need to do is pass in the name of the cell in the pCell parameter, so you could name a cell "MyNamedCell" and then execute: SendToXL "500","MyNamedCell" To get data from a cell, all you need to do is modify the SendToXL handler to return a result using WScript.Echo. Here's a "GetFromXL" handler that will retrieve the data from a specific cell (using A1, R1C1, or named cell format) - I removed the comments to make it more compact: function GetFromXL pCell put "Dim ObjXL" & cr & \ "Set ObjXL = GetObject(," & q("Excel.Application") & ")" & cr & \ "WScript.Echo ObjXL.Range(" & q(pCell) & ").Value" into tScript put "C:\VBSTemp.vbs" into tFile put tScript into url("file:" & tFile) set the hideConsoleWindows to true get shell("cscript.exe //nologo" && tFile) send "delete file" && q(tFile) to me in 1 second return it end GetFromXL So you could do this: put GetFromXL("MyNamedCell") into tVar -- tVar now has "500" in it To get or set a multi-cell range requires a bit more work - you basically need to loop through all the cells in the range, which is several more lines of VBScript code. Here's a modified "GetFromXL" function that will do the trick (and still keep backward compatibility): function GetFromXL pRangeRef put "Dim ObjXL,tNumRows,tNumCols,tRetVal,tRow,tCol" & cr & \ "Set ObjXL = GetObject(," & q("Excel.Application") & ")" & cr & \ "tNumRows = ObjXL.Range(" & q(pRangeRef) & ").Rows.Count" & cr & \ "tNumCols = ObjXL.Range(" & q(pRangeRef) & ").Columns.Count" into tScript put tScript & cr & "For tRow = 1 To tNumRows" & cr & \ "For tCol = 1 to tNumCols" & cr & \ "If tCol <> tNumCols Then" & cr & \ "tRetVal = tRetVal & ObjXL.Range(" & q(pRangeRef) & ").Cells(tRow,tCol).Value & vbTab" & cr & \ "Else" & cr & \ "tRetVal = tRetVal & ObjXL.Range(" & q(pRangeRef) & ").Cells(tRow,tCol).Value & vbCrLf" & cr & \ "End If" & cr & "Next" & cr & "Next" into tScript put tScript & cr & "tRetVal = Left(tRetVal,Len(tRetVal) - 2)" & cr & \ "WScript.Echo tRetVal" into tScript put "C:\VBSTemp.vbs" into tFile put tScript into url("file:" & tFile) set the hideConsoleWindows to true get shell("cscript.exe //nologo" && tFile) send "delete file" && q(tFile) to me in 1 second if char -1 of it is CR then delete char -1 of it -- strip any trailing CR return it end GetFromXL So now you can do any of these: put GetFromXL("A1:D4") into tTable put GetFromXL("MyNamedRange") into tTable put GetFromXL("MyNamedCell") into tCell put GetFromXL("A1") into tCell Setting a range is basically the same, but goes in the opposite direction. Here's a new replacement "SendToXL" handler that will handle individual cells and ranges in the same way that GetFromXL does above: on SendToXL pWhat,pRangeRef -- assumes a tab & CR delimited set of data set the itemDel to tab put the number of items of line 1 of pWhat into tNumCols put the number of lines of pWhat into tNumRows put "Dim ObjXL,tRetVal,tRow,tCol" & cr & \ "Dim tDataA(" & tNumCols & "," & tNumRows & ")" & cr & \ "Set ObjXL = GetObject(," & q("Excel.Application") & ")" into tScript repeat with x = 1 to tNumCols repeat with y = 1 to tNumRows put tScript & cr & "tDataA(" & x & "," & y & ") = " & q(item x of line y of pWhat) into tScript end repeat end repeat put tScript & cr & "For tRow = 1 To" && tNumRows & cr & \ "For tCol = 1 to" && tNumCols & cr & \ "ObjXL.Range(" & q(pRangeRef) & ").Cells(tRow,tCol).Value = tDataA(tCol,tRow)" & cr & \ "Next" & cr & "Next" into tScript put "C:\VBSTemp.vbs" into tFile put tScript into url("file:" & tFile) set the hideConsoleWindows to true get shell("cscript.exe //nologo" && tFile) send "delete file" && q(tFile) to me in 1 second end SendToXL So something like: put "Hello" & tab & "there" & cr & "From" & tab & "Ken" into tData SendToXL tData,"A1:B2" Have fun! Ken Ray Sons of Thunder Software, Inc. Email: [EMAIL PROTECTED] Web Site: http://www.sonsothunder.com/ _______________________________________________ use-revolution mailing list [email protected] Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-revolution
