SendString using Excel spreadsheet

[Gary]

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