Sendstrings with Excel Two

[Gary]

Made with Office16 Excel

Sendstring with Excel making spreadsheet:
-borders around cells
-colors for range of cell
-format currency for range of cells
-sum totals for columns and rows




'---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

'COLOR SIDE HEADER
sleep 500
gotoCell("a1:a13")
sleep 300
LightBrown()

'COLOR TOP HEADER
sleep 500
gotoCell("b1:m1")
sleep 300
LightBrown()

'COLOR TOTALS FOR EACH COLUMN
sleep 100
gotoCell("a14:m14")
DarkBlue()

'get total for each row
sumsRows()

'COLOR TOTALS FOR EACH ROW
sleep 100
gotoCell("N1:N13")
DarkBlue()

'change to currency format
Sleep 100
gotoCell("b2:n14")
sendstring "{CTRL_DOWN}{SHIFT_DOWN}${SHIFT_UP}{CTRL_UP}",200,200


'put border around cells
Sleep 100
gotoCell("b2:n14")
Border()


end if
BEEP


FUNCTION Border()
sendstring "{ALT_DOWN}{ALT_UP}",500,500
sleep 100
sendstring "h",0,100
sleep 100
sendstring "b",0,100
sleep 100
sendstring "a",0,00
sleep 100
end function




FUNCTION LightBrown()
sendstring "{ALT_DOWN}{ALT_UP}",500,500
sleep 100
sendstring "h",0,100
sleep 100
sendstring "h",0,100
sendstring "{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{ENTER}",0,500
end function


FUNCTION DarkBlue()
sendstring "{ALT_DOWN}{ALT_UP}",500,500
sleep 100
sendstring "h",0,100
sleep 100
sendstring "h",0,100
sendstring "{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{RIGHT}{ENTER}",0,500
end function




function gotoCell(cell as string )

sendstring "{F5}",400,500
sendstring cell+"{ENTER}",400,500
sleep 500


end function




FUNCTION fillColor(txt as string)
'sendstring "{ALT_DOWN}{SHIFT_DOWN}hh{SHIFT_UP}{ALT_UP}",0,500
sendstring "{ALT_DOWN}H{ALT_UP}"+txt,0,500
end function


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:D13){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 sumsRows()
DIM I AS LONG
'positioning cursor to HOME position
gotoCell("n2")

'total all columns
SendString "=SUM(B2:M2){ENTER}",0,200
SendString "=SUM(B3:M3){ENTER}",0,200
SendString "=SUM(B4:M4){ENTER}",0,200
SendString "=SUM(B5:M5){ENTER}",0,200
SendString "=SUM(B6:M6){ENTER}",0,200
SendString "=SUM(B7:M7){ENTER}",0,200
SendString "=SUM(B8:M8){ENTER}",0,200
SendString "=SUM(B9:M9){ENTER}",0,200
SendString "=SUM(B10:M10){ENTER}",0,200
SendString "=SUM(B11:M11){ENTER}",0,200
SendString "=SUM(B12:M12){ENTER}",0,200
SendString "=SUM(B13:M13){ENTER}",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{TAB}",0,200 'MOVE RIGHT
SendString "TOTAL",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