Last active
May 4, 2024 15:00
-
-
Save feo52/4ed4632df7c91f32cfa7975b55a87f55 to your computer and use it in GitHub Desktop.
Python pywin32(win32com) : Excel
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 14159 | 26535 | 89793 | 23846 | 26433 | 83279 | 5028 | 841971 | 69399 | 37510 | |
|---|---|---|---|---|---|---|---|---|---|---|
| 58209 | 74944 | 59230 | 78164 | 06286 | 20899 | 8628 | 034825 | 34211 | 70679 | |
| 82148 | 08651 | 32823 | 06647 | 09384 | 46095 | 5058 | 223172 | 53594 | 08128 | |
| 48111 | 74502 | 84102 | 70193 | 85211 | 05559 | 6446 | 229489 | 54930 | 38196 | |
| 44288 | 10975 | 66593 | 34461 | 28475 | 64823 | 3786 | 783165 | 27120 | 19091 | |
| 45648 | 56692 | 34603 | 48610 | 45432 | 66482 | 1339 | 360726 | 02491 | 41273 | |
| 72458 | 70066 | 06315 | 58817 | 48815 | 20920 | 9628 | 292540 | 91715 | 36436 | |
| 78925 | 90360 | 01133 | 05305 | 48820 | 46652 | 1384 | 146951 | 94151 | 16094 | |
| 33057 | 27036 | 57595 | 91953 | 09218 | 61173 | 8193 | 261179 | 31051 | 18548 | |
| 07446 | 23799 | 62749 | 56735 | 18857 | 52724 | 8912 | 279381 | 83011 | 94912 | |
| 98336 | 73362 | 44065 | 66430 | 86021 | 39494 | 6395 | 224737 | 19070 | 21798 | |
| 60943 | 70277 | 05392 | 17176 | 29317 | 67523 | 8467 | 481846 | 76694 | 05132 | |
| 00056 | 81271 | 45263 | 56082 | 77857 | 71342 | 7577 | 896091 | 73637 | 17872 | |
| 14684 | 40901 | 22495 | 34301 | 46549 | 58537 | 1050 | 792279 | 68925 | 89235 | |
| 42019 | 95611 | 21290 | 21960 | 86403 | 44181 | 5981 | 362977 | 47713 | 09960 | |
| 51870 | 72113 | 49999 | 99837 | 29780 | 49951 | 0597 | 317328 | 16096 | 31859 | |
| 50244 | 59455 | 34690 | 83026 | 42522 | 30825 | 3344 | 685035 | 26193 | 11881 | |
| 71010 | 00313 | 78387 | 52886 | 58753 | 32083 | 8142 | 061717 | 76691 | 47303 | |
| 59825 | 34904 | 28755 | 46873 | 11595 | 62863 | 8823 | 537875 | 93751 | 95778 | |
| 18577 | 80532 | 17122 | 68066 | 13001 | 92787 | 6611 | 195909 | 21642 | 01989 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # coding:utf-8 | |
| import os | |
| import win32com.client | |
| import win32con | |
| import win32gui | |
| def main(): | |
| # ------------------------------------------------------------------ | |
| # Excelの定数を設定 | |
| # ------------------------------------------------------------------ | |
| # https://excel-ubara.com/EXCEL/EXCEL905.html | |
| # https://docs.microsoft.com/en-us/office/vba/api/excel(enumerations) | |
| # https://docs.microsoft.com/ja-jp/office/vba/api/excel(enumerations) | |
| # ------------------------------------------------------------------ | |
| # Excelの定数を取得する方法もあるようです。 | |
| # https://stackoverflow.com/questions/28264548/ | |
| # https://stackoverflow.com/questions/75154736/ | |
| # https://stackoverflow.com/questions/50127959/ | |
| # https://stackoverflow.com/questions/48257308/ | |
| # https://wacky.hatenadiary.com/entry/20091011/1255240572 | |
| # win32com.client.gencache.EnsureDispatch("Excel.Application") | |
| # ------------------------------------------------------------------ | |
| # Excel Enum Constants | |
| # ------------------------------------------------------------------ | |
| xlAbove = 0 | |
| xlBelow = 1 | |
| xlSolid = 1 | |
| xlFirst = 0 | |
| xlLast = 1 | |
| xlLastCell = 11 | |
| xlTopToBottom = 1 | |
| xlLeftToRight = 2 | |
| xlGeneral = 1 | |
| xlAutomatic = -4105 | |
| xlFormats = -4122 | |
| xlNone = -4142 | |
| xlCenter = -4108 | |
| xlDistributed = -4117 | |
| xlJustify = -4130 | |
| xlBottom = -4107 | |
| xlLeft = -4131 | |
| xlRight = -4152 | |
| xlTop = -4160 | |
| xlRTL = -5004 | |
| xlLTR = -5003 | |
| xlContext = -5002 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XLBorderWeight | |
| # ------------------------------------------------------------------ | |
| xlHairline = 1 | |
| xlThin = 2 | |
| xlThick = 4 | |
| xlMedium = -4138 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XLBordersIndex | |
| # ------------------------------------------------------------------ | |
| xlDiagonalDown = 5 | |
| xlDiagonalUp = 6 | |
| xlEdgeLeft = 7 | |
| xlEdgeTop = 8 | |
| xlEdgeBottom = 9 | |
| xlEdgeRight = 10 | |
| xlInsideHorizontal = 12 | |
| xlInsideVertical = 11 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlAutoFillType | |
| # ------------------------------------------------------------------ | |
| xlFillDefault = 0 | |
| xlFillCopy = 1 | |
| xlFillSeries = 2 | |
| xlFillFormats = 3 | |
| xlFillValues = 4 | |
| xlFillDays = 5 | |
| xlFillWeekdays = 6 | |
| xlFillMonths = 7 | |
| xlFillYears = 8 | |
| xlLinearTrend = 9 | |
| xlGrowthTrend = 10 | |
| xlFlashFill = 11 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlAutoFilterOperator | |
| # ------------------------------------------------------------------ | |
| xlAnd = 1 | |
| xlOr = 2 | |
| xlTop10Items = 3 | |
| xlBottom10Items = 4 | |
| xlTop10Percent = 5 | |
| xlBottom10Percent = 6 | |
| xlFilterValues = 7 | |
| xlFilterCellColor = 8 | |
| xlFilterFontColor = 9 | |
| xlFilterIcon = 10 | |
| xlFilterDynamic = 11 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlCVError | |
| # ------------------------------------------------------------------ | |
| xlErrDiv0 = 2007 | |
| xlErrNA = 2042 | |
| xlErrName = 2029 | |
| xlErrNull = 2000 | |
| xlErrNum = 2036 | |
| xlErrRef = 2023 | |
| xlErrValue = 2015 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlCVError | |
| # ------------------------------------------------------------------ | |
| xlErrSpill = 2045 | |
| xlErrConnect = 2046 | |
| xlErrBlocked = 2047 | |
| xlErrUnknown = 2048 | |
| xlErrField = 2049 | |
| xlErrCalc = 2050 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlCellType | |
| # ------------------------------------------------------------------ | |
| xlCellTypeConstants = 2 | |
| xlCellTypeBlanks = 4 | |
| xlCellTypeLastCell = 11 | |
| xlCellTypeVisible = 12 | |
| xlCellTypeFormulas = -4123 | |
| xlCellTypeComments = -4144 | |
| xlCellTypeAllFormatConditions = -4172 | |
| xlCellTypeSameFormatConditions = -4173 | |
| xlCellTypeAllValidation = -4174 | |
| xlCellTypeSameValidation = -4175 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlColorIndex | |
| # ------------------------------------------------------------------ | |
| xlColorIndexAutomatic = -4105 | |
| xlColorIndexNone = -4142 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlCutCopyMode | |
| # ------------------------------------------------------------------ | |
| xlCopy = 1 | |
| xlCut = 2 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlDeleteShiftDirection | |
| # Excel Enum XlInsertShiftDirection | |
| # ------------------------------------------------------------------ | |
| xlShiftUp = -4162 | |
| xlShiftDown = -4121 | |
| xlShiftToLeft = -4159 | |
| xlShiftToRight = -4161 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlDirection | |
| # ------------------------------------------------------------------ | |
| xlUp = -4162 | |
| xlDown = -4121 | |
| xlToLeft = -4159 | |
| xlToRight = -4161 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlFileFormat | |
| # ------------------------------------------------------------------ | |
| xlCSV = 6 | |
| xlHtml = 44 | |
| xlWorkbookDefault = 51 | |
| xlOpenXMLWorkbook = 51 | |
| xlOpenXMLWorkbookMacroEnabled = 52 | |
| xlWorkbookNormal = -4143 | |
| xlCurrentPlatformText = -4158 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlFindLookIn | |
| # ------------------------------------------------------------------ | |
| xlComments = -4144 | |
| xlCommentsThreaded = -4184 | |
| xlFormulas = -4123 | |
| xlValues = -4163 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlFixedFormatQuality | |
| # ------------------------------------------------------------------ | |
| xlQualityStandard = 0 | |
| xlQualityMinimum = 1 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlFixedFormatType | |
| # ------------------------------------------------------------------ | |
| xlTypePDF = 0 | |
| xlTypeXPS = 1 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlLineStyle | |
| # ------------------------------------------------------------------ | |
| xlContinuous = 1 | |
| xlDashDot = 4 | |
| xlDashDotDot = 5 | |
| xlSlantDashDot = 13 | |
| xlDash = -4115 | |
| xldot = -4118 | |
| xlDouble = -4119 | |
| xlLineStyleNone = -4142 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlLookAt | |
| # ------------------------------------------------------------------ | |
| xlPart = 2 | |
| xlWhole = 1 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlOrientation | |
| # ------------------------------------------------------------------ | |
| xlHorizontal = -4128 | |
| xlVertical = -4166 | |
| xlDownward = -4170 | |
| xlUpward = -4171 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlPasteType | |
| # ------------------------------------------------------------------ | |
| xlPasteValues = -4163 | |
| xlPasteComments = -4144 | |
| xlPasteFormulas = -4123 | |
| xlPasteFormats = -4122 | |
| xlPasteAll = -4104 | |
| xlPasteValidation = 6 | |
| xlPasteAllExceptBorders = 7 | |
| xlPasteColumnWidths = 8 | |
| xlPasteFormulasAndNumberFormats = 11 | |
| xlPasteValuesAndNumberFormats = 12 | |
| xlPasteAllUsingSourceTheme = 13 | |
| xlPasteAllMergingConditionalFormats = 14 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlReferenceStyle | |
| # ------------------------------------------------------------------ | |
| xlA1 = 1 | |
| xlR1C1 = -4150 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlReferenceType | |
| # ------------------------------------------------------------------ | |
| xlAbsolute = 1 | |
| xlAbsRowRelColumn = 2 | |
| xlRelRowAbsColumn = 3 | |
| xlRelative = 4 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSearchDirection | |
| # ------------------------------------------------------------------ | |
| xlNext = 1 | |
| xlPrevious = 2 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSearchOrder | |
| # ------------------------------------------------------------------ | |
| xlByColumns = 2 | |
| xlByRows = 1 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSheetVisibility | |
| # ------------------------------------------------------------------ | |
| xlSheetVisible = -1 | |
| xlSheetHidden = 0 | |
| xlSheetVeryHidden = 2 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSortDataOption | |
| # ------------------------------------------------------------------ | |
| xlSortNormal = 0 | |
| xlSortTextAsNumbers = 1 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSortMethod | |
| # ------------------------------------------------------------------ | |
| xlPinYin = 1 | |
| xlStroke = 2 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSortOn | |
| # ------------------------------------------------------------------ | |
| xlSortOnValues = 0 | |
| xlSortOnCellColor = 1 | |
| xlSortOnFontColor = 2 | |
| xlSortOnIcon = 3 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSortOrder | |
| # ------------------------------------------------------------------ | |
| xlAscending = 1 | |
| xlDescending = 2 | |
| xlManual = -4135 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSortOrientation | |
| # ------------------------------------------------------------------ | |
| xlSortColumns = 1 | |
| xlSortRows = 2 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSortType | |
| # ------------------------------------------------------------------ | |
| xlSortValues = 1 | |
| xlSortLabels = 2 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSpecialCellsValue | |
| # ------------------------------------------------------------------ | |
| xlNumbers = 1 | |
| xlTextValues = 2 | |
| xlLogical = 4 | |
| xlErrors = 16 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlUnderlineStyle | |
| # ------------------------------------------------------------------ | |
| xlUnderlineStyleNone = -4142 | |
| xlUnderlineStyleDouble = -4119 | |
| xlUnderlineStyleSingle = 2 | |
| xlUnderlineStyleSingleAccounting = 4 | |
| xlUnderlineStyleDoubleAccounting = 5 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlYesNoGuess | |
| # ------------------------------------------------------------------ | |
| xlGuess = 0 | |
| xlYes = 1 | |
| xlNo = 2 | |
| # ------------------------------------------------------------------ | |
| # Excel起動 | |
| xlApp = win32com.client.Dispatch("Excel.Application") | |
| # https://stackoverflow.com/questions/2790825/ | |
| # ExcelのWindow最大化 | |
| win32gui.ShowWindow(xlApp.hwnd, win32con.SW_MAXIMIZE) | |
| # Excel表示 | |
| xlApp.Visible = 1 | |
| # Excelファイルオープン | |
| wb = xlApp.Workbooks.Open(f"{os.getcwd()}\\sample.csv") | |
| # Excelシートオブジェクト | |
| ws = wb.Worksheets(1) | |
| # ------------------------------------------------------------------ | |
| # 指定したシートを選択 | |
| # Select()の使用前にシートのActivate()が必要 | |
| ws.Activate() | |
| # ------------------------------------------------------------------ | |
| # A1セルを選択 | |
| ws.Range("A1").Select() | |
| # A1～B2を選択 | |
| ws.Range("A1:B2").Select() | |
| # A1とB2とC3とD4を選択 | |
| ws.Range("A1,B2,C3,D4").Select() | |
| # A1～B2とC3～D4を選択 | |
| ws.Range("A1:B2,C3:D4").Select() | |
| # ------------------------------------------------------------------ | |
| # A1セルを選択 | |
| ws.Cells(1, 1).Select() | |
| # A1～B2を選択 | |
| ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Select() | |
| # ------------------------------------------------------------------ | |
| # 全セルを選択 | |
| ws.Cells.Select() | |
| # ------------------------------------------------------------------ | |
| # 1～2行を選択 | |
| ws.Range("1:2").Select() | |
| # A～B列を選択 | |
| ws.Range("A:B").Select() | |
| # https://www.relief.jp/docs/excel-vba-difference-range-rows-columns.html | |
| # 1～2行を選択 | |
| ws.Rows("1:2").Select() | |
| # https://www.relief.jp/docs/excel-vba-difference-range-columns.html | |
| # A～B列を選択 | |
| ws.Columns("A:B").Select() | |
| # ------------------------------------------------------------------ | |
| # 指定範囲の先頭行を選択 | |
| ws.Range("A1:D4").Rows(1).Select() | |
| # 指定範囲の先頭列を選択 | |
| ws.Range("A1:D4").Columns(1).Select() | |
| # ------------------------------------------------------------------ | |
| # (A1セル基準で)行全体を選択 | |
| ws.Range("A1").EntireRow.Select() | |
| # (A1セル基準で)列全体を選択 | |
| ws.Range("A1").EntireColumn.Select() | |
| # ------------------------------------------------------------------ | |
| # (A1セル基準で)最終列までの行を選択 | |
| ws.Range(ws.Range("A1"), ws.Cells(1, ws.Columns.Count).End(xlToLeft)).Select() | |
| # (A1セル基準で)最終行までの列を選択 | |
| ws.Range(ws.Range("A1"), ws.Cells(ws.Rows.Count, 1).End(xlUp)).Select() | |
| # ------------------------------------------------------------------ | |
| # (A1セル基準で)現在の領域を選択 | |
| ws.Range("A1").CurrentRegion.Select() | |
| # (現行シートの)使用中領域を選択 | |
| ws.UsedRange.Select() | |
| # (A1セル基準で)最終右下セル選択 | |
| ws.Range("A1").SpecialCells(xlLastCell).Select() | |
| # (A1セル基準で)可視状態セル選択 | |
| ws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Select() | |
| # ------------------------------------------------------------------ | |
| # 指定範囲の参照範囲の文字列を行列両方を絶対参照で取得 | |
| a = ws.UsedRange.Address | |
| print(a) | |
| # 指定範囲の参照範囲の文字列を行列両方を絶対参照で取得 | |
| a = ws.UsedRange.GetAddress() | |
| print(a) | |
| # 指定範囲の参照範囲の文字列を行列両方を絶対参照で取得 | |
| a = ws.UsedRange.GetAddress(True, True) | |
| print(a) | |
| # 指定範囲の参照範囲の文字列を行列両方を相対参照で取得 | |
| a = ws.UsedRange.GetAddress(False, False) | |
| print(a) | |
| # 指定範囲の参照範囲の文字列を行列両方を相対参照のA1形式で取得 | |
| a = ws.UsedRange.GetAddress(False, False, xlA1) | |
| print(a) | |
| # 指定範囲の参照範囲の文字列を行列両方を相対参照のR1C1形式で取得 | |
| a = ws.UsedRange.GetAddress(False, False, xlR1C1) | |
| print(a) | |
| # 指定範囲の参照範囲の文字列を行列両方を相対参照のxlA1形式の外部参照で取得 | |
| a = ws.UsedRange.GetAddress(False, False, xlA1, True) | |
| print(a) | |
| # ------------------------------------------------------------------ | |
| # 数式的な文字列のセル参照をR1C1形式からA1形式に変更 | |
| a1 = ws.UsedRange.GetAddress(False, False, xlR1C1, False) | |
| a2 = ws.Application.ConvertFormula(a1, xlR1C1, xlA1) | |
| print(a1) | |
| print(a2) | |
| # 数式的な文字列のセル参照をA1形式からR1C1形式に変更 | |
| a1 = ws.UsedRange.GetAddress(False, False, xlA1, False) | |
| a2 = ws.Application.ConvertFormula(a1, xlA1, xlR1C1) | |
| print(a1) | |
| print(a2) | |
| # 数式的な文字列のセル参照を相対参照のA1形式から絶対参照のA1形式に変更 | |
| a1 = ws.UsedRange.GetAddress(False, False, xlA1, False) | |
| a2 = ws.Application.ConvertFormula(a1, xlA1, xlA1, xlAbsolute) | |
| print(a1) | |
| print(a2) | |
| # 数式的な文字列のセル参照を絶対参照のA1形式から相対参照のA1形式に変更 | |
| a1 = ws.UsedRange.GetAddress(True, True, xlA1, False) | |
| a2 = ws.Application.ConvertFormula(a1, xlA1, xlA1, xlRelative) | |
| print(a1) | |
| print(a2) | |
| # 数式的な文字列の外部参照のセル参照をA1形式からR1C1形式に変更 | |
| a1 = ws.UsedRange.GetAddress(False, False, xlA1, True) | |
| a2 = ws.Application.ConvertFormula(a1, xlA1, xlR1C1) | |
| print(a1) | |
| print(a2) | |
| # 数式的な文字列の外部参照のセル参照をR1C1形式からA1形式に変更 | |
| a1 = ws.UsedRange.GetAddress(False, False, xlR1C1, True) | |
| a2 = ws.Application.ConvertFormula(a1, xlR1C1, xlA1) | |
| print(a1) | |
| print(a2) | |
| # 数式的な文字列の外部参照のセル参照を相対参照のR1C1形式から絶対参照のA1形式に変更 | |
| a1 = ws.UsedRange.GetAddress(False, False, xlR1C1, True) | |
| a2 = ws.Application.ConvertFormula(a1, xlR1C1, xlA1, xlAbsolute) | |
| print(a1) | |
| print(a2) | |
| # 数式的な文字列の外部参照のセル参照を絶対参照のR1C1形式から相対参照のA1形式に変更 | |
| a1 = ws.UsedRange.GetAddress(True, True, xlR1C1, True) | |
| a2 = ws.Application.ConvertFormula(a1, xlR1C1, xlA1, xlRelative) | |
| print(a1) | |
| print(a2) | |
| # ------------------------------------------------------------------ | |
| # A1形式の外部参照のセル参照の文字列でRangeを取得 | |
| # R1C1形式は使用不可 | |
| a = ws.UsedRange.GetAddress(False, False, xlA1, True) | |
| rg = ws.Evaluate(a) | |
| print(rg.Address) | |
| # A1形式の外部参照のセル参照の文字列でRangeを取得 | |
| # R1C1形式は使用不可 | |
| a = ws.UsedRange.GetAddress(False, False, xlA1, True) | |
| rg = ws.Application.Evaluate(a) | |
| print(rg.Address) | |
| # ------------------------------------------------------------------ | |
| # https://thecodingforums.com/threads/328174/ | |
| # Range.Offset()Property用GetOffset()Method | |
| # 指定範囲をOffset | |
| ws.Range("A1:D4").GetOffset(2, 2).Select() | |
| # Range.Offset()Property用GetOffset()Method | |
| # 指定範囲をOffset | |
| ws.Range("A1:D4").GetOffset(RowOffset=3, ColumnOffset=3).Select() | |
| # Range.Offset()Property用GetOffset()Method | |
| # 指定範囲を縦方向にOffset | |
| ws.Range("A1:D4").GetOffset(RowOffset=3).Select() | |
| # Range.Offset()Property用GetOffset()Method | |
| # 指定範囲を横方向にOffset | |
| ws.Range("A1:D4").GetOffset(RowOffset=0, ColumnOffset=3).Select() | |
| # ------------------------------------------------------------------ | |
| # https://stackoverflow.com/questions/63112880/ | |
| # Range.Resize()Property用GetResize()Method | |
| # 指定範囲をResize | |
| ws.Range("A1:H8").GetResize(2, 2).Select() | |
| # Range.Resize()Property用GetResize()Method | |
| # 指定範囲をResize | |
| ws.Range("A1:H8").GetResize(RowSize=3, ColumnSize=3).Select() | |
| # Range.Resize()Property用GetResize()Method | |
| # 指定範囲の縦方向をResize | |
| ws.Range("A1:H8").GetResize(RowSize=3).Select() | |
| # Range.Resize()Property用GetResize()Method | |
| # 指定範囲の横方向をResize | |
| ws.Range("A1:H8").GetResize(RowSize=ws.Range("A1:H8").Rows.Count, ColumnSize=3).Select() | |
| # ------------------------------------------------------------------ | |
| # 指定範囲から指定の文字列を含むセルを選択 | |
| rg = ws.UsedRange | |
| rg.find("56", rg.GetResize(1, 1), xlValues, xlPart, xlByRows, xlNext, True).Select() | |
| # 指定範囲から指定の文字列と合致のセルを選択 | |
| rg = ws.UsedRange | |
| rg.find("56", rg.GetResize(1, 1), xlValues, xlWhole, xlByRows, xlNext, True).Select() | |
| # ------------------------------------------------------------------ | |
| # 指定セルの行数を取得 | |
| n = ws.Range("A1").Row | |
| print(n) | |
| # 指定セルの列数を取得 | |
| n = ws.Range("A1").Column | |
| print(n) | |
| # 指定範囲に含まれる行数を取得 | |
| n = ws.Range("A1:D4").Rows.Count | |
| print(n) | |
| # 指定範囲に含まれる列数を取得 | |
| n = ws.Range("A1:D4").Columns.Count | |
| print(n) | |
| # (A1セル基準で)最終行数を取得 | |
| n = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row | |
| print(n) | |
| # (A1セル基準で)最終列数を取得 | |
| n = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column | |
| print(n) | |
| # ------------------------------------------------------------------ | |
| # https://binary-star.net/excel-vba-columnchange | |
| # 数字列を英字列に変換 | |
| a = (ws.Cells(1, 1).Address).split("$")[1] | |
| print(a) | |
| a = (ws.Cells(1, 2).Address).split("$")[1] | |
| print(a) | |
| a = (ws.Cells(1, 3).Address).split("$")[1] | |
| print(a) | |
| a = (ws.Cells(1, 27).Address).split("$")[1] | |
| print(a) | |
| a = (ws.Cells(1, 53).Address).split("$")[1] | |
| print(a) | |
| # 英字列を数字列に変換 | |
| n = ws.Cells(1, "A").Column | |
| print(n) | |
| n = ws.Cells(1, "B").Column | |
| print(n) | |
| n = ws.Cells(1, "C").Column | |
| print(n) | |
| n = ws.Cells(1, "AA").Column | |
| print(n) | |
| n = ws.Cells(1, "BA").Column | |
| print(n) | |
| # ------------------------------------------------------------------ | |
| # A1セルに値を設定 | |
| ws.Range("A1").Value = 99999 | |
| # A1セルに数式を設定 | |
| ws.Range("A1").Formula = "=(3.14159-3)*100000" | |
| # ------------------------------------------------------------------ | |
| # A1セルの表示形式を設定 | |
| ws.Range("A1").NumberFormatLocal = "0.00" | |
| # ------------------------------------------------------------------ | |
| # A1セルの配置の縦位置を設定 | |
| ws.Range("A1").VerticalAlignment = xlCenter | |
| # A1セルの配置の横位置を設定 | |
| ws.Range("A1").HorizontalAlignment = xlCenter | |
| # A1セルの文字列の方向を設定 | |
| ws.Range("A1").Orientation = xlUpward | |
| # A1セルの文字列の方向を設定 | |
| ws.Range("A1").Orientation = 45 | |
| # A1セルの文字列の方向を設定 | |
| ws.Range("A1").Orientation = 0 | |
| # ------------------------------------------------------------------ | |
| # A1セルにフォントを設定 | |
| ws.Range("A1").Font.Name = "Yu Gothic UI" | |
| # A1セルのフォントのサイズを設定 | |
| ws.Range("A1").Font.Size = 12 | |
| # A1セルのフォントを太字に設定 | |
| ws.Range("A1").Font.Bold = True | |
| # A1セルのフォントを斜体に設定 | |
| ws.Range("A1").Font.Italic = True | |
| # A1セルのフォントに下線を設定 | |
| ws.Range("A1").Font.Underline = xlUnderlineStyleSingle | |
| # A1セルのフォントに取消線を設定 | |
| ws.Range("A1").Font.Strikethrough = True | |
| # ------------------------------------------------------------------ | |
| # A1セルの文字の色を設定(R+Gx256+Bx256x256) | |
| ws.Range("A1").Font.Color = 255 + 0 * 256 + 0 * 256 * 256 | |
| # A1セルの文字の色を設定(指定順序BGR) | |
| ws.Range("A1").Font.Color = int("FF0000", 16) | |
| # A1セルの文字の色を設定(指定順序RGB) | |
| ws.Range("A1").Font.Color = int("".join(list(reversed(["FF0000"[i : i + 2] for i in range(0, 6, 2)]))), 16) | |
| # A1セルの文字の色を設定(デフォルト) | |
| ws.Range("A1").Font.ColorIndex = xlColorIndexAutomatic | |
| # ------------------------------------------------------------------ | |
| # A1セルの背景の色を設定(R+Gx256+Bx256x256) | |
| ws.Range("A1").Interior.Color = 255 + 255 * 256 + 0 * 256 * 256 | |
| # A1セルの背景の色を設定(指定順序BGR) | |
| ws.Range("A1").Interior.Color = int("FFFF00", 16) | |
| # A1セルの背景の色を設定(指定順序RGB) | |
| ws.Range("A1").Interior.Color = int("".join(list(reversed(["FFFF00"[i : i + 2] for i in range(0, 6, 2)]))), 16) | |
| # A1セルの背景の色を設定(デフォルト) | |
| ws.Range("A1").Interior.ColorIndex = xlColorIndexNone | |
| # ------------------------------------------------------------------ | |
| # A1セルに罫線を設定 | |
| ws.Range("A1").Borders.Color = int("".join(list(reversed(["FF0000"[i : i + 2] for i in range(0, 6, 2)]))), 16) | |
| ws.Range("A1").Borders.LineStyle = xlContinuous | |
| ws.Range("A1").Borders.Weight = xlMedium | |
| # A1セルの罫線を解除 | |
| ws.Range("A1").Borders.LineStyle = xlLineStyleNone | |
| # A1セルの下側に罫線を設定 | |
| ws.Range("A1").Borders(xlEdgeBottom).Color = int("".join(list(reversed(["FF0000"[i : i + 2] for i in range(0, 6, 2)]))), 16) | |
| ws.Range("A1").Borders(xlEdgeBottom).LineStyle = xlContinuous | |
| ws.Range("A1").Borders(xlEdgeBottom).Weight = xlMedium | |
| # A1セルの下側の罫線を解除 | |
| ws.Range("A1").Borders(xlEdgeBottom).LineStyle = xlLineStyleNone | |
| # ------------------------------------------------------------------ | |
| # A1セルの値や数式を消去 | |
| ws.Range("A1").ClearContents() | |
| # A1セルの書式を消去 | |
| ws.Range("A1").ClearFormats() | |
| # A1セルを消去 | |
| ws.Range("A1").Clear() | |
| # A1セルを削除 | |
| ws.Range("A1").Delete() | |
| # A1セルを削除(上方向にシフト) | |
| ws.Range("A1").Delete(xlShiftUp) | |
| # A1セルを削除(左方向にシフト) | |
| ws.Range("A1").Delete(xlShiftToLeft) | |
| # A1セルに挿入 | |
| ws.Range("A1").Insert() | |
| # A1セルに挿入(下方向にシフト) | |
| ws.Range("A1").Insert(xlShiftDown) | |
| # A1セルに挿入(右方向にシフト) | |
| ws.Range("A1").Insert(xlShiftToRight) | |
| # ------------------------------------------------------------------ | |
| # (A1行基準で)高さを設定 | |
| ws.Range("A1").RowHeight = 30 | |
| # (A1列基準で)横幅を設定 | |
| ws.Range("A1").ColumnWidth = 30 | |
| # (A1行基準で)高さを自動調整 | |
| ws.Range("A1").EntireRow.AutoFit() | |
| # (A1列基準で)横幅を自動調整 | |
| ws.Range("A1").EntireColumn.AutoFit() | |
| # ------------------------------------------------------------------ | |
| # 個別のグループ化の表示と非表示を行う方法 | |
| # https://vbabeginner.net/%e3%82%b0%e3%83%ab%e3%83%bc%e3%83%97%e5%8c%96%e3%81%ae%e8%a1%a8%e7%a4%ba%e3%81%a8%e9%9d%9e%e8%a1%a8%e7%a4%ba/ | |
| # 行のグループ化を設定 | |
| ws.Range("A1").EntireRow.Group() | |
| # 行のグループ化を非表示 | |
| ws.Outline.ShowLevels(RowLevels=1) | |
| # 行のグループ化を表示 | |
| ws.Outline.ShowLevels(RowLevels=8) | |
| # 行のグループ化を解除 | |
| ws.Range("A1").EntireRow.Ungroup() | |
| # 列のグループ化を設定 | |
| ws.Range("A1").EntireColumn.Group() | |
| # 列のグループ化を非表示 | |
| ws.Outline.ShowLevels(RowLevels=0, ColumnLevels=1) | |
| # 列のグループ化を表示 | |
| ws.Outline.ShowLevels(RowLevels=0, ColumnLevels=8) | |
| # 列のグループ化を解除 | |
| ws.Range("A1").EntireColumn.Ungroup() | |
| # ------------------------------------------------------------------ | |
| # A1セルをB1セルに複写 | |
| ws.Range("A1").Copy(ws.Range("B1")) | |
| # A1セルをSheet2のA1セルに複写 | |
| ws.Range("A1").Copy(wb.Worksheets("Sheet2").Range("A1")) | |
| # A1セル基準の現在の領域をSheet2のA1セル基準で複写 | |
| ws.Range("A1").CurrentRegion.Copy(wb.Worksheets("Sheet2").Range("A1")) | |
| # A1セルをクリップボードに複写 | |
| ws.Range("A1").Copy() | |
| # クリップボードをB2セルに複写 | |
| ws.Activate() | |
| ws.Range("B2").Select() | |
| ws.Paste() | |
| # クリップボードをC3セルに複写(値の貼り付け) | |
| ws.Range("C3").PasteSpecial(xlPasteValues) | |
| # クリップボードをC3セルに複写(書式貼り付け) | |
| ws.Range("C3").PasteSpecial(xlPasteFormats) | |
| # クリップボードをC3セルに複写(数式貼り付け) | |
| ws.Range("C3").PasteSpecial(xlPasteFormulas) | |
| # 切り取りモードまたはコピー モードを解除 | |
| ws.Application.CutCopyMode = False | |
| # ------------------------------------------------------------------ | |
| # A1セルをB1セルに移動 | |
| ws.Range("A1").Cut(ws.Range("B1")) | |
| # A1セルをSheet2のA1セルに移動 | |
| ws.Range("A1").Cut(wb.Worksheets("Sheet2").Range("A1")) | |
| # A1セルをクリップボードに移動 | |
| ws.Range("A1").Cut() | |
| # 切り取りモードまたはコピー モードを解除 | |
| ws.Application.CutCopyMode = False | |
| # ------------------------------------------------------------------ | |
| # https://excel-ubara.com/excelvba4/EXCEL254.html | |
| # A1～D5の範囲にG16～J20の範囲を一括複写(値の貼り付け) | |
| ws.Range("A1:D5").Value = ws.Range("G16:J20").Value | |
| # A1～D5の範囲にG16～J20の範囲を一括複写(値の貼り付け) | |
| ws.Range("A1").GetResize(ws.Range("G16:J20").Rows.Count, ws.Range("G16:J20").Columns.Count).Value = ws.Range("G16:J20").Value | |
| # ------------------------------------------------------------------ | |
| # A1～D4の範囲の左側の列の内容を範囲に一括複写 | |
| ws.Range("A1:D4").FillRight() | |
| # E1～H4の範囲の上段の行の内容を範囲に一括複写 | |
| ws.Range("E1:H4").FillDown() | |
| # A5～D8の範囲の右側の列の内容を範囲に一括複写 | |
| ws.Range("A5:D8").FillLeft() | |
| # E5～H8の範囲の下段の行の内容を範囲に一括複写 | |
| ws.Range("E5:H8").FillUp() | |
| # ------------------------------------------------------------------ | |
| # A1～H1の範囲をA1～H8の範囲にAutoFill | |
| ws.Range("A1:H1").AutoFill(ws.Range("A1:H8")) | |
| # A1～H2の範囲をA1～H8の範囲にAutoFill | |
| ws.Range("A1:H2").AutoFill(ws.Range("A1:H8"), xlFillDefault) | |
| # ------------------------------------------------------------------ | |
| # A1セル基準の現在の領域をAutoFilter | |
| ws.Range("A1").CurrentRegion.AutoFilter() | |
| ws.Range("A1").CurrentRegion.AutoFilter(Field=1, Criteria1=">30") | |
| # A1セル基準の現在の領域をAutoFilter | |
| ws.Range("A1").CurrentRegion.AutoFilter() | |
| ws.Range("A1").CurrentRegion.AutoFilter(Field=1, Criteria1=">30", Operator=xlAnd, Criteria2="<80") | |
| # A1セル基準の現在の領域をAutoFilter | |
| ws.Range("A1").CurrentRegion.AutoFilter() | |
| ws.Range("A1").CurrentRegion.AutoFilter(1, ">30") | |
| # A1セル基準の現在の領域をAutoFilter | |
| ws.Range("A1").CurrentRegion.AutoFilter() | |
| ws.Range("A1").CurrentRegion.AutoFilter(1, ">30", xlAnd, "<80") | |
| # A1セル基準の現在の領域をAutoFilter | |
| ws.Range("A1").CurrentRegion.AutoFilter() | |
| ws.Range("A1").CurrentRegion.AutoFilter(1, ">30", xlAnd, "<80") | |
| ws.Range("A1").CurrentRegion.AutoFilter(2, ">40", xlAnd, "<60") | |
| # AutoFilterの範囲を選択 | |
| ws.AutoFilter.Range.Select() | |
| # AutoFilterの範囲の可視状態セル選択 | |
| ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Select() | |
| # AutoFilterの範囲の可視状態セル行数 | |
| n = int(ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count / ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Columns.Count) | |
| print(n) | |
| # AutoFilterの適用 | |
| ws.AutoFilter.ApplyFilter() | |
| # AutoFilterの絞り込み解除 | |
| if ws.FilterMode: | |
| ws.ShowAllData() | |
| # AutoFilterの解除 | |
| if ws.AutoFilterMode: | |
| ws.AutoFilterMode = False | |
| # ------------------------------------------------------------------ | |
| # A1セル基準の現在の領域を並び替え ( Range Sort Method ) | |
| # Typeを省略するとType以降のOrder2等の指定が無視される。 | |
| # TypeをNoneにするとエラーにはならないのだが誤動作する。 | |
| ws.Activate() | |
| ws.Range("A1").CurrentRegion.Select() | |
| ws.Range("A1").CurrentRegion.Sort( | |
| Key1=ws.Range("A1"), | |
| Order1=xlDescending, | |
| Key2=ws.Range("B1"), | |
| Type=None, | |
| Order2=xlDescending, | |
| Key3=ws.Range("C1"), | |
| Order3=xlAscending, | |
| Header=xlYes, | |
| MatchCase=False, | |
| Orientation=xlSortColumns, | |
| SortMethod=xlPinYin, | |
| DataOption1=xlSortNormal, | |
| DataOption2=xlSortNormal, | |
| DataOption3=xlSortNormal, | |
| ) | |
| # A1セル基準の現在の領域を並び替え ( Sort Object ) | |
| # MicrosoftのVBAのリファレンスでは省略可能とされていますが、 | |
| # 省略すると正常に並び替えされない場合があるので注意が必要。 | |
| ws.Activate() | |
| ws.Range("A1").CurrentRegion.Select() | |
| ws.Sort.SortFields.Clear() | |
| ws.Sort.SortFields.Add(Key=ws.Range("A1"), SortOn=xlSortOnValues, DataOption=xlSortNormal, Order=xlAscending) | |
| ws.Sort.SortFields.Add(Key=ws.Range("B1"), SortOn=xlSortOnValues, DataOption=xlSortNormal, Order=xlAscending) | |
| ws.Sort.SortFields.Add(Key=ws.Range("C1"), SortOn=xlSortOnValues, DataOption=xlSortNormal, Order=xlDescending) | |
| ws.Sort.SetRange(ws.Range("A1").CurrentRegion) | |
| ws.Sort.Header = xlYes | |
| ws.Sort.MatchCase = False | |
| ws.Sort.Orientation = xlSortColumns | |
| ws.Sort.SortMethod = xlPinYin | |
| ws.Sort.Apply() | |
| # ------------------------------------------------------------------ | |
| # AutoFilterを行なった上で並び替え ( Sort Object ) | |
| # MicrosoftのVBAのリファレンスでは省略可能とされていますが、 | |
| # 省略すると正常に並び替えされない場合があるので注意が必要。 | |
| ws.Activate() | |
| ws.Range("A1").CurrentRegion.Select() | |
| ws.Range("A1").CurrentRegion.AutoFilter() | |
| ws.Range("A1").CurrentRegion.AutoFilter(1, ">30", xlAnd, "<80") | |
| ws.AutoFilter.Sort.SortFields.Clear() | |
| ws.AutoFilter.Sort.SortFields.Add(Key=ws.Range("A1"), SortOn=xlSortOnValues, DataOption=xlSortNormal, Order=xlDescending) | |
| ws.AutoFilter.Sort.SortFields.Add(Key=ws.Range("B1"), SortOn=xlSortOnValues, DataOption=xlSortNormal, Order=xlDescending) | |
| ws.AutoFilter.Sort.SortFields.Add(Key=ws.Range("C1"), SortOn=xlSortOnValues, DataOption=xlSortNormal, Order=xlAscending) | |
| # ws.AutoFilter.Sort.SetRange() | |
| ws.AutoFilter.Sort.Header = xlYes | |
| ws.AutoFilter.Sort.MatchCase = False | |
| ws.AutoFilter.Sort.Orientation = xlSortColumns | |
| ws.AutoFilter.Sort.SortMethod = xlPinYin | |
| ws.AutoFilter.Sort.Apply() | |
| # ------------------------------------------------------------------ | |
| # シートの非表示 | |
| ws.Visible = xlSheetHidden | |
| # シートの表示 | |
| ws.Visible = xlSheetVisible | |
| # ------------------------------------------------------------------ | |
| # シートの保護の設定 | |
| ws.Protect() | |
| # シートの保護の解除 | |
| ws.Unprotect() | |
| # シートをパスワード付きで保護の設定 | |
| ws.Protect(Password="hoge") | |
| # シートのパスワード付きの保護の解除 | |
| ws.Unprotect(Password="hoge") | |
| # ------------------------------------------------------------------ | |
| # ブックの保護の設定 | |
| wb.Protect() | |
| # ブックの保護の解除 | |
| wb.Unprotect() | |
| # ブックをパスワード付きで保護の設定 | |
| wb.Protect(Password="hoge") | |
| # ブックのパスワード付きの保護の解除 | |
| wb.Unprotect(Password="hoge") | |
| # ------------------------------------------------------------------ | |
| # https://learn.microsoft.com/en-us/office/vba/api/excel.windows | |
| # Note that the active window is always Windows(1). | |
| # ズームの倍率の設定 | |
| ws.Activate() | |
| ws.Range("A1").Select() | |
| ws.Parent.Windows(1).Zoom = 90 | |
| # ------------------------------------------------------------------ | |
| # https://excelwork.info/excel/freezepanes/ | |
| # https://stackoverflow.com/questions/43146073/ | |
| # 枠の固定 | |
| ws.Activate() | |
| ws.Range("C3").Select() | |
| ws.Parent.Windows(1).FreezePanes = True | |
| # ------------------------------------------------------------------ | |
| # https://excel-ubara.com/excelvba4/EXCEL272.html | |
| # CTRL+HOME的A1セル選択 | |
| ws.Activate() | |
| ws.Range("A1").Select() | |
| ws.Application.Goto(ws.Range("A1"), True) | |
| # https://qiita.com/Tachy_Pochy/items/64fe16ec076c52556b2d | |
| # CTRL+HOME的選択 ( AutoFilter使用時は残念 ) | |
| ws.Activate() | |
| r = int(ws.Parent.Windows(1).SplitRow) + 1 | |
| c = int(ws.Parent.Windows(1).SplitColumn) + 1 | |
| ws.Cells(r, c).Select() | |
| # CTRL+HOME的関数 | |
| def SpecialCells_xlHomeCell(ws): | |
| r = int(ws.Parent.Windows(1).SplitRow) + 1 | |
| c = int(ws.Parent.Windows(1).SplitColumn) + 1 | |
| rg = ws.Cells(r, c) | |
| if ws.Parent.Windows(1).FreezePanes and ws.Parent.Windows(1).SplitRow and ws.AutoFilterMode and ws.FilterMode: | |
| rg = rg.GetResize(ws.Cells.Rows.Count - rg.Row + 1, ws.Cells.Columns.Count - rg.Column + 1) | |
| rg = ws.Application.Intersect(ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible), rg) | |
| rg = rg.GetResize(1, 1) | |
| return rg | |
| # CTRL+HOME的選択 | |
| ws.Activate() | |
| rg = SpecialCells_xlHomeCell(ws) | |
| rg.Select() | |
| ws.Application.Goto(rg, True) | |
| # ------------------------------------------------------------------ | |
| # 再計算 | |
| ws.Calculate() | |
| # 再計算 | |
| xlApp.Calculate() | |
| # Excelの警告メッセージの表示の停止 | |
| xlApp.DisplayAlerts = False | |
| # Excelの警告メッセージの表示の開始 | |
| xlApp.DisplayAlerts = True | |
| # Excelの画面の更新を停止 | |
| xlApp.ScreenUpdating = False | |
| # Excelの画面の更新を開始 | |
| xlApp.ScreenUpdating = True | |
| # https://stackoverflow.com/questions/3735378/#8561483 | |
| # Excelの画面の更新を強制 | |
| xlApp.ActiveWindow.SmallScroll() | |
| xlApp.WindowState = xlApp.WindowState | |
| # ------------------------------------------------------------------ | |
| # ActiveWorkbookの取得 | |
| wb = xlApp.ActiveWorkbook | |
| print(wb.Name) | |
| # ActiveWindowの取得 | |
| aw = xlApp.ActiveWindow | |
| print(aw.Caption) | |
| # ActiveSheetの取得 | |
| ws = xlApp.ActiveSheet | |
| print(ws.Name) | |
| # ActiveSheetの取得 | |
| ws = wb.ActiveSheet | |
| print(ws.Name) | |
| # ActiveCellをRangeで取得 | |
| rg = xlApp.ActiveCell | |
| print(rg.Address) | |
| # Application オブジェクトの取得 | |
| xl = rg.Application | |
| print(xl.Name) | |
| # Application オブジェクトの取得 | |
| xl = ws.Application | |
| print(xl.Name) | |
| # Application オブジェクトの取得 | |
| xl = wb.Application | |
| print(xl.Name) | |
| # ------------------------------------------------------------------ | |
| # シートの名称の変更 | |
| wb.Worksheets("Sheet2").Name = "Sheet9" | |
| # シートの追加 | |
| ws = wb.Worksheets.Add() | |
| # シートの複写 ( 指定シートの前に複写 ) | |
| ws.Copy(Before=wb.Worksheets("Sheet9")) | |
| ws = wb.Worksheets(wb.Worksheets("Sheet9").Index - 1) | |
| # https://stackoverflow.com/questions/52685699/ | |
| # シートの複写 ( 指定シートの後に複写 ) | |
| ws.Copy(Before=None, After=wb.Worksheets("Sheet9")) | |
| ws = wb.Worksheets(wb.Worksheets("Sheet9").Index + 1) | |
| # シートの移動 ( 指定シートの前に移動 ) | |
| ws.Move(Before=wb.Worksheets("Sheet9")) | |
| # https://stackoverflow.com/questions/52685699/ | |
| # シートの移動 ( 指定シートの後に移動 ) | |
| ws.Move(Before=None, After=wb.Worksheets("Sheet9")) | |
| # シートの削除 | |
| wb.Worksheets("Sheet3").Delete() | |
| wb.Worksheets("Sheet9").Delete() | |
| # ------------------------------------------------------------------ | |
| # シートをPDF出力 | |
| ws.ExportAsFixedFormat(Type=xlTypePDF, Quality=xlQualityStandard, Filename=f"{os.getcwd()}\\output.pdf") | |
| # ------------------------------------------------------------------ | |
| # ブックをファイルに上書き保存 | |
| # wb.Save() | |
| # ブックをXLSXファイルに保存 | |
| wb.SaveAs(Filename=f"{os.getcwd()}\\outputSaveAs.xlsx", FileFormat=xlOpenXMLWorkbook) | |
| # ブックをXLSファイルに保存 | |
| wb.SaveAs(Filename=f"{os.getcwd()}\\outputSaveAs.xls", FileFormat=xlWorkbookNormal) | |
| # ブックをCSVファイルに保存 | |
| wb.SaveAs(Filename=f"{os.getcwd()}\\outputSaveAs.csv", FileFormat=xlCSV) | |
| # ------------------------------------------------------------------ | |
| # ブックをクローズ | |
| # wb.Close() | |
| # ブックを保存せずにクローズ | |
| wb.Close(SaveChanges=False) | |
| # ------------------------------------------------------------------ | |
| # Excel終了 | |
| xlApp.Quit() | |
| # ------------------------------------------------------------------ | |
| if __name__ == "__main__": | |
| main() |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # coding:utf-8 | |
| def main(): | |
| o = [] | |
| with open("sample.with.print.and.input.py", mode="rt", encoding="utf-8") as f: | |
| for line in f: | |
| if "# NO POST #" in line: | |
| continue | |
| line = line.replace('print("#', "#") | |
| line = line.replace(' #")', "") | |
| line = line.replace("#wb.Save()", " wb.Save()") | |
| line = line.replace("#wb.Close(", " wb.Close(") | |
| o.append(line) | |
| with open("sample.py", mode="wt", encoding="utf-8") as f: | |
| f.writelines(o) | |
| if __name__ == "__main__": | |
| main() |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| # coding:utf-8 | |
| import os | |
| import win32com.client | |
| import win32con | |
| import win32gui | |
| def main(): | |
| # ------------------------------------------------------------------ | |
| # Excelの定数を設定 | |
| # ------------------------------------------------------------------ | |
| # https://excel-ubara.com/EXCEL/EXCEL905.html | |
| # https://docs.microsoft.com/en-us/office/vba/api/excel(enumerations) | |
| # https://docs.microsoft.com/ja-jp/office/vba/api/excel(enumerations) | |
| # ------------------------------------------------------------------ | |
| # Excelの定数を取得する方法もあるようです。 | |
| # https://stackoverflow.com/questions/28264548/ | |
| # https://stackoverflow.com/questions/75154736/ | |
| # https://stackoverflow.com/questions/50127959/ | |
| # https://stackoverflow.com/questions/48257308/ | |
| # https://wacky.hatenadiary.com/entry/20091011/1255240572 | |
| # win32com.client.gencache.EnsureDispatch("Excel.Application") | |
| # ------------------------------------------------------------------ | |
| # Excel Enum Constants | |
| # ------------------------------------------------------------------ | |
| xlAbove = 0 | |
| xlBelow = 1 | |
| xlSolid = 1 | |
| xlFirst = 0 | |
| xlLast = 1 | |
| xlLastCell = 11 | |
| xlTopToBottom = 1 | |
| xlLeftToRight = 2 | |
| xlGeneral = 1 | |
| xlAutomatic = -4105 | |
| xlFormats = -4122 | |
| xlNone = -4142 | |
| xlCenter = -4108 | |
| xlDistributed = -4117 | |
| xlJustify = -4130 | |
| xlBottom = -4107 | |
| xlLeft = -4131 | |
| xlRight = -4152 | |
| xlTop = -4160 | |
| xlRTL = -5004 | |
| xlLTR = -5003 | |
| xlContext = -5002 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XLBorderWeight | |
| # ------------------------------------------------------------------ | |
| xlHairline = 1 | |
| xlThin = 2 | |
| xlThick = 4 | |
| xlMedium = -4138 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XLBordersIndex | |
| # ------------------------------------------------------------------ | |
| xlDiagonalDown = 5 | |
| xlDiagonalUp = 6 | |
| xlEdgeLeft = 7 | |
| xlEdgeTop = 8 | |
| xlEdgeBottom = 9 | |
| xlEdgeRight = 10 | |
| xlInsideHorizontal = 12 | |
| xlInsideVertical = 11 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlAutoFillType | |
| # ------------------------------------------------------------------ | |
| xlFillDefault = 0 | |
| xlFillCopy = 1 | |
| xlFillSeries = 2 | |
| xlFillFormats = 3 | |
| xlFillValues = 4 | |
| xlFillDays = 5 | |
| xlFillWeekdays = 6 | |
| xlFillMonths = 7 | |
| xlFillYears = 8 | |
| xlLinearTrend = 9 | |
| xlGrowthTrend = 10 | |
| xlFlashFill = 11 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlAutoFilterOperator | |
| # ------------------------------------------------------------------ | |
| xlAnd = 1 | |
| xlOr = 2 | |
| xlTop10Items = 3 | |
| xlBottom10Items = 4 | |
| xlTop10Percent = 5 | |
| xlBottom10Percent = 6 | |
| xlFilterValues = 7 | |
| xlFilterCellColor = 8 | |
| xlFilterFontColor = 9 | |
| xlFilterIcon = 10 | |
| xlFilterDynamic = 11 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlCVError | |
| # ------------------------------------------------------------------ | |
| xlErrDiv0 = 2007 | |
| xlErrNA = 2042 | |
| xlErrName = 2029 | |
| xlErrNull = 2000 | |
| xlErrNum = 2036 | |
| xlErrRef = 2023 | |
| xlErrValue = 2015 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlCVError | |
| # ------------------------------------------------------------------ | |
| xlErrSpill = 2045 | |
| xlErrConnect = 2046 | |
| xlErrBlocked = 2047 | |
| xlErrUnknown = 2048 | |
| xlErrField = 2049 | |
| xlErrCalc = 2050 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlCellType | |
| # ------------------------------------------------------------------ | |
| xlCellTypeConstants = 2 | |
| xlCellTypeBlanks = 4 | |
| xlCellTypeLastCell = 11 | |
| xlCellTypeVisible = 12 | |
| xlCellTypeFormulas = -4123 | |
| xlCellTypeComments = -4144 | |
| xlCellTypeAllFormatConditions = -4172 | |
| xlCellTypeSameFormatConditions = -4173 | |
| xlCellTypeAllValidation = -4174 | |
| xlCellTypeSameValidation = -4175 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlColorIndex | |
| # ------------------------------------------------------------------ | |
| xlColorIndexAutomatic = -4105 | |
| xlColorIndexNone = -4142 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlCutCopyMode | |
| # ------------------------------------------------------------------ | |
| xlCopy = 1 | |
| xlCut = 2 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlDeleteShiftDirection | |
| # Excel Enum XlInsertShiftDirection | |
| # ------------------------------------------------------------------ | |
| xlShiftUp = -4162 | |
| xlShiftDown = -4121 | |
| xlShiftToLeft = -4159 | |
| xlShiftToRight = -4161 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlDirection | |
| # ------------------------------------------------------------------ | |
| xlUp = -4162 | |
| xlDown = -4121 | |
| xlToLeft = -4159 | |
| xlToRight = -4161 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlFileFormat | |
| # ------------------------------------------------------------------ | |
| xlCSV = 6 | |
| xlHtml = 44 | |
| xlWorkbookDefault = 51 | |
| xlOpenXMLWorkbook = 51 | |
| xlOpenXMLWorkbookMacroEnabled = 52 | |
| xlWorkbookNormal = -4143 | |
| xlCurrentPlatformText = -4158 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlFindLookIn | |
| # ------------------------------------------------------------------ | |
| xlComments = -4144 | |
| xlCommentsThreaded = -4184 | |
| xlFormulas = -4123 | |
| xlValues = -4163 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlFixedFormatQuality | |
| # ------------------------------------------------------------------ | |
| xlQualityStandard = 0 | |
| xlQualityMinimum = 1 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlFixedFormatType | |
| # ------------------------------------------------------------------ | |
| xlTypePDF = 0 | |
| xlTypeXPS = 1 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlLineStyle | |
| # ------------------------------------------------------------------ | |
| xlContinuous = 1 | |
| xlDashDot = 4 | |
| xlDashDotDot = 5 | |
| xlSlantDashDot = 13 | |
| xlDash = -4115 | |
| xldot = -4118 | |
| xlDouble = -4119 | |
| xlLineStyleNone = -4142 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlLookAt | |
| # ------------------------------------------------------------------ | |
| xlPart = 2 | |
| xlWhole = 1 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlOrientation | |
| # ------------------------------------------------------------------ | |
| xlHorizontal = -4128 | |
| xlVertical = -4166 | |
| xlDownward = -4170 | |
| xlUpward = -4171 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlPasteType | |
| # ------------------------------------------------------------------ | |
| xlPasteValues = -4163 | |
| xlPasteComments = -4144 | |
| xlPasteFormulas = -4123 | |
| xlPasteFormats = -4122 | |
| xlPasteAll = -4104 | |
| xlPasteValidation = 6 | |
| xlPasteAllExceptBorders = 7 | |
| xlPasteColumnWidths = 8 | |
| xlPasteFormulasAndNumberFormats = 11 | |
| xlPasteValuesAndNumberFormats = 12 | |
| xlPasteAllUsingSourceTheme = 13 | |
| xlPasteAllMergingConditionalFormats = 14 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlReferenceStyle | |
| # ------------------------------------------------------------------ | |
| xlA1 = 1 | |
| xlR1C1 = -4150 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlReferenceType | |
| # ------------------------------------------------------------------ | |
| xlAbsolute = 1 | |
| xlAbsRowRelColumn = 2 | |
| xlRelRowAbsColumn = 3 | |
| xlRelative = 4 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSearchDirection | |
| # ------------------------------------------------------------------ | |
| xlNext = 1 | |
| xlPrevious = 2 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSearchOrder | |
| # ------------------------------------------------------------------ | |
| xlByColumns = 2 | |
| xlByRows = 1 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSheetVisibility | |
| # ------------------------------------------------------------------ | |
| xlSheetVisible = -1 | |
| xlSheetHidden = 0 | |
| xlSheetVeryHidden = 2 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSortDataOption | |
| # ------------------------------------------------------------------ | |
| xlSortNormal = 0 | |
| xlSortTextAsNumbers = 1 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSortMethod | |
| # ------------------------------------------------------------------ | |
| xlPinYin = 1 | |
| xlStroke = 2 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSortOn | |
| # ------------------------------------------------------------------ | |
| xlSortOnValues = 0 | |
| xlSortOnCellColor = 1 | |
| xlSortOnFontColor = 2 | |
| xlSortOnIcon = 3 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSortOrder | |
| # ------------------------------------------------------------------ | |
| xlAscending = 1 | |
| xlDescending = 2 | |
| xlManual = -4135 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSortOrientation | |
| # ------------------------------------------------------------------ | |
| xlSortColumns = 1 | |
| xlSortRows = 2 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSortType | |
| # ------------------------------------------------------------------ | |
| xlSortValues = 1 | |
| xlSortLabels = 2 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlSpecialCellsValue | |
| # ------------------------------------------------------------------ | |
| xlNumbers = 1 | |
| xlTextValues = 2 | |
| xlLogical = 4 | |
| xlErrors = 16 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlUnderlineStyle | |
| # ------------------------------------------------------------------ | |
| xlUnderlineStyleNone = -4142 | |
| xlUnderlineStyleDouble = -4119 | |
| xlUnderlineStyleSingle = 2 | |
| xlUnderlineStyleSingleAccounting = 4 | |
| xlUnderlineStyleDoubleAccounting = 5 | |
| # ------------------------------------------------------------------ | |
| # Excel Enum XlYesNoGuess | |
| # ------------------------------------------------------------------ | |
| xlGuess = 0 | |
| xlYes = 1 | |
| xlNo = 2 | |
| # ------------------------------------------------------------------ | |
| print("# Excel起動 #") | |
| xlApp = win32com.client.Dispatch("Excel.Application") | |
| # print("press enter key to continue") # NO POST # | |
| # input() # NO POST # | |
| # https://stackoverflow.com/questions/2790825/ | |
| print("# ExcelのWindow最大化 #") | |
| win32gui.ShowWindow(xlApp.hwnd, win32con.SW_MAXIMIZE) | |
| # print("press enter key to continue") # NO POST # | |
| # input() # NO POST # | |
| print("# Excel表示 #") | |
| xlApp.Visible = 1 | |
| # print("press enter key to continue") # NO POST # | |
| # input() # NO POST # | |
| print("# Excelファイルオープン #") | |
| wb = xlApp.Workbooks.Open(f"{os.getcwd()}\\sample.csv") | |
| # print("press enter key to continue") # NO POST # | |
| # input() # NO POST # | |
| print("# Excelシートオブジェクト #") | |
| ws = wb.Worksheets(1) | |
| # print("press enter key to continue") # NO POST # | |
| # input() # NO POST # | |
| # ------------------------------------------------------ # NO POST # | |
| # NO POST # | |
| ws.Activate() # NO POST # | |
| ws.Range("A1").RowHeight = 50 # NO POST # | |
| ws.Range("A1").ColumnWidth = 20 # NO POST # | |
| ws.Cells.VerticalAlignment = xlBottom # NO POST # | |
| ws.Name = "Sheet1" # NO POST # | |
| ws.Parent.Worksheets.Add() # NO POST # | |
| ws.Move(Before=wb.Worksheets("Sheet2")) # NO POST # | |
| ws.Activate() # NO POST # | |
| # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# 指定したシートを選択 #") | |
| print("# Select()の使用前にシートのActivate()が必要 #") | |
| ws.Activate() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セルを選択 #") | |
| ws.Range("A1").Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1～B2を選択 #") | |
| ws.Range("A1:B2").Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1とB2とC3とD4を選択 #") | |
| ws.Range("A1,B2,C3,D4").Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1～B2とC3～D4を選択 #") | |
| ws.Range("A1:B2,C3:D4").Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セルを選択 #") | |
| ws.Cells(1, 1).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1～B2を選択 #") | |
| ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# 全セルを選択 #") | |
| ws.Cells.Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# 1～2行を選択 #") | |
| ws.Range("1:2").Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A～B列を選択 #") | |
| ws.Range("A:B").Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # https://www.relief.jp/docs/excel-vba-difference-range-rows-columns.html | |
| print("# 1～2行を選択 #") | |
| ws.Rows("1:2").Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # https://www.relief.jp/docs/excel-vba-difference-range-columns.html | |
| print("# A～B列を選択 #") | |
| ws.Columns("A:B").Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# 指定範囲の先頭行を選択 #") | |
| ws.Range("A1:D4").Rows(1).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 指定範囲の先頭列を選択 #") | |
| ws.Range("A1:D4").Columns(1).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# (A1セル基準で)行全体を選択 #") | |
| ws.Range("A1").EntireRow.Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# (A1セル基準で)列全体を選択 #") | |
| ws.Range("A1").EntireColumn.Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# (A1セル基準で)最終列までの行を選択 #") | |
| ws.Range(ws.Range("A1"), ws.Cells(1, ws.Columns.Count).End(xlToLeft)).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# (A1セル基準で)最終行までの列を選択 #") | |
| ws.Range(ws.Range("A1"), ws.Cells(ws.Rows.Count, 1).End(xlUp)).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# (A1セル基準で)現在の領域を選択 #") | |
| ws.Range("A1").CurrentRegion.Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# (現行シートの)使用中領域を選択 #") | |
| ws.UsedRange.Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# (A1セル基準で)最終右下セル選択 #") | |
| ws.Range("A1").SpecialCells(xlLastCell).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# (A1セル基準で)可視状態セル選択 #") | |
| ws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# 指定範囲の参照範囲の文字列を行列両方を絶対参照で取得 #") | |
| a = ws.UsedRange.Address | |
| print(a) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 指定範囲の参照範囲の文字列を行列両方を絶対参照で取得 #") | |
| a = ws.UsedRange.GetAddress() | |
| print(a) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 指定範囲の参照範囲の文字列を行列両方を絶対参照で取得 #") | |
| a = ws.UsedRange.GetAddress(True, True) | |
| print(a) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 指定範囲の参照範囲の文字列を行列両方を相対参照で取得 #") | |
| a = ws.UsedRange.GetAddress(False, False) | |
| print(a) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 指定範囲の参照範囲の文字列を行列両方を相対参照のA1形式で取得 #") | |
| a = ws.UsedRange.GetAddress(False, False, xlA1) | |
| print(a) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 指定範囲の参照範囲の文字列を行列両方を相対参照のR1C1形式で取得 #") | |
| a = ws.UsedRange.GetAddress(False, False, xlR1C1) | |
| print(a) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 指定範囲の参照範囲の文字列を行列両方を相対参照のxlA1形式の外部参照で取得 #") | |
| a = ws.UsedRange.GetAddress(False, False, xlA1, True) | |
| print(a) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# 数式的な文字列のセル参照をR1C1形式からA1形式に変更 #") | |
| a1 = ws.UsedRange.GetAddress(False, False, xlR1C1, False) | |
| a2 = ws.Application.ConvertFormula(a1, xlR1C1, xlA1) | |
| print(a1) | |
| print(a2) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 数式的な文字列のセル参照をA1形式からR1C1形式に変更 #") | |
| a1 = ws.UsedRange.GetAddress(False, False, xlA1, False) | |
| a2 = ws.Application.ConvertFormula(a1, xlA1, xlR1C1) | |
| print(a1) | |
| print(a2) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 数式的な文字列のセル参照を相対参照のA1形式から絶対参照のA1形式に変更 #") | |
| a1 = ws.UsedRange.GetAddress(False, False, xlA1, False) | |
| a2 = ws.Application.ConvertFormula(a1, xlA1, xlA1, xlAbsolute) | |
| print(a1) | |
| print(a2) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 数式的な文字列のセル参照を絶対参照のA1形式から相対参照のA1形式に変更 #") | |
| a1 = ws.UsedRange.GetAddress(True, True, xlA1, False) | |
| a2 = ws.Application.ConvertFormula(a1, xlA1, xlA1, xlRelative) | |
| print(a1) | |
| print(a2) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 数式的な文字列の外部参照のセル参照をA1形式からR1C1形式に変更 #") | |
| a1 = ws.UsedRange.GetAddress(False, False, xlA1, True) | |
| a2 = ws.Application.ConvertFormula(a1, xlA1, xlR1C1) | |
| print(a1) | |
| print(a2) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 数式的な文字列の外部参照のセル参照をR1C1形式からA1形式に変更 #") | |
| a1 = ws.UsedRange.GetAddress(False, False, xlR1C1, True) | |
| a2 = ws.Application.ConvertFormula(a1, xlR1C1, xlA1) | |
| print(a1) | |
| print(a2) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 数式的な文字列の外部参照のセル参照を相対参照のR1C1形式から絶対参照のA1形式に変更 #") | |
| a1 = ws.UsedRange.GetAddress(False, False, xlR1C1, True) | |
| a2 = ws.Application.ConvertFormula(a1, xlR1C1, xlA1, xlAbsolute) | |
| print(a1) | |
| print(a2) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 数式的な文字列の外部参照のセル参照を絶対参照のR1C1形式から相対参照のA1形式に変更 #") | |
| a1 = ws.UsedRange.GetAddress(True, True, xlR1C1, True) | |
| a2 = ws.Application.ConvertFormula(a1, xlR1C1, xlA1, xlRelative) | |
| print(a1) | |
| print(a2) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1形式の外部参照のセル参照の文字列でRangeを取得 #") | |
| print("# R1C1形式は使用不可 #") | |
| a = ws.UsedRange.GetAddress(False, False, xlA1, True) | |
| rg = ws.Evaluate(a) | |
| print(rg.Address) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1形式の外部参照のセル参照の文字列でRangeを取得 #") | |
| print("# R1C1形式は使用不可 #") | |
| a = ws.UsedRange.GetAddress(False, False, xlA1, True) | |
| rg = ws.Application.Evaluate(a) | |
| print(rg.Address) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| # https://thecodingforums.com/threads/328174/ | |
| print("# Range.Offset()Property用GetOffset()Method #") | |
| print("# 指定範囲をOffset #") | |
| ws.Range("A1:D4").GetOffset(2, 2).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# Range.Offset()Property用GetOffset()Method #") | |
| print("# 指定範囲をOffset #") | |
| ws.Range("A1:D4").GetOffset(RowOffset=3, ColumnOffset=3).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# Range.Offset()Property用GetOffset()Method #") | |
| print("# 指定範囲を縦方向にOffset #") | |
| ws.Range("A1:D4").GetOffset(RowOffset=3).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# Range.Offset()Property用GetOffset()Method #") | |
| print("# 指定範囲を横方向にOffset #") | |
| ws.Range("A1:D4").GetOffset(RowOffset=0, ColumnOffset=3).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| # https://stackoverflow.com/questions/63112880/ | |
| print("# Range.Resize()Property用GetResize()Method #") | |
| print("# 指定範囲をResize #") | |
| ws.Range("A1:H8").GetResize(2, 2).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# Range.Resize()Property用GetResize()Method #") | |
| print("# 指定範囲をResize #") | |
| ws.Range("A1:H8").GetResize(RowSize=3, ColumnSize=3).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# Range.Resize()Property用GetResize()Method #") | |
| print("# 指定範囲の縦方向をResize #") | |
| ws.Range("A1:H8").GetResize(RowSize=3).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# Range.Resize()Property用GetResize()Method #") | |
| print("# 指定範囲の横方向をResize #") | |
| ws.Range("A1:H8").GetResize(RowSize=ws.Range("A1:H8").Rows.Count, ColumnSize=3).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# 指定範囲から指定の文字列を含むセルを選択 #") | |
| rg = ws.UsedRange | |
| rg.find("56", rg.GetResize(1, 1), xlValues, xlPart, xlByRows, xlNext, True).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 指定範囲から指定の文字列と合致のセルを選択 #") | |
| rg = ws.UsedRange | |
| rg.find("56", rg.GetResize(1, 1), xlValues, xlWhole, xlByRows, xlNext, True).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------ # NO POST # | |
| # NO POST # | |
| ws.Activate() # NO POST # | |
| ws.Range("A1").Select() # NO POST # | |
| # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# 指定セルの行数を取得 #") | |
| n = ws.Range("A1").Row | |
| print(n) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 指定セルの列数を取得 #") | |
| n = ws.Range("A1").Column | |
| print(n) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 指定範囲に含まれる行数を取得 #") | |
| n = ws.Range("A1:D4").Rows.Count | |
| print(n) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 指定範囲に含まれる列数を取得 #") | |
| n = ws.Range("A1:D4").Columns.Count | |
| print(n) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# (A1セル基準で)最終行数を取得 #") | |
| n = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row | |
| print(n) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# (A1セル基準で)最終列数を取得 #") | |
| n = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column | |
| print(n) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| # https://binary-star.net/excel-vba-columnchange | |
| print("# 数字列を英字列に変換 #") | |
| a = (ws.Cells(1, 1).Address).split("$")[1] | |
| print(a) | |
| a = (ws.Cells(1, 2).Address).split("$")[1] | |
| print(a) | |
| a = (ws.Cells(1, 3).Address).split("$")[1] | |
| print(a) | |
| a = (ws.Cells(1, 27).Address).split("$")[1] | |
| print(a) | |
| a = (ws.Cells(1, 53).Address).split("$")[1] | |
| print(a) | |
| print("# 英字列を数字列に変換 #") | |
| n = ws.Cells(1, "A").Column | |
| print(n) | |
| n = ws.Cells(1, "B").Column | |
| print(n) | |
| n = ws.Cells(1, "C").Column | |
| print(n) | |
| n = ws.Cells(1, "AA").Column | |
| print(n) | |
| n = ws.Cells(1, "BA").Column | |
| print(n) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セルに値を設定 #") | |
| ws.Range("A1").Value = 99999 | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルに数式を設定 #") | |
| ws.Range("A1").Formula = "=(3.14159-3)*100000" | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セルの表示形式を設定 #") | |
| ws.Range("A1").NumberFormatLocal = "0.00" | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セルの配置の縦位置を設定 #") | |
| ws.Range("A1").VerticalAlignment = xlCenter | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの配置の横位置を設定 #") | |
| ws.Range("A1").HorizontalAlignment = xlCenter | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの文字列の方向を設定 #") | |
| ws.Range("A1").Orientation = xlUpward | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの文字列の方向を設定 #") | |
| ws.Range("A1").Orientation = 45 | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの文字列の方向を設定 #") | |
| ws.Range("A1").Orientation = 0 | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セルにフォントを設定 #") | |
| ws.Range("A1").Font.Name = "Yu Gothic UI" | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルのフォントのサイズを設定 #") | |
| ws.Range("A1").Font.Size = 12 | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルのフォントを太字に設定 #") | |
| ws.Range("A1").Font.Bold = True | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルのフォントを斜体に設定 #") | |
| ws.Range("A1").Font.Italic = True | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルのフォントに下線を設定 #") | |
| ws.Range("A1").Font.Underline = xlUnderlineStyleSingle | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルのフォントに取消線を設定 #") | |
| ws.Range("A1").Font.Strikethrough = True | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セルの文字の色を設定(R+Gx256+Bx256x256) #") | |
| ws.Range("A1").Font.Color = 255 + 0 * 256 + 0 * 256 * 256 | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの文字の色を設定(指定順序BGR) #") | |
| ws.Range("A1").Font.Color = int("FF0000", 16) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの文字の色を設定(指定順序RGB) #") | |
| ws.Range("A1").Font.Color = int("".join(list(reversed(["FF0000"[i : i + 2] for i in range(0, 6, 2)]))), 16) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの文字の色を設定(デフォルト) #") | |
| ws.Range("A1").Font.ColorIndex = xlColorIndexAutomatic | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セルの背景の色を設定(R+Gx256+Bx256x256) #") | |
| ws.Range("A1").Interior.Color = 255 + 255 * 256 + 0 * 256 * 256 | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの背景の色を設定(指定順序BGR) #") | |
| ws.Range("A1").Interior.Color = int("FFFF00", 16) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの背景の色を設定(指定順序RGB) #") | |
| ws.Range("A1").Interior.Color = int("".join(list(reversed(["FFFF00"[i : i + 2] for i in range(0, 6, 2)]))), 16) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの背景の色を設定(デフォルト) #") | |
| ws.Range("A1").Interior.ColorIndex = xlColorIndexNone | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セルに罫線を設定 #") | |
| ws.Range("A1").Borders.Color = int("".join(list(reversed(["FF0000"[i : i + 2] for i in range(0, 6, 2)]))), 16) | |
| ws.Range("A1").Borders.LineStyle = xlContinuous | |
| ws.Range("A1").Borders.Weight = xlMedium | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの罫線を解除 #") | |
| ws.Range("A1").Borders.LineStyle = xlLineStyleNone | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの下側に罫線を設定 #") | |
| ws.Range("A1").Borders(xlEdgeBottom).Color = int("".join(list(reversed(["FF0000"[i : i + 2] for i in range(0, 6, 2)]))), 16) | |
| ws.Range("A1").Borders(xlEdgeBottom).LineStyle = xlContinuous | |
| ws.Range("A1").Borders(xlEdgeBottom).Weight = xlMedium | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの下側の罫線を解除 #") | |
| ws.Range("A1").Borders(xlEdgeBottom).LineStyle = xlLineStyleNone | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セルの値や数式を消去 #") | |
| ws.Range("A1").ClearContents() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルの書式を消去 #") | |
| ws.Range("A1").ClearFormats() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルを消去 #") | |
| ws.Range("A1").Clear() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルを削除 #") | |
| ws.Range("A1").Delete() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルを削除(上方向にシフト) #") | |
| ws.Range("A1").Delete(xlShiftUp) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルを削除(左方向にシフト) #") | |
| ws.Range("A1").Delete(xlShiftToLeft) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルに挿入 #") | |
| ws.Range("A1").Insert() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルに挿入(下方向にシフト) #") | |
| ws.Range("A1").Insert(xlShiftDown) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルに挿入(右方向にシフト) #") | |
| ws.Range("A1").Insert(xlShiftToRight) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# (A1行基準で)高さを設定 #") | |
| ws.Range("A1").RowHeight = 30 | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# (A1列基準で)横幅を設定 #") | |
| ws.Range("A1").ColumnWidth = 30 | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# (A1行基準で)高さを自動調整 #") | |
| ws.Range("A1").EntireRow.AutoFit() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# (A1列基準で)横幅を自動調整 #") | |
| ws.Range("A1").EntireColumn.AutoFit() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| # 個別のグループ化の表示と非表示を行う方法 | |
| # https://vbabeginner.net/%e3%82%b0%e3%83%ab%e3%83%bc%e3%83%97%e5%8c%96%e3%81%ae%e8%a1%a8%e7%a4%ba%e3%81%a8%e9%9d%9e%e8%a1%a8%e7%a4%ba/ | |
| print("# 行のグループ化を設定 #") | |
| ws.Range("A1").EntireRow.Group() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 行のグループ化を非表示 #") | |
| ws.Outline.ShowLevels(RowLevels=1) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 行のグループ化を表示 #") | |
| ws.Outline.ShowLevels(RowLevels=8) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 行のグループ化を解除 #") | |
| ws.Range("A1").EntireRow.Ungroup() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| ws.Application.Goto(ws.Range("A1"), True) # NO POST # | |
| # NO POST # | |
| print("# 列のグループ化を設定 #") | |
| ws.Range("A1").EntireColumn.Group() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 列のグループ化を非表示 #") | |
| ws.Outline.ShowLevels(RowLevels=0, ColumnLevels=1) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 列のグループ化を表示 #") | |
| ws.Outline.ShowLevels(RowLevels=0, ColumnLevels=8) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 列のグループ化を解除 #") | |
| ws.Range("A1").EntireColumn.Ungroup() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| ws.Application.Goto(ws.Range("A1"), True) # NO POST # | |
| # NO POST # | |
| # ------------------------------------------------------ # NO POST # | |
| # NO POST # | |
| ws.Range("A1").Value = 11 # NO POST # | |
| ws.Range("B1").Value = 12 # NO POST # | |
| ws.Range("C1").Value = 13 # NO POST # | |
| # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セルをB1セルに複写 #") | |
| ws.Range("A1").Copy(ws.Range("B1")) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルをSheet2のA1セルに複写 #") | |
| ws.Range("A1").Copy(wb.Worksheets("Sheet2").Range("A1")) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セル基準の現在の領域をSheet2のA1セル基準で複写 #") | |
| ws.Range("A1").CurrentRegion.Copy(wb.Worksheets("Sheet2").Range("A1")) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルをクリップボードに複写 #") | |
| ws.Range("A1").Copy() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# クリップボードをB2セルに複写 #") | |
| ws.Activate() | |
| ws.Range("B2").Select() | |
| ws.Paste() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# クリップボードをC3セルに複写(値の貼り付け) #") | |
| ws.Range("C3").PasteSpecial(xlPasteValues) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# クリップボードをC3セルに複写(書式貼り付け) #") | |
| ws.Range("C3").PasteSpecial(xlPasteFormats) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# クリップボードをC3セルに複写(数式貼り付け) #") | |
| ws.Range("C3").PasteSpecial(xlPasteFormulas) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 切り取りモードまたはコピー モードを解除 #") | |
| ws.Application.CutCopyMode = False | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セルをB1セルに移動 #") | |
| ws.Range("A1").Cut(ws.Range("B1")) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルをSheet2のA1セルに移動 #") | |
| ws.Range("A1").Cut(wb.Worksheets("Sheet2").Range("A1")) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セルをクリップボードに移動 #") | |
| ws.Range("A1").Cut() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 切り取りモードまたはコピー モードを解除 #") | |
| ws.Application.CutCopyMode = False | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| # https://excel-ubara.com/excelvba4/EXCEL254.html | |
| print("# A1～D5の範囲にG16～J20の範囲を一括複写(値の貼り付け) #") | |
| ws.Range("A1:D5").Value = ws.Range("G16:J20").Value | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1～D5の範囲にG16～J20の範囲を一括複写(値の貼り付け) #") | |
| ws.Range("A1").GetResize(ws.Range("G16:J20").Rows.Count, ws.Range("G16:J20").Columns.Count).Value = ws.Range("G16:J20").Value | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1～D4の範囲の左側の列の内容を範囲に一括複写 #") | |
| ws.Range("A1:D4").FillRight() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# E1～H4の範囲の上段の行の内容を範囲に一括複写 #") | |
| ws.Range("E1:H4").FillDown() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A5～D8の範囲の右側の列の内容を範囲に一括複写 #") | |
| ws.Range("A5:D8").FillLeft() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# E5～H8の範囲の下段の行の内容を範囲に一括複写 #") | |
| ws.Range("E5:H8").FillUp() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| ws.Range("A1").Value = 11 # NO POST # | |
| ws.Range("B1").Value = 12 # NO POST # | |
| ws.Range("C1").Value = 13 # NO POST # | |
| ws.Range("D1").Value = 14 # NO POST # | |
| ws.Range("E1").Value = 15 # NO POST # | |
| ws.Range("F1").Value = 16 # NO POST # | |
| ws.Range("G1").Value = 17 # NO POST # | |
| ws.Range("H1").Value = 18 # NO POST # | |
| # NO POST # | |
| print("# A1～H1の範囲をA1～H8の範囲にAutoFill #") | |
| ws.Range("A1:H1").AutoFill(ws.Range("A1:H8")) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| ws.Range("A1").Value = 11 # NO POST # | |
| ws.Range("B1").Value = 12 # NO POST # | |
| ws.Range("C1").Value = 13 # NO POST # | |
| ws.Range("D1").Value = 14 # NO POST # | |
| ws.Range("E1").Value = 15 # NO POST # | |
| ws.Range("F1").Value = 16 # NO POST # | |
| ws.Range("G1").Value = 17 # NO POST # | |
| ws.Range("H1").Value = 18 # NO POST # | |
| # NO POST # | |
| ws.Range("A2").Value = 21 # NO POST # | |
| ws.Range("B2").Value = 22 # NO POST # | |
| ws.Range("C2").Value = 23 # NO POST # | |
| ws.Range("D2").Value = 24 # NO POST # | |
| ws.Range("E2").Value = 25 # NO POST # | |
| ws.Range("F2").Value = 26 # NO POST # | |
| ws.Range("G2").Value = 27 # NO POST # | |
| ws.Range("H2").Value = 28 # NO POST # | |
| # NO POST # | |
| print("# A1～H2の範囲をA1～H8の範囲にAutoFill #") | |
| ws.Range("A1:H2").AutoFill(ws.Range("A1:H8"), xlFillDefault) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------ # NO POST # | |
| # NO POST # | |
| ws.Activate() # NO POST # | |
| ws.Range("A1").Select() # NO POST # | |
| # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セル基準の現在の領域をAutoFilter #") | |
| ws.Range("A1").CurrentRegion.AutoFilter() | |
| ws.Range("A1").CurrentRegion.AutoFilter(Field=1, Criteria1=">30") | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セル基準の現在の領域をAutoFilter #") | |
| ws.Range("A1").CurrentRegion.AutoFilter() | |
| ws.Range("A1").CurrentRegion.AutoFilter(Field=1, Criteria1=">30", Operator=xlAnd, Criteria2="<80") | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セル基準の現在の領域をAutoFilter #") | |
| ws.Range("A1").CurrentRegion.AutoFilter() | |
| ws.Range("A1").CurrentRegion.AutoFilter(1, ">30") | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セル基準の現在の領域をAutoFilter #") | |
| ws.Range("A1").CurrentRegion.AutoFilter() | |
| ws.Range("A1").CurrentRegion.AutoFilter(1, ">30", xlAnd, "<80") | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セル基準の現在の領域をAutoFilter #") | |
| ws.Range("A1").CurrentRegion.AutoFilter() | |
| ws.Range("A1").CurrentRegion.AutoFilter(1, ">30", xlAnd, "<80") | |
| ws.Range("A1").CurrentRegion.AutoFilter(2, ">40", xlAnd, "<60") | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# AutoFilterの範囲を選択 #") | |
| ws.AutoFilter.Range.Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# AutoFilterの範囲の可視状態セル選択 #") | |
| ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# AutoFilterの範囲の可視状態セル行数 #") | |
| n = int(ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count / ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Columns.Count) | |
| print(n) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| ws.Range("A1").Select() # NO POST # | |
| ws.Range("A5").Value = 99999 # NO POST # | |
| # NO POST # | |
| print("# AutoFilterの適用 #") | |
| ws.AutoFilter.ApplyFilter() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# AutoFilterの絞り込み解除 #") | |
| if ws.FilterMode: | |
| ws.ShowAllData() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# AutoFilterの解除 #") | |
| if ws.AutoFilterMode: | |
| ws.AutoFilterMode = False | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# A1セル基準の現在の領域を並び替え ( Range Sort Method ) #") | |
| print("# Typeを省略するとType以降のOrder2等の指定が無視される。 #") | |
| print("# TypeをNoneにするとエラーにはならないのだが誤動作する。 #") | |
| ws.Activate() | |
| ws.Range("A1").CurrentRegion.Select() | |
| ws.Range("A1").CurrentRegion.Sort( | |
| Key1=ws.Range("A1"), | |
| Order1=xlDescending, | |
| Key2=ws.Range("B1"), | |
| Type=None, | |
| Order2=xlDescending, | |
| Key3=ws.Range("C1"), | |
| Order3=xlAscending, | |
| Header=xlYes, | |
| MatchCase=False, | |
| Orientation=xlSortColumns, | |
| SortMethod=xlPinYin, | |
| DataOption1=xlSortNormal, | |
| DataOption2=xlSortNormal, | |
| DataOption3=xlSortNormal, | |
| ) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# A1セル基準の現在の領域を並び替え ( Sort Object ) #") | |
| print("# MicrosoftのVBAのリファレンスでは省略可能とされていますが、 #") | |
| print("# 省略すると正常に並び替えされない場合があるので注意が必要。 #") | |
| ws.Activate() | |
| ws.Range("A1").CurrentRegion.Select() | |
| ws.Sort.SortFields.Clear() | |
| ws.Sort.SortFields.Add(Key=ws.Range("A1"), SortOn=xlSortOnValues, DataOption=xlSortNormal, Order=xlAscending) | |
| ws.Sort.SortFields.Add(Key=ws.Range("B1"), SortOn=xlSortOnValues, DataOption=xlSortNormal, Order=xlAscending) | |
| ws.Sort.SortFields.Add(Key=ws.Range("C1"), SortOn=xlSortOnValues, DataOption=xlSortNormal, Order=xlDescending) | |
| ws.Sort.SetRange(ws.Range("A1").CurrentRegion) | |
| ws.Sort.Header = xlYes | |
| ws.Sort.MatchCase = False | |
| ws.Sort.Orientation = xlSortColumns | |
| ws.Sort.SortMethod = xlPinYin | |
| ws.Sort.Apply() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# AutoFilterを行なった上で並び替え ( Sort Object ) #") | |
| print("# MicrosoftのVBAのリファレンスでは省略可能とされていますが、 #") | |
| print("# 省略すると正常に並び替えされない場合があるので注意が必要。 #") | |
| ws.Activate() | |
| ws.Range("A1").CurrentRegion.Select() | |
| ws.Range("A1").CurrentRegion.AutoFilter() | |
| ws.Range("A1").CurrentRegion.AutoFilter(1, ">30", xlAnd, "<80") | |
| ws.AutoFilter.Sort.SortFields.Clear() | |
| ws.AutoFilter.Sort.SortFields.Add(Key=ws.Range("A1"), SortOn=xlSortOnValues, DataOption=xlSortNormal, Order=xlDescending) | |
| ws.AutoFilter.Sort.SortFields.Add(Key=ws.Range("B1"), SortOn=xlSortOnValues, DataOption=xlSortNormal, Order=xlDescending) | |
| ws.AutoFilter.Sort.SortFields.Add(Key=ws.Range("C1"), SortOn=xlSortOnValues, DataOption=xlSortNormal, Order=xlAscending) | |
| # ws.AutoFilter.Sort.SetRange() | |
| ws.AutoFilter.Sort.Header = xlYes | |
| ws.AutoFilter.Sort.MatchCase = False | |
| ws.AutoFilter.Sort.Orientation = xlSortColumns | |
| ws.AutoFilter.Sort.SortMethod = xlPinYin | |
| ws.AutoFilter.Sort.Apply() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# シートの非表示 #") | |
| ws.Visible = xlSheetHidden | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# シートの表示 #") | |
| ws.Visible = xlSheetVisible | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------ # NO POST # | |
| # NO POST # | |
| ws.Activate() # NO POST # | |
| ws.Range("A1").Select() # NO POST # | |
| # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# シートの保護の設定 #") | |
| ws.Protect() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# シートの保護の解除 #") | |
| ws.Unprotect() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# シートをパスワード付きで保護の設定 #") | |
| ws.Protect(Password="hoge") | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# シートのパスワード付きの保護の解除 #") | |
| ws.Unprotect(Password="hoge") | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# ブックの保護の設定 #") | |
| wb.Protect() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# ブックの保護の解除 #") | |
| wb.Unprotect() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# ブックをパスワード付きで保護の設定 #") | |
| wb.Protect(Password="hoge") | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# ブックのパスワード付きの保護の解除 #") | |
| wb.Unprotect(Password="hoge") | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| # https://learn.microsoft.com/en-us/office/vba/api/excel.windows | |
| # Note that the active window is always Windows(1). | |
| print("# ズームの倍率の設定 #") | |
| ws.Activate() | |
| ws.Range("A1").Select() | |
| ws.Parent.Windows(1).Zoom = 90 | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| # https://excelwork.info/excel/freezepanes/ | |
| # https://stackoverflow.com/questions/43146073/ | |
| print("# 枠の固定 #") | |
| ws.Activate() | |
| ws.Range("C3").Select() | |
| ws.Parent.Windows(1).FreezePanes = True | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| # https://excel-ubara.com/excelvba4/EXCEL272.html | |
| print("# CTRL+HOME的A1セル選択 #") | |
| ws.Activate() | |
| ws.Range("A1").Select() | |
| ws.Application.Goto(ws.Range("A1"), True) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # https://qiita.com/Tachy_Pochy/items/64fe16ec076c52556b2d | |
| print("# CTRL+HOME的選択 ( AutoFilter使用時は残念 ) #") | |
| ws.Activate() | |
| r = int(ws.Parent.Windows(1).SplitRow) + 1 | |
| c = int(ws.Parent.Windows(1).SplitColumn) + 1 | |
| ws.Cells(r, c).Select() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# CTRL+HOME的関数 #") | |
| def SpecialCells_xlHomeCell(ws): | |
| r = int(ws.Parent.Windows(1).SplitRow) + 1 | |
| c = int(ws.Parent.Windows(1).SplitColumn) + 1 | |
| rg = ws.Cells(r, c) | |
| if ws.Parent.Windows(1).FreezePanes and ws.Parent.Windows(1).SplitRow and ws.AutoFilterMode and ws.FilterMode: | |
| rg = rg.GetResize(ws.Cells.Rows.Count - rg.Row + 1, ws.Cells.Columns.Count - rg.Column + 1) | |
| rg = ws.Application.Intersect(ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible), rg) | |
| rg = rg.GetResize(1, 1) | |
| return rg | |
| print("# CTRL+HOME的選択 #") | |
| ws.Activate() | |
| rg = SpecialCells_xlHomeCell(ws) | |
| rg.Select() | |
| ws.Application.Goto(rg, True) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# 再計算 #") | |
| ws.Calculate() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# 再計算 #") | |
| xlApp.Calculate() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# Excelの警告メッセージの表示の停止 #") | |
| xlApp.DisplayAlerts = False | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# Excelの警告メッセージの表示の開始 #") | |
| xlApp.DisplayAlerts = True | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# Excelの画面の更新を停止 #") | |
| xlApp.ScreenUpdating = False | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# Excelの画面の更新を開始 #") | |
| xlApp.ScreenUpdating = True | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # https://stackoverflow.com/questions/3735378/#8561483 | |
| print("# Excelの画面の更新を強制 #") | |
| xlApp.ActiveWindow.SmallScroll() | |
| xlApp.WindowState = xlApp.WindowState | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# ActiveWorkbookの取得 #") | |
| wb = xlApp.ActiveWorkbook | |
| print(wb.Name) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# ActiveWindowの取得 #") | |
| aw = xlApp.ActiveWindow | |
| print(aw.Caption) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# ActiveSheetの取得 #") | |
| ws = xlApp.ActiveSheet | |
| print(ws.Name) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# ActiveSheetの取得 #") | |
| ws = wb.ActiveSheet | |
| print(ws.Name) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# ActiveCellをRangeで取得 #") | |
| rg = xlApp.ActiveCell | |
| print(rg.Address) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# Application オブジェクトの取得 #") | |
| xl = rg.Application | |
| print(xl.Name) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# Application オブジェクトの取得 #") | |
| xl = ws.Application | |
| print(xl.Name) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# Application オブジェクトの取得 #") | |
| xl = wb.Application | |
| print(xl.Name) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# シートの名称の変更 #") | |
| wb.Worksheets("Sheet2").Name = "Sheet9" | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# シートの追加 #") | |
| ws = wb.Worksheets.Add() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# シートの複写 ( 指定シートの前に複写 ) #") | |
| ws.Copy(Before=wb.Worksheets("Sheet9")) | |
| ws = wb.Worksheets(wb.Worksheets("Sheet9").Index - 1) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # https://stackoverflow.com/questions/52685699/ | |
| print("# シートの複写 ( 指定シートの後に複写 ) #") | |
| ws.Copy(Before=None, After=wb.Worksheets("Sheet9")) | |
| ws = wb.Worksheets(wb.Worksheets("Sheet9").Index + 1) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# シートの移動 ( 指定シートの前に移動 ) #") | |
| ws.Move(Before=wb.Worksheets("Sheet9")) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # https://stackoverflow.com/questions/52685699/ | |
| print("# シートの移動 ( 指定シートの後に移動 ) #") | |
| ws.Move(Before=None, After=wb.Worksheets("Sheet9")) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| xlApp.DisplayAlerts = False # NO POST # | |
| # NO POST # | |
| print("# シートの削除 #") | |
| wb.Worksheets("Sheet3").Delete() | |
| wb.Worksheets("Sheet9").Delete() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| ws = wb.Worksheets("Sheet1") # NO POST # | |
| ws.Activate() # NO POST # | |
| xlApp.DisplayAlerts = True # NO POST # | |
| # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# シートをPDF出力 #") | |
| ws.ExportAsFixedFormat(Type=xlTypePDF, Quality=xlQualityStandard, Filename=f"{os.getcwd()}\\output.pdf") | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# ブックをファイルに上書き保存 #") | |
| # wb.Save() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# ブックをXLSXファイルに保存 #") | |
| wb.SaveAs(Filename=f"{os.getcwd()}\\outputSaveAs.xlsx", FileFormat=xlOpenXMLWorkbook) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# ブックをXLSファイルに保存 #") | |
| wb.SaveAs(Filename=f"{os.getcwd()}\\outputSaveAs.xls", FileFormat=xlWorkbookNormal) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# ブックをCSVファイルに保存 #") | |
| wb.SaveAs(Filename=f"{os.getcwd()}\\outputSaveAs.csv", FileFormat=xlCSV) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# ブックをクローズ #") | |
| # wb.Close() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| print("# ブックを保存せずにクローズ #") | |
| wb.Close(SaveChanges=False) | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| print("# Excel終了 #") | |
| xlApp.Quit() | |
| print("press enter key to continue") # NO POST # | |
| input() # NO POST # | |
| # ------------------------------------------------------------------ | |
| # NO POST # | |
| print("press enter key to exit") # NO POST # | |
| input() # NO POST # | |
| if __name__ == "__main__": | |
| main() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment