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