Can REV create Excel files?

[Ken Ray]

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: kray at sonsothunder.com
Web Site: http://www.sonsothunder.com/