ExcelBoot

構文
  1. 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は存在しません