Scripts/JMacrosXLS
< Scripts
Jump to navigation
Jump to search
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