Sendstring with LibreOffice Calc

[Gary]

Special note:
LibreOffice version 7.4.3.2
-Do not like subscription based software. (99.00 yearly excel)
-LibreOffice is free.





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