'---Script created on 07-20-2023 21:51:17 by G Robinson
' uses sendstring to fill in LibreOffice Calc spreadsheet
uses "UI"
Dim hInstance as DWord
Dim hWnd as Long
'works with LibreOffice Calc
'have a clear spreadsheet page for program
'have spreadsheet active (does not have to be top level window)
hWnd = Win_FindByTitle("...LibreOffice Calc")
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
LocationPosition("a1")
sleep 500
'fill in month headers
monthHeaders()
LocationPosition("a1")
'column headers
columnHeaders()
LocationPosition("a1")
'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 3
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
LocationPosition("b14")
sumsColumns()
LocationPosition("e2")
sumsRows()
SLEEP 1000
'area =top-left:bottom-right
SelectArea("b2:e14")
formatRowColumn()
end if
BEEP
'area =top-left:bottom-right
function SelectArea( Area as string) ' Area a2:d4
dim BegPos as string, EndPos as String
dim letterpos as string ,numPos as string
dim letterpos2 as string ,numPos2 as string
Local position, length As Long
local rws as long,cols as long
local rws2 as long,cols2 as long
local totalrows as long , totalcolumns as long
if instr(area,":") Then
begPos=mid$(area,1,instr(area,":") -1)
endpos=Mid$(area,instr(area,":") + 1)
'get letter part of string
RegExpr$("([A-Za-a]+)", begPos, 1, position, length)
letterpos = Mid$(begPos, position, length)
'get number part of string
RegExpr$("([0-9]+)",begPos, 1, position, length)
numpos = Mid$(begPos, position, length)
'Position cursor
SendString "{F5}",0,700
SendString "{DELETE}",0,700
SendString letterpos + "{tab}{tab}",0,700
SendString numpos+"{ENTER}",0,700
SendString "{F5}",0,700
'get letter part of string
RegExpr$("([A-Za-a]+)", endPos, 1, position, length)
letterpos2 = Mid$(endPos, position, length)
'get number part of string
RegExpr$("([0-9]+)",endPos, 1, position, length)
numpos2 = Mid$(endPos, position, length)
'this only work with letterpos being single letters not AF AH
'double letters formula = (firstLetterPos * 26) + (secondletterPos)
letterpos = lcase$( letterpos )
letterpos2 = lcase$( letterpos2 )
cols=instr("abcdefghijklmnopqrstuvwxyz",letterpos)
cols2=instr("abcdefghijklmnopqrstuvwxyz",letterpos2)
totalColumns = cols2 - cols
totalRows = val(numPos2)-val(numPos)
IF totalRows>1 THEN
FOR i=1 to totalRows
sendstring "{SHIFT_DOWN}{DOWN}{SHIFT_UP}",0,400
next
END IF
IF totalColumns >1 THEN
FOR i=1 to totalColumns
sendstring "{SHIFT_DOWN}{RIGHT}{SHIFT_UP}",0,400
next
END IF
end if
end function
function LocationPosition(StartingPosition as string )
dim letterpos as string ,numPos as string
Local position, length As Long
'get letter part of string
RegExpr$("([A-Za-a]+)", StartingPosition, 1, position, length)
letterpos = Mid$(StartingPosition, position, length)
'get number part of string
RegExpr$("([0-9]+)",StartingPosition, 1, position, length)
numpos = Mid$(StartingPosition, position, length)
SendString "{F5}",0,700
SendString "{DELETE}",0,700
SendString letterpos + "{tab}{tab}",0,700
SendString numpos+"{ENTER}",0,700
SendString "{F5}",0,700
end Function
function formatRowColumn()
'FORMAT THE SELECTED COLUMN AND ROW
sendstring "{CTRL_DOWN}m{CTRL_UP}",0,500
sendstring "{CTRL_DOWN}{SHIFT_DOWN}4{SHIFT_UP}{CTRL_UP}",0,500
sendstring "{RIGHT}",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:D2){ENTER}",0,200
SendString "=SUM(B3:D3){ENTER}",0,200
SendString "=SUM(B4:D4){ENTER}",0,200
SendString "=SUM(B5:D5){ENTER}",0,200
SendString "=SUM(B6:D6){ENTER}",0,200
SendString "=SUM(B7:D7){ENTER}",0,200
SendString "=SUM(B8:D8){ENTER}",0,200
SendString "=SUM(B9:D9){ENTER}",0,200
SendString "=SUM(B10:D10){ENTER}",0,200
SendString "=SUM(B11:D11){ENTER}",0,200
SendString "=SUM(B12:D12){ENTER}",0,200
SendString "=SUM(B13:D13){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 "RESTAURANTS{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
Members who have read this thread: 0
Reply With Quote
Bookmarks