# 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://wacky.hatenadiary.com/entry/20091011/1255240572
# ------------------------------------------------------------------
# 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 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 XLBordersIndex
# ------------------------------------------------------------------
xlDiagonalDown = 5
xlDiagonalUp = 6
xlEdgeLeft = 7
xlEdgeTop = 8
xlEdgeBottom = 9
xlEdgeRight = 10
xlInsideHorizontal = 12
xlInsideVertical = 11
# ------------------------------------------------------------------
# Excel Enum XLBorderWeight
# ------------------------------------------------------------------
xlHairline = 1
xlThin = 2
xlThick = 4
xlMedium = -4138
# ------------------------------------------------------------------
# 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 XlFixedFormatType
# ------------------------------------------------------------------
xlTypePDF = 0
xlTypeXPS = 1
# ------------------------------------------------------------------
# Excel Enum XlFixedFormatQuality
# ------------------------------------------------------------------
xlQualityStandard = 0
xlQualityMinimum = 1
# ------------------------------------------------------------------
# Excel Enum XlFindLookIn
# ------------------------------------------------------------------
xlFormulas = -4123
xlComments = -4144
xlValues = -4163
# ------------------------------------------------------------------
# Excel Enum XlLineStyle
# ------------------------------------------------------------------
xlContinuous = 1
xlDashDot = 4
xlDashDotDot = 5
xlSlantDashDot = 13
xlDash = -4115
xldot = -4118
xlDouble = -4119
xlLineStyleNone = -4142
# ------------------------------------------------------------------
# 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 XlSheetVisibility
# ------------------------------------------------------------------
xlSheetVisible = -1
xlSheetHidden = 0
xlSheetVeryHidden = 2
# ------------------------------------------------------------------
# Excel Enum XlSpecialCellsValue
# ------------------------------------------------------------------
xlNumbers = 1
xlTextValues = 2
xlLogical = 4
xlErrors = 16
# ------------------------------------------------------------------
# Excel Enum XlSortDataOption
# ------------------------------------------------------------------
xlSortNormal = 0
xlSortTextAsNumbers = 1
# ------------------------------------------------------------------
# Excel Enum XlSortMethod
# ------------------------------------------------------------------
xlPinYin = 1
xlStroke = 2
# ------------------------------------------------------------------
# Excel Enum XlSortOrder
# ------------------------------------------------------------------
xlAscending = 1
xlDescending = 2
xlManual = -4135
# ------------------------------------------------------------------
# Excel Enum XlSortOrientation
# ------------------------------------------------------------------
xlSortColumns = 1
xlSortRows = 2
# ------------------------------------------------------------------
# Excel Enum XlSortOn
# ------------------------------------------------------------------
xlSortOnValues = 0
xlSortOnCellColor = 1
xlSortOnFontColor = 2
xlSortOnIcon = 3
# ------------------------------------------------------------------
# Excel Enum XlSortType
# ------------------------------------------------------------------
xlSortValues = 1
xlSortLabels = 2
# ------------------------------------------------------------------
# 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 any 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 any key to continue" ) # NO POST #
#input() # NO POST #
print( "# Excel表示 #" )
xlApp.Visible = 1
#print( "press any key to continue" ) # NO POST #
#input() # NO POST #
print( "# Excelファイルオープン #" )
wb = xlApp.Workbooks.Open(f"{os.getcwd()}\\sample.csv")
#print( "press any key to continue" ) # NO POST #
#input() # NO POST #
print( "# Excelシートオブジェクト #" )
ws = wb.Worksheets(1)
#print( "press any 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 #
xlApp.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 any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# A1セルを選択 #" )
ws.Range("A1").Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1～B2を選択 #" )
ws.Range("A1:B2").Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1とB2とC3とD4を選択 #" )
ws.Range("A1,B2,C3,D4").Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1～B2とC3～D4を選択 #" )
ws.Range("A1:B2,C3:D4").Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# A1セルを選択 #" )
ws.Cells(1, 1).Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1～B2を選択 #" )
ws.Range(
ws.Cells(1, 1),
ws.Cells(2, 2)
).Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# 全セルを選択 #" )
ws.Cells.Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# 1～2行を選択 #" )
ws.Range("1:2").Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A～B列を選択 #" )
ws.Range("A:B").Select()
print( "press any 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 any 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 any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# 指定範囲の先頭行を選択 #" )
ws.Range("A1:D4").Rows(1).Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# 指定範囲の先頭列を選択 #" )
ws.Range("A1:D4").Columns(1).Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# (A1セル基準で)行全体を選択 #" )
ws.Range("A1").EntireRow.Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# (A1セル基準で)列全体を選択 #" )
ws.Range("A1").EntireColumn.Select()
print( "press any 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 any 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 any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# (A1セル基準で)現在の領域を選択 #" )
ws.Range("A1").CurrentRegion.Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# (現行シートの)使用中領域を選択 #" )
ws.UsedRange.Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# (A1セル基準で)最終右下セル選択 #" )
ws.Range("A1").SpecialCells(xlLastCell).Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# (A1セル基準で)可視状態セル選択 #" )
ws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Select()
print( "press any 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 any 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 any 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 any 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 any 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 any 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 any 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 any 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 any 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 any key to continue" ) # NO POST #
input() # NO POST #
print( "# 指定セルの列数を取得 #" )
n = ws.Range("A1").Column
print( n )
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# 指定範囲に含まれる行数を取得 #" )
n = ws.Range("A1:D4").Rows.Count
print( n )
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# 指定範囲に含まれる列数を取得 #" )
n = ws.Range("A1:D4").Columns.Count
print( n )
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# (A1セル基準で)最終行数を取得 #" )
n = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
print( n )
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# (A1セル基準で)最終列数を取得 #" )
n = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
print( n )
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
# https://binary-star.net/excel-vba-columnchange
print( "# 数字列を英字列に変換 #" )
a = (xlApp.ActiveWorkBook.ActiveSheet.Cells(1, 1).Address).split("$")[1]
print( a )
a = (xlApp.ActiveWorkBook.ActiveSheet.Cells(1, 2).Address).split("$")[1]
print( a )
a = (xlApp.ActiveWorkBook.ActiveSheet.Cells(1, 3).Address).split("$")[1]
print( a )
a = (xlApp.ActiveWorkBook.ActiveSheet.Cells(1, 27).Address).split("$")[1]
print( a )
a = (xlApp.ActiveWorkBook.ActiveSheet.Cells(1, 53).Address).split("$")[1]
print( a )
print( "# 英字列を数字列に変換 #" )
n = xlApp.ActiveWorkBook.ActiveSheet.Cells(1, "A").Column
print( n )
n = xlApp.ActiveWorkBook.ActiveSheet.Cells(1, "B").Column
print( n )
n = xlApp.ActiveWorkBook.ActiveSheet.Cells(1, "C").Column
print( n )
n = xlApp.ActiveWorkBook.ActiveSheet.Cells(1, "AA").Column
print( n )
n = xlApp.ActiveWorkBook.ActiveSheet.Cells(1, "BA").Column
print( n )
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# A1セルに値を設定 #" )
ws.Range("A1").Value = 99999
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルに数式を設定 #" )
ws.Range("A1").Formula = "=(3.14159-3)*100000"
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# A1セルの表示形式を設定 #" )
ws.Range("A1").NumberFormatLocal = "0.00"
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# A1セルの配置の縦位置を設定 #" )
ws.Range("A1").VerticalAlignment = xlCenter
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルの配置の横位置を設定 #" )
ws.Range("A1").HorizontalAlignment = xlCenter
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルの文字列の方向を設定 #" )
ws.Range("A1").Orientation = xlUpward
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルの文字列の方向を設定 #" )
ws.Range("A1").Orientation = 45
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルの文字列の方向を設定 #" )
ws.Range("A1").Orientation = 0
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# A1セルにフォントを設定 #" )
ws.Range("A1").Font.Name = "Yu Gothic UI"
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルのフォントのサイズを設定 #" )
ws.Range("A1").Font.Size = 12
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルのフォントを太字に設定 #" )
ws.Range("A1").Font.Bold = True
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルのフォントを斜体に設定 #" )
ws.Range("A1").Font.Italic = True
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルのフォントに下線を設定 #" )
ws.Range("A1").Font.Underline = xlUnderlineStyleSingle
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルのフォントに取消線を設定 #" )
ws.Range("A1").Font.Strikethrough = True
print( "press any 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 any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルの文字の色を設定(指定順序BGR) #" )
ws.Range("A1").Font.Color = int("FF0000",16)
print( "press any 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 any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルの文字の色を設定(デフォルト) #" )
ws.Range("A1").Font.ColorIndex = xlColorIndexAutomatic
print( "press any 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 any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルの背景の色を設定(指定順序BGR) #" )
ws.Range("A1").Interior.Color = int("FFFF00",16)
print( "press any 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 any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルの背景の色を設定(デフォルト) #" )
ws.Range("A1").Interior.ColorIndex = xlColorIndexNone
print( "press any 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 any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルの罫線を解除 #" )
ws.Range("A1").Borders.LineStyle = xlLineStyleNone
print( "press any 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 any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルの下側の罫線を解除 #" )
ws.Range("A1").Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# A1セルの値や数式を消去 #" )
ws.Range("A1").ClearContents()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルの書式を消去 #" )
ws.Range("A1").ClearFormats()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルを消去 #" )
ws.Range("A1").Clear()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルを削除 #" )
ws.Range("A1").Delete()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルを削除(上方向にシフト) #" )
ws.Range("A1").Delete(xlShiftUp)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルを削除(左方向にシフト) #" )
ws.Range("A1").Delete(xlShiftToLeft)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルに挿入 #" )
ws.Range("A1").Insert()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルに挿入(下方向にシフト) #" )
ws.Range("A1").Insert(xlShiftDown)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルに挿入(右方向にシフト) #" )
ws.Range("A1").Insert(xlShiftToRight)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# (A1行基準で)高さを設定 #" )
ws.Range("A1").RowHeight = 30
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# (A1列基準で)横幅を設定 #" )
ws.Range("A1").ColumnWidth = 30
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# (A1行基準で)高さを自動調整 #" )
ws.Range("A1").EntireRow.AutoFit()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# (A1列基準で)横幅を自動調整 #" )
ws.Range("A1").EntireColumn.AutoFit()
print( "press any 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 any key to continue" ) # NO POST #
input() # NO POST #
print( "# 行のグループ化を非表示 #" )
ws.Outline.ShowLevels(RowLevels = 1)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# 行のグループ化を表示 #" )
ws.Outline.ShowLevels(RowLevels = 8)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# 行のグループ化を解除 #" )
ws.Range("A1").EntireRow.Ungroup()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
xlApp.Goto(ws.Range("A1"), True) # NO POST #
# NO POST #
print( "# 列のグループ化を設定 #" )
ws.Range("A1").EntireColumn.Group()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# 列のグループ化を非表示 #" )
ws.Outline.ShowLevels(RowLevels = 0, ColumnLevels = 1)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# 列のグループ化を表示 #" )
ws.Outline.ShowLevels(RowLevels = 0, ColumnLevels = 8)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# 列のグループ化を解除 #" )
ws.Range("A1").EntireColumn.Ungroup()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
xlApp.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 any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルをSheet2のA1セルに複写 #" )
ws.Range("A1").Copy(wb.Worksheets("Sheet2").Range("A1"))
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セル基準の現在の領域をSheet2のA1セル基準で複写 #" )
ws.Range("A1").CurrentRegion.Copy(wb.Worksheets("Sheet2").Range("A1"))
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルをクリップボードに複写 #" )
ws.Range("A1").Copy()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# クリップボードをB2セルに複写 #" )
ws.Activate()
ws.Range("B2").Select()
ws.Paste()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# クリップボードをC3セルに複写(値の貼り付け) #" )
ws.Range("C3").PasteSpecial(xlPasteValues)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# クリップボードをC3セルに複写(書式貼り付け) #" )
ws.Range("C3").PasteSpecial(xlPasteFormats)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# クリップボードをC3セルに複写(数式貼り付け) #" )
ws.Range("C3").PasteSpecial(xlPasteFormulas)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# 切り取りモードまたはコピー モードを解除 #" )
xlApp.CutCopyMode = False
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# A1セルをB1セルに移動 #" )
ws.Range("A1").Cut(ws.Range("B1"))
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルをSheet2のA1セルに移動 #" )
ws.Range("A1").Cut(wb.Worksheets("Sheet2").Range("A1"))
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A1セルをクリップボードに移動 #" )
ws.Range("A1").Cut()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# 切り取りモードまたはコピー モードを解除 #" )
xlApp.CutCopyMode = False
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# A1～D4の範囲の左側の列の内容を範囲に一括複写 #" )
ws.Range("A1:D4").FillRight()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# E1～H4の範囲の上段の行の内容を範囲に一括複写 #" )
ws.Range("E1:H4").FillDown()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# A5～D8の範囲の右側の列の内容を範囲に一括複写 #" )
ws.Range("A5:D8").FillLeft()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# E5～H8の範囲の下段の行の内容を範囲に一括複写 #" )
ws.Range("E5:H8").FillUp()
print( "press any 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 any 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 any 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 any 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 any 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 any 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 any 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 any key to continue" ) # NO POST #
input() # NO POST #
print( "# AutoFilterの範囲を選択 #" )
ws.AutoFilter.Range.Select()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# AutoFilterの範囲の可視状態セル選択 #" )
ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Select()
print( "press any 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 any 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 any key to continue" ) # NO POST #
input() # NO POST #
print( "# AutoFilterの絞り込み解除 #" )
if ws.FilterMode:
ws.ShowAllData()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# AutoFilterの解除 #" )
if ws.AutoFilterMode:
ws.AutoFilterMode = False
print( "press any 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 any 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 any 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 any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# シートの非表示 #" )
ws.Visible = xlSheetHidden
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# シートの表示 #" )
ws.Visible = xlSheetVisible
print( "press any 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 any key to continue" ) # NO POST #
input() # NO POST #
print( "# シートの保護の解除 #" )
ws.Unprotect()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# シートをパスワード付きで保護の設定 #" )
ws.Protect(Password = "hoge")
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# シートのパスワード付きの保護の解除 #" )
ws.Unprotect(Password = "hoge")
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# ブックの保護の設定 #" )
wb.Protect()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# ブックの保護の解除 #" )
wb.Unprotect()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# ブックをパスワード付きで保護の設定 #" )
wb.Protect(Password = "hoge")
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# ブックのパスワード付きの保護の解除 #" )
wb.Unprotect(Password = "hoge")
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# ズームの倍率の設定 #" )
ws.Activate()
ws.Range("A1").Select()
xlApp.ActiveWindow.Zoom = 90
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
# https://stackoverflow.com/questions/43146073/
print( "# 枠の固定 #" )
ws.Activate()
ws.Range("C3").Select()
xlApp.ActiveWindow.FreezePanes = True
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
# https://qiita.com/Tachy_Pochy/items/64fe16ec076c52556b2d
print( "# CTRL+HOME ( AutoFilter使用時は残念 ) #" )
ws.Activate()
ws.Range("A1").Select()
xlCtrlHomeRow = int(xlApp.ActiveWindow.SplitRow) + int(xlApp.ActiveWindow.Panes(1).ScrollRow) if (1 < xlApp.ActiveWindow.Panes.Count) else 1
xlCtrlHomeColumn = int(xlApp.ActiveWindow.SplitColumn) + int(xlApp.ActiveWindow.Panes(1).ScrollColumn) if (1 < xlApp.ActiveWindow.Panes.Count) else 1
ws.Cells(xlCtrlHomeRow, xlCtrlHomeColumn).Select()
print( "press any 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()
xlApp.Goto(ws.Range("A1"), True)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# 再計算 #" )
xlApp.Calculate()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# Excelの警告メッセージの表示の停止 #" )
xlApp.DisplayAlerts = False
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# Excelの警告メッセージの表示の開始 #" )
xlApp.DisplayAlerts = True
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# Excelの画面の更新を停止 #" )
xlApp.ScreenUpdating = False
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# Excelの画面の更新を開始 #" )
xlApp.ScreenUpdating = True
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# シートの名称の変更 #" )
wb.Worksheets("Sheet2").Name = "Sheet9"
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# シートの追加 #" )
xlApp.Worksheets.Add()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# シートの複写 ( 指定シートの前に複写 ) #" )
ws.Copy(Before = wb.Worksheets("Sheet9"))
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# https://stackoverflow.com/questions/52685699/
print( "# シートの複写 ( 指定シートの後に複写 ) #" )
ws.Copy(Before = None, After = wb.Worksheets("Sheet9"))
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# シートの移動 ( 指定シートの前に移動 ) #" )
ws.Move(Before = wb.Worksheets("Sheet9"))
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# https://stackoverflow.com/questions/52685699/
print( "# シートの移動 ( 指定シートの後に移動 ) #" )
ws.Move(Before = None, After = wb.Worksheets("Sheet9"))
print( "press any key to continue" ) # NO POST #
input() # NO POST #
xlApp.DisplayAlerts = False # NO POST #
# NO POST #
print( "# シートの削除 #" )
wb.Worksheets("Sheet9").Delete()
print( "press any key to continue" ) # NO POST #
input() # 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 any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# ブックをファイルに上書き保存 #" )
#wb.Save()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# ブックをXLSXファイルに保存 #" )
wb.SaveAs(f"{os.getcwd()}\\outputSaveAs.xlsx", FileFormat = xlOpenXMLWorkbook)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# ブックをXLSファイルに保存 #" )
wb.SaveAs(f"{os.getcwd()}\\outputSaveAs.xls", FileFormat = xlWorkbookNormal)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# ブックをCSVファイルに保存 #" )
wb.SaveAs(f"{os.getcwd()}\\outputSaveAs.csv", FileFormat = xlCSV)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# ブックをクローズ #" )
#wb.Close()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
print( "# ブックを保存せずにクローズ #" )
wb.Close(SaveChanges = False)
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "# Excel終了 #" )
xlApp.Quit()
print( "press any key to continue" ) # NO POST #
input() # NO POST #
# ------------------------------------------------------------------
print( "press any key to exit" ) # NO POST #
input() # NO POST #
# NO POST #
if __name__ == "__main__":
main()
扫一扫
评论
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
查看更多评论
添加红包
