Gary
21-07-2023, 20:11
This is a test application using SendString with Excel.
'---Script created on 07-20-2023 21:51:17 by G Robinson
' uses sendstring to fill in Excel spreadsheet
uses "UI"
Dim hInstance as DWord
Dim hWnd as Long
'works with LibreOffice Calc or Excel
'have a clear spreadsheet page for program
'have spreadsheet active (does not have to be top level window)
'hWnd = Win_FindByTitle("...LibreOffice Calc")
hWnd = Win_FindByTitle("...- Excel")
Long currentMonth = Month(Now) 'GET CURRENT MONTH
dim i as Long,j as long
dim n as double
if hWnd Then
sleep 1000
Win_SetForeground(hWnd)
sleep 1000
'Start from same point on spreadsheet top left
topLeftSpreadSheet()
sleep 500
'fill in month headers
monthHeaders()
'top left
topLeftSpreadSheet()
'column headers
columnHeaders()
'top left
topLeftSpreadSheet()
'Move to proper position right AND DOWN on spreadsheet
SendString "{RIGHT}",0,1500 'RIGHT OF SPREADSHEET
SendString "{DOWN}",0,1500 'DOWN OF SPREADSHEET
'fill in spreadsheet with random numbers
for j=1 to 12
for i=1 to 12
'put random number in spreadsheet
n = RNDF(10.00, 100.00 , 2)
SendString str$(n),0,200
SendString "{ENTER}",0,200
Next
SendString "{TAB}",0,500 'MOVE RIGHT
SendString "{UP}{UP}{UP}{UP}{UP}{UP}{UP}{UP}{UP}{UP}{UP}{UP}",0,500 'MOVE TO TOP OF COLUMN AND REPEAT
next
'fill in the sums of each column in spreadsheet
sumsColumns()
SLEEP 1000
end if
BEEP
function sumsColumns()
DIM I AS LONG
'positioning cursor to HOME position
topLeftSpreadSheet()
'position for total sums
FOR I=1 to 13
SendString "{DOWN}",0,200
next
SendString "{RIGHT}",0,200
'total all columns
SendString "=SUM(B2:B13){TAB}",0,200
SendString "=SUM(C2:C13){TAB}",0,200
SendString "=SUM(D2:13){TAB}",0,200
SendString "=SUM(E2:E13){TAB}",0,200
SendString "=SUM(F2:F13){TAB}",0,200
SendString "=SUM(G2:G13){TAB}",0,200
SendString "=SUM(H2:H13){TAB}",0,200
SendString "=SUM(I2:I13){TAB}",0,200
SendString "=SUM(J2:J13){TAB}",0,200
SendString "=SUM(K2:K13){TAB}",0,200
SendString "=SUM(L2:L13){TAB}",0,200
SendString "=SUM(M2:M13){TAB}",0,200
SendString "{TAB}",0,200
'SendString "here",0,200
end function
function columnHeaders()
SendString "{LEFT}",0,200
SendString "{ENTER}",0,200 'MOVE DOWN
SendString "FOOD{ENTER}",0,200 'MOVE DOWN
SendString "CLOTHING{ENTER}",0,200 'MOVE DOWN
SendString "SPORTS{ENTER}",0,200 'MOVE DOWN
SendString "CONCERTS{ENTER}",0,200 'MOVE DOWN
SendString "HOUSING{ENTER}",0,200 'MOVE DOWN
SendString "CARS{ENTER}",0,200 'MOVE DOWN
SendString "INSURANCE{ENTER}",0,200 'MOVE DOWN
SendString "UTILITIES{ENTER}",0,200 'MOVE DOWN
SendString "ELECTRIC{ENTER}",0,200 'MOVE DOWN
SendString "SAVING{ENTER}",0,200 'MOVE DOWN
SendString "RESTURANTS{ENTER}",0,200 'MOVE DOWN
SendString "FUEL{ENTER}",0,200 'MOVE DOWN
SendString "TOTAL",0,200 'MOVE DOWN
end function
function monthHeaders()
SendString "{TAB}",0,200 'MOVE RIGHT
SendString "JAN{TAB}",0,200 'MOVE RIGHT
SendString "FEB{TAB}",0,200 'MOVE RIGHT
SendString "MAR{TAB}",0,200 'MOVE RIGHT
SendString "APR{TAB}",0,200 'MOVE RIGHT
SendString "MAY{TAB}",0,200 'MOVE RIGHT
SendString "JUN{TAB}",0,200 'MOVE RIGHT
SendString "JUL{TAB}",0,200 'MOVE RIGHT
SendString "AUG{TAB}",0,200 'MOVE RIGHT
SendString "SEP{TAB}",0,200 'MOVE RIGHT
SendString "OCT{TAB}",0,200 'MOVE RIGHT
SendString "NOV{TAB}",0,200 'MOVE RIGHT
SendString "DEC",0,200 'MOVE RIGHT
end function
function topLeftSpreadSheet() 'top left on spreadsheet
SendString "{CTRL_DOWN}{HOME}{CTRL_UP}",0,1500 'TOP OF SPREADSHEET
end function
'---Script created on 07-20-2023 21:51:17 by G Robinson
' uses sendstring to fill in Excel spreadsheet
uses "UI"
Dim hInstance as DWord
Dim hWnd as Long
'works with LibreOffice Calc or Excel
'have a clear spreadsheet page for program
'have spreadsheet active (does not have to be top level window)
'hWnd = Win_FindByTitle("...LibreOffice Calc")
hWnd = Win_FindByTitle("...- Excel")
Long currentMonth = Month(Now) 'GET CURRENT MONTH
dim i as Long,j as long
dim n as double
if hWnd Then
sleep 1000
Win_SetForeground(hWnd)
sleep 1000
'Start from same point on spreadsheet top left
topLeftSpreadSheet()
sleep 500
'fill in month headers
monthHeaders()
'top left
topLeftSpreadSheet()
'column headers
columnHeaders()
'top left
topLeftSpreadSheet()
'Move to proper position right AND DOWN on spreadsheet
SendString "{RIGHT}",0,1500 'RIGHT OF SPREADSHEET
SendString "{DOWN}",0,1500 'DOWN OF SPREADSHEET
'fill in spreadsheet with random numbers
for j=1 to 12
for i=1 to 12
'put random number in spreadsheet
n = RNDF(10.00, 100.00 , 2)
SendString str$(n),0,200
SendString "{ENTER}",0,200
Next
SendString "{TAB}",0,500 'MOVE RIGHT
SendString "{UP}{UP}{UP}{UP}{UP}{UP}{UP}{UP}{UP}{UP}{UP}{UP}",0,500 'MOVE TO TOP OF COLUMN AND REPEAT
next
'fill in the sums of each column in spreadsheet
sumsColumns()
SLEEP 1000
end if
BEEP
function sumsColumns()
DIM I AS LONG
'positioning cursor to HOME position
topLeftSpreadSheet()
'position for total sums
FOR I=1 to 13
SendString "{DOWN}",0,200
next
SendString "{RIGHT}",0,200
'total all columns
SendString "=SUM(B2:B13){TAB}",0,200
SendString "=SUM(C2:C13){TAB}",0,200
SendString "=SUM(D2:13){TAB}",0,200
SendString "=SUM(E2:E13){TAB}",0,200
SendString "=SUM(F2:F13){TAB}",0,200
SendString "=SUM(G2:G13){TAB}",0,200
SendString "=SUM(H2:H13){TAB}",0,200
SendString "=SUM(I2:I13){TAB}",0,200
SendString "=SUM(J2:J13){TAB}",0,200
SendString "=SUM(K2:K13){TAB}",0,200
SendString "=SUM(L2:L13){TAB}",0,200
SendString "=SUM(M2:M13){TAB}",0,200
SendString "{TAB}",0,200
'SendString "here",0,200
end function
function columnHeaders()
SendString "{LEFT}",0,200
SendString "{ENTER}",0,200 'MOVE DOWN
SendString "FOOD{ENTER}",0,200 'MOVE DOWN
SendString "CLOTHING{ENTER}",0,200 'MOVE DOWN
SendString "SPORTS{ENTER}",0,200 'MOVE DOWN
SendString "CONCERTS{ENTER}",0,200 'MOVE DOWN
SendString "HOUSING{ENTER}",0,200 'MOVE DOWN
SendString "CARS{ENTER}",0,200 'MOVE DOWN
SendString "INSURANCE{ENTER}",0,200 'MOVE DOWN
SendString "UTILITIES{ENTER}",0,200 'MOVE DOWN
SendString "ELECTRIC{ENTER}",0,200 'MOVE DOWN
SendString "SAVING{ENTER}",0,200 'MOVE DOWN
SendString "RESTURANTS{ENTER}",0,200 'MOVE DOWN
SendString "FUEL{ENTER}",0,200 'MOVE DOWN
SendString "TOTAL",0,200 'MOVE DOWN
end function
function monthHeaders()
SendString "{TAB}",0,200 'MOVE RIGHT
SendString "JAN{TAB}",0,200 'MOVE RIGHT
SendString "FEB{TAB}",0,200 'MOVE RIGHT
SendString "MAR{TAB}",0,200 'MOVE RIGHT
SendString "APR{TAB}",0,200 'MOVE RIGHT
SendString "MAY{TAB}",0,200 'MOVE RIGHT
SendString "JUN{TAB}",0,200 'MOVE RIGHT
SendString "JUL{TAB}",0,200 'MOVE RIGHT
SendString "AUG{TAB}",0,200 'MOVE RIGHT
SendString "SEP{TAB}",0,200 'MOVE RIGHT
SendString "OCT{TAB}",0,200 'MOVE RIGHT
SendString "NOV{TAB}",0,200 'MOVE RIGHT
SendString "DEC",0,200 'MOVE RIGHT
end function
function topLeftSpreadSheet() 'top left on spreadsheet
SendString "{CTRL_DOWN}{HOME}{CTRL_UP}",0,1500 'TOP OF SPREADSHEET
end function