Scripts/JMacrosXLS

VBA macros necessary to use J OLE facilities as detailed here.

' J OLE client macros
'
' used by script: ~system\examples\ole\excel\xlutil.ijs
' see this script for more information
'
' Macros:
'   jread, jreadr     read cell, read range
'   jwrite, jwriter   write cell, write range
'   jsetchart         set chart data
'   jexit             exit Excel (prompts for save)
'   jquit             quit Excel (no save)
'
'   jinfo             display info msg box (useful for testing)
'
' Examples:
' read from book1, sheet1, the 2 by 3 range starting at 1 1:
'   xlreadr 'book1 sheet1 1 1 2 3'
'
' write to cell 2, 2 value 123:
'   123 xlwrite 'book1 sheet1 2 2'
'
' write 2 by 3 range starting at 1 1:
'   data xlwriter 'book1 sheet1 1 1 2 3'
'
' set chart source:
'   xlsetchart 'chart1 sheet1!A3:C8'
'
' Notes:
' Test a macro in Excel before trying it from J.
' wd'qer' reporting macro not found is a common problem.
'
' All macros in a module must be successfully compiled
' before any macro will be found. A compilation failure
' for a J call only reports macro not found. Running
' the macro in Excel reports the compilation problem.
'
' All macros should be defined as Function as String

' jread book sheet row cell
Function jread(b, s, r As Long, c As Long) As String
jread = Workbooks(b).Worksheets(s).Cells(r, c).Value
End Function

' jreadr book sheet row cell rowsize colsize
Function jreadr(b, s, _
    r As Long, c As Long, w As Long, h As Long) As String
With Workbooks(b).Worksheets(s)
    .Range(.Cells(r, c), .Cells((r + w - 1), (c + h - 1))).Copy
End With
jreadr = "1"
End Function

' jwrite book sheet row cell value
Function jwrite(b, s, r As Long, c As Long, d) As String
Workbooks(b).Worksheets(s).Cells(r, c).Value = d
jwrite = "1"
End Function

' jwriter book sheet row cell rowsize colsize values
Function jwriter(b, s, _
    r As Long, c As Long, w As Long, h As Long) As String
With Workbooks(b).Worksheets(s)
    .Range(.Cells(r, c), .Cells((r + w - 1), (c + h - 1))).PasteSpecial
End With
jwriter = "1"
End Function

' jsetchart chart range
Function jsetchart(c, r As String) As String
Charts(c).ChartWizard Source:=Range(r)
jsetchart = "1"
End Function

' jquit
' note Application.DisplayAlerts=False does not work under OLE
' Workbooks must be closed one at a time
Function jquit() As String
jquit = "1"
For Each w In Application.Workbooks
  w.Saved = True
Next w
Application.Quit
End Function

' jexit
' uses Save, however SaveAs should be used if saved for the first time
Function jexit() As String
jexit = "1"
For Each w In Application.Workbooks
  w.Save
Next w
Application.Quit
End Function

Function jinfo(s As String) As String
MsgBox s
jinfo = "1"
End Function

' jusedrange return range of cells actually used on sheet.
' for example: jusedrange book sheet
Function jusedrange(b, s) As String
jusedrange = Workbooks(b).Worksheets(s).UsedRange.Address
End Function