- 構文
- Excel = ExcelBoot( [path] )
- 引数
- path
- 開くファイルのパス名
- 戻値
- Excelオブジェクト
プログラム
//////////////////////////////////////////////////
// 【引数】
// path : 開くファイルのパス名
// 【戻値】
// Excelオブジェクト
//////////////////////////////////////////////////
FUNCTION ExcelBoot(path = "")
DIM Excel = CREATEOLEOBJ("Excel.Application")
Excel.Visible = TRUE
IFB path = "" THEN
Excel.Workbooks.Add
ELSE
DIM FSO = CREATEOLEOBJ("Scripting.FileSystemObject")
IFB FSO.GetParentFolderName(path) = "" THEN
path = GET_CUR_DIR + "\" + path
ENDIF
Excel.Workbooks.Open(path)
ENDIF
RESULT = Excel
FEND
プログラム実行例
相対座標
DIM Excel = ExcelBoot()
WITH Excel.Range("E5")
FOR r = -3 TO 3
FOR c = -3 TO 3
DIM Range = .Offset(r, c)
Range.Value = "(" + r + ", " + c + ")"
IF r = 0 AND c = 0 THEN Range.Value = "基準セル"
NEXT
NEXT
ENDWITH
//////////////////////////////////////////////////
// 【引数】
// path : 開くファイルのパス名
// 【戻値】
// Excelオブジェクト
//////////////////////////////////////////////////
FUNCTION ExcelBoot(path = "")
DIM Excel = CREATEOLEOBJ("Excel.Application")
Excel.Visible = TRUE
IFB path = "" THEN
Excel.Workbooks.Add
ELSE
DIM FSO = CREATEOLEOBJ("Scripting.FileSystemObject")
IFB FSO.GetParentFolderName(path) = "" THEN
path = GET_CUR_DIR + "\" + path
ENDIF
Excel.Workbooks.Open(path)
ENDIF
RESULT = Excel
FEND
- 結果
2軸のグラフを作成する
CONST xlLine = 4
CONST xlColumnClustered = 51
CONST xlMarkerStyleCircle = 8
CONST xlValue = 2
CONST xlSecondary = 2
DIM Excel = ExcelBoot("東京の気温と降水量(2019年).xlsx")
DIM SheetName = "Sheet1"
DIM Charts = Excel.Charts.Add
DIM Series
Charts.ChartArea.ClearContents
Series = Charts.SeriesCollection.NewSeries
WITH Series
WITH Charts.Axes(xlValue)
.HasTitle = TRUE
.AxisTitle.Caption = "気温[℃]"
ENDWITH
.ChartType = xlLine
.MarkerStyle = xlMarkerStyleCircle
.XValues = Excel.Worksheets(SheetName).Range("B5:B16")
.Values = Excel.Worksheets(SheetName).Range("C5:C16")
.Name = Excel.Worksheets(SheetName).Range("C4")
.Format.Line.ForeColor.RGB = 255
.MarkerBackgroundColor = 255
ENDWITH
Series = Charts.SeriesCollection.NewSeries
WITH Series
.ChartType = xlLine
.MarkerStyle = xlMarkerStyleCircle
.XValues = Excel.Worksheets(SheetName).Range("B5:B16")
.Values = Excel.Worksheets(SheetName).Range("D5:D16")
.Name = Excel.Worksheets(SheetName).Range("D4")
.Format.Line.ForeColor.RGB = 16711680
.MarkerBackgroundColor = 16711680
ENDWITH
Series = Charts.SeriesCollection.NewSeries
WITH Series
TEXTBLOCK
WITH Charts.Axes(xlValue)
.HasTitle = TRUE
.AxisTitle.Caption = "降水量[mm]"
ENDWITH
ENDTEXTBLOCK
.ChartType = xlColumnClustered
.XValues = Excel.Worksheets(SheetName).Range("B5:B16")
.Values = Excel.Worksheets(SheetName).Range("E5:E16")
.Name = Excel.Worksheets(SheetName).Range("E4")
.Format.Line.ForeColor.RGB = 10341600
.AxisGroup = xlSecondary
ENDWITH
//////////////////////////////////////////////////
// 【引数】
// path : 開くファイルのパス名
// 【戻値】
// Excelオブジェクト
//////////////////////////////////////////////////
FUNCTION ExcelBoot(path = "")
DIM Excel = CREATEOLEOBJ("Excel.Application")
Excel.Visible = TRUE
IFB path = "" THEN
Excel.Workbooks.Add
ELSE
DIM FSO = CREATEOLEOBJ("Scripting.FileSystemObject")
IFB FSO.GetParentFolderName(path) = "" THEN
path = GET_CUR_DIR + "\" + path
ENDIF
Excel.Workbooks.Open(path)
ENDIF
RESULT = Excel
FEND
//////////////////////////////////////////////////
// 【引数】
// シリアル値 : 時間を表すシリアル値を指定
// 【戻値】
//
//////////////////////////////////////////////////
FUNCTION Second(serial)
RESULT = REPLACE(FORMAT(INT(serial * 86400) MOD 60, 2), " ", "0")
FEND
指定したシートが存在するか
Sheet1〜Sheet5のシートが存在するか調べます。
存在しなければ「Excel.Sheets(SheetName)」でエラーを出すので、存在有無を確認できます。
DIM Excel = ExcelBoot()
FOR n = 1 TO 5
TRY
DIM SheetName ="Sheet" + n
DIM Worksheet = Excel.Sheets(SheetName)
PRINT SheetName + "は存在します"
EXCEPT
PRINT SheetName + "は存在しません"
ENDTRY
NEXT
//////////////////////////////////////////////////
// 【引数】
// path : 開くファイルのパス名
// 【戻値】
// Excelオブジェクト
//////////////////////////////////////////////////
FUNCTION ExcelBoot(path = "")
DIM Excel = CREATEOLEOBJ("Excel.Application")
Excel.Visible = TRUE
IFB path = "" THEN
Excel.Workbooks.Add
ELSE
DIM FSO = CREATEOLEOBJ("Scripting.FileSystemObject")
IFB FSO.GetParentFolderName(path) = "" THEN
path = GET_CUR_DIR + "\" + path
ENDIF
Excel.Workbooks.Open(path)
ENDIF
RESULT = Excel
FEND
- 結果
- Sheet1は存在します
Sheet2は存在します
Sheet3は存在します
Sheet4は存在しません
Sheet5は存在しません