VBA - Excel - LC

[Greg O.]

I am getting information out of a SQLite database and trying to put it into an Excel Template. I am trying to automate the process as much as possible. While I have failed to get the LC "Launch" command to work I did get the following to open Excel:

Code: Select all

 get shell("start EXCEL.exe")

from there I use the following code to open the template and populate the cells using the resulting VBScript:

Code: Select all

 put specialFolderPath("desktop") & "/frontDeskNum.xltx" into tFile
   --actually build the vb script
   put "Dim ObjXL,tRetVal,tRow,tCol" & cr & \
         "Dim tDataA(" & tNumCols & "," & tNumRows & ")" & cr & \
         "Set ObjXL = GetObject(," & q("Excel.Application") & ")" &cr & \
         "ObjXL.Workbooks.Open " &q(tFile) 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

then, I use:

Code: Select all

 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 10 seconds

to run the script and delete the script file.

the odd behavior is after excel opens, but before the script get written out, I put focus on another program the script will run, if Excel retains focus the script will never run and no information is entered into Excel.

so the single question, in something like 38 parts is this: why is this behavior happening, how do I counteract it, and the bajillion dollar question: what is the most elegant way to accomplish all of this.

Thank you.