Sendstring with LibreOffice Calc

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

Code:

---


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

---