VBA - Excel - LC
Posted: Sat Nov 22, 2014 6:11 am
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:
from there I use the following code to open the template and populate the cells using the resulting VBScript:
then, I use:
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.
Code: Select all
get shell("start EXCEL.exe")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 tScriptCode: 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 secondsthe 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.