はじめに
ノンプログラマーの素人が記述をしたコードです。
狭い利用範囲と少ない利用頻度での確認ですので、
記載内容に間違いや勘違いがあるかもしれません。
下記内容を参照の際は自己責任でお願い致します。
名前付き引数と省略可能な引数
メソッドの引数は、
C++のデフォルト引数と同様、
途中の引数を省略することができなさそうです。
pywin32(win32com)の参考にさせて頂いたサイト
https://excel-ubara.com/python/python025.html
https://qiita.com/kumarstack55/items/9ae3432446afca06497f
ExcelVBAの参考にさせて頂いたサイト
https://excel-ubara.com/
http://www.eurus.dti.ne.jp/~yoneyama/
Pythonのpywin32(win32com)でExcelPivotTable操作備忘録
https://feo52.hatenablog.jp/entry/2023/05/07/080000#main
https://feo52.hatenablog.jp/entry/2023/05/07/080000#code
Pythonのpywin32(win32com)でExcel操作備忘録サンプル
sample.csv
sample.py
sample.with.print.and.input.py
Pythonのpywin32(win32com)でExcel操作備忘録
sample.csv
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
sample.with.print.and.input.py
# 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()
sample.py
# 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()