A non-programmer amateur To let Python do the boring things It is a memorandum when considering pywin32.
Since it is a confirmation with a narrow usage range and a low usage frequency, There may be mistakes or misunderstandings in the description. Please refer to the following contents at your own risk.
The method argument is Similar to C ++ default arguments It seems that it is not possible to omit the argument in the middle.
How to operate Excel from pywin32 using COM https://sites.google.com/site/pythoncasestudy/home/pywin32kara-comwo-tsuka-tsu-te-excelwo-sousa-suru-houhou Operate Excel from Python with win32com https://qiita.com/kumarstack55/items/9ae3432446afca06497f Operate Excel (pywin32: win32com) https://excel-ubara.com/python/python025.html
The essence of excel https://excel-ubara.com/ Instructor's storybook https://www.relief.jp/ Office TANAKA - Excel VBA http://officetanaka.net/excel/vba/ Yone's Word and Excel small room http://www.eurus.dti.ne.jp/~yoneyama/
# coding:utf-8
import os
import win32com.client
import win32con
import win32gui
def main():
# ------------------------------------------------------------------
#Set Excel constants
# ------------------------------------------------------------------
# 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)
# ------------------------------------------------------------------
#There seems to be a way to get Excel constants.
# 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
# ------------------------------------------------------------------
#Start Excel
xlApp = win32com.client.Dispatch("Excel.Application")
# https://stackoverflow.com/questions/2790825/
#Excel Window Maximization
win32gui.ShowWindow(xlApp.hwnd, win32con.SW_MAXIMIZE)
#Excel display
xlApp.Visible = 1
#Excel file open
wb = xlApp.Workbooks.Open(f"{os.getcwd()}\\sample.csv")
#Excel sheet object
ws = wb.Worksheets(1)
# ------------------------------------------------------------------
#Select the specified sheet
# Select()Activate the sheet before using()Is necessary
ws.Activate()
# ------------------------------------------------------------------
#Select cell A1
ws.Range("A1").Select()
#Select A1 to B2
ws.Range("A1:B2").Select()
#Select A1, B2, C3 and D4
ws.Range("A1,B2,C3,D4").Select()
#Select A1 to B2 and C3 to D4
ws.Range("A1:B2,C3:D4").Select()
# ------------------------------------------------------------------
#Select cell A1
ws.Cells(1, 1).Select()
#Select A1 to B2
ws.Range(
ws.Cells(1, 1),
ws.Cells(2, 2)
).Select()
# ------------------------------------------------------------------
#Select all cells
ws.Cells.Select()
# ------------------------------------------------------------------
#Select 1-2 lines
ws.Range("1:2").Select()
#Select columns A to B
ws.Range("A:B").Select()
# https://www.relief.jp/docs/excel-vba-difference-range-rows-columns.html
#Select 1-2 lines
ws.Rows("1:2").Select()
# https://www.relief.jp/docs/excel-vba-difference-range-columns.html
#Select columns A to B
ws.Columns("A:B").Select()
# ------------------------------------------------------------------
#Select the first line of the specified range
ws.Range("A1:D4").Rows(1).Select()
#Select the first column of the specified range
ws.Range("A1:D4").Columns(1).Select()
# ------------------------------------------------------------------
# (Based on A1 cell)Select entire line
ws.Range("A1").EntireRow.Select()
# (Based on A1 cell)Select entire column
ws.Range("A1").EntireColumn.Select()
# ------------------------------------------------------------------
# (Based on A1 cell)Select the row up to the last column
ws.Range(
ws.Range("A1"),
ws.Cells(1, ws.Columns.Count).End(xlToLeft)
).Select()
# (Based on A1 cell)Select columns up to the last row
ws.Range(
ws.Range("A1"),
ws.Cells(ws.Rows.Count, 1).End(xlUp)
).Select()
# ------------------------------------------------------------------
# (Based on A1 cell)Select current area
ws.Range("A1").CurrentRegion.Select()
# (Of the current sheet)Select the area in use
ws.UsedRange.Select()
# (Based on A1 cell)Final lower right cell selection
ws.Range("A1").SpecialCells(xlLastCell).Select()
# (Based on A1 cell)Visible state cell selection
ws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Select()
# ------------------------------------------------------------------
# https://thecodingforums.com/threads/328174/
# Range.Offset()Get Offset for Property()Method
#Offset the specified range
ws.Range("A1:D4").GetOffset(2, 2).Select()
# Range.Offset()Get Offset for Property()Method
#Offset the specified range
ws.Range("A1:D4").GetOffset(RowOffset = 3, ColumnOffset = 3).Select()
# Range.Offset()Get Offset for Property()Method
#Offset the specified range vertically
ws.Range("A1:D4").GetOffset(RowOffset = 3).Select()
# Range.Offset()Get Offset for Property()Method
#Offset the specified range horizontally
ws.Range("A1:D4").GetOffset(RowOffset = 0, ColumnOffset = 3).Select()
# ------------------------------------------------------------------
# https://stackoverflow.com/questions/63112880/
# Range.Resize()Get Resize for Property()Method
#Resize the specified range
ws.Range("A1:H8").GetResize(2, 2).Select()
# Range.Resize()Get Resize for Property()Method
#Resize the specified range
ws.Range("A1:H8").GetResize(RowSize = 3, ColumnSize = 3).Select()
# Range.Resize()Get Resize for Property()Method
#Resize the vertical direction of the specified range
ws.Range("A1:H8").GetResize(RowSize = 3).Select()
# Range.Resize()Get Resize for Property()Method
#Resize the horizontal direction of the specified range
ws.Range("A1:H8").GetResize(RowSize = ws.Range("A1:H8").Rows.Count, ColumnSize = 3).Select()
# ------------------------------------------------------------------
#Get the number of rows in the specified cell
n = ws.Range("A1").Row
print( n )
#Get the number of columns in the specified cell
n = ws.Range("A1").Column
print( n )
#Get the number of lines included in the specified range
n = ws.Range("A1:D4").Rows.Count
print( n )
#Get the number of columns included in the specified range
n = ws.Range("A1:D4").Columns.Count
print( n )
# (Based on A1 cell)Get the number of last lines
n = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
print( n )
# (Based on A1 cell)Get the number of last columns
n = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
print( n )
# ------------------------------------------------------------------
# https://binary-star.net/excel-vba-columnchange
#Convert a string of numbers to a string of letters
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 )
#Convert alphabetic strings to numeric strings
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 )
# ------------------------------------------------------------------
#Set value in cell A1
ws.Range("A1").Value = 99999
#Set formula in cell A1
ws.Range("A1").Formula = "=(3.14159-3)*100000"
# ------------------------------------------------------------------
#Set the display format of cell A1
ws.Range("A1").NumberFormatLocal = "0.00"
# ------------------------------------------------------------------
#Set the vertical position of A1 cell placement
ws.Range("A1").VerticalAlignment = xlCenter
#Set the horizontal position of A1 cell placement
ws.Range("A1").HorizontalAlignment = xlCenter
#Set the direction of the character string in cell A1
ws.Range("A1").Orientation = xlUpward
#Set the direction of the character string in cell A1
ws.Range("A1").Orientation = 45
#Set the direction of the character string in cell A1
ws.Range("A1").Orientation = 0
# ------------------------------------------------------------------
#Set font in cell A1
ws.Range("A1").Font.Name = "Yu Gothic UI"
#Set font size for cell A1
ws.Range("A1").Font.Size = 12
#Set the font of cell A1 to bold
ws.Range("A1").Font.Bold = True
#Set font in cell A1 to italics
ws.Range("A1").Font.Italic = True
#Underline the font in cell A1
ws.Range("A1").Font.Underline = xlUnderlineStyleSingle
#Set strikethrough for A1 cell font
ws.Range("A1").Font.Strikethrough = True
# ------------------------------------------------------------------
#Set the text color of cell A1(R+Gx256+Bx256x256)
ws.Range("A1").Font.Color = 255 + 0*256 + 0*256*256
#Set the text color of cell A1(Specified order BGR)
ws.Range("A1").Font.Color = int("FF0000",16)
#Set the text color of cell A1(Specified order RGB)
ws.Range("A1").Font.Color = int("".join(list(reversed(["FF0000"[i: i+2] for i in range(0, 6, 2)]))),16)
#Set the text color of cell A1(Default)
ws.Range("A1").Font.ColorIndex = xlColorIndexAutomatic
# ------------------------------------------------------------------
#Set the background color of cell A1(R+Gx256+Bx256x256)
ws.Range("A1").Interior.Color = 255 + 255*256 + 0*256*256
#Set the background color of cell A1(Specified order BGR)
ws.Range("A1").Interior.Color = int("FFFF00",16)
#Set the background color of cell A1(Specified order RGB)
ws.Range("A1").Interior.Color = int("".join(list(reversed(["FFFF00"[i: i+2] for i in range(0, 6, 2)]))),16)
#Set the background color of cell A1(Default)
ws.Range("A1").Interior.ColorIndex = xlColorIndexNone
# ------------------------------------------------------------------
#Set a ruled line in cell 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
#Remove the border of cell A1
ws.Range("A1").Borders.LineStyle = xlLineStyleNone
#Set a ruled line at the bottom of cell 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
#Remove the ruled line at the bottom of cell A1
ws.Range("A1").Borders(xlEdgeBottom).LineStyle = xlLineStyleNone
# ------------------------------------------------------------------
#Clear values and formulas in cell A1
ws.Range("A1").ClearContents()
#Erase A1 cell format
ws.Range("A1").ClearFormats()
#Erase cell A1
ws.Range("A1").Clear()
#Delete cell A1
ws.Range("A1").Delete()
#Delete cell A1(Shift upwards)
ws.Range("A1").Delete(xlShiftUp)
#Delete cell A1(Shift to the left)
ws.Range("A1").Delete(xlShiftToLeft)
#Insert in cell A1
ws.Range("A1").Insert()
#Insert in cell A1(Shift down)
ws.Range("A1").Insert(xlShiftDown)
#Insert in cell A1(Shift to the right)
ws.Range("A1").Insert(xlShiftToRight)
# ------------------------------------------------------------------
# (A1 line standard)Set height
ws.Range("A1").RowHeight = 30
# (Based on A1 column)Set width
ws.Range("A1").ColumnWidth = 30
# (A1 line standard)Automatic height adjustment
ws.Range("A1").EntireRow.AutoFit()
# (Based on A1 column)Width is automatically adjusted
ws.Range("A1").EntireColumn.AutoFit()
# ------------------------------------------------------------------
#How to show and hide individual groups
# 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/
#Set row grouping
ws.Range("A1").EntireRow.Group()
#Hide row grouping
ws.Outline.ShowLevels(RowLevels = 1)
#Show row grouping
ws.Outline.ShowLevels(RowLevels = 8)
#Ungroup rows
ws.Range("A1").EntireRow.Ungroup()
#Set column grouping
ws.Range("A1").EntireColumn.Group()
#Hide column grouping
ws.Outline.ShowLevels(RowLevels = 0, ColumnLevels = 1)
#Show column grouping
ws.Outline.ShowLevels(RowLevels = 0, ColumnLevels = 8)
#Ungroup columns
ws.Range("A1").EntireColumn.Ungroup()
# ------------------------------------------------------------------
#Copy cell A1 to cell B1
ws.Range("A1").Copy(ws.Range("B1"))
#Copy cell A1 to cell A1 of Sheet2
ws.Range("A1").Copy(wb.Worksheets("Sheet2").Range("A1"))
#Copy the current area of A1 cell reference to Sheet2 A1 cell reference
ws.Range("A1").CurrentRegion.Copy(wb.Worksheets("Sheet2").Range("A1"))
#Copy cell A1 to clipboard
ws.Range("A1").Copy()
#Copy clipboard to cell B2
ws.Activate()
ws.Range("B2").Select()
ws.Paste()
#Copy clipboard to cell C3(Paste value)
ws.Range("C3").PasteSpecial(xlPasteValues)
#Copy clipboard to cell C3(Paste format)
ws.Range("C3").PasteSpecial(xlPasteFormats)
#Copy clipboard to cell C3(Paste formula)
ws.Range("C3").PasteSpecial(xlPasteFormulas)
#Cancel cut mode or copy mode
xlApp.CutCopyMode = False
# ------------------------------------------------------------------
#Move cell A1 to cell B1
ws.Range("A1").Cut(ws.Range("B1"))
#Move cell A1 to cell A1 of Sheet2
ws.Range("A1").Cut(wb.Worksheets("Sheet2").Range("A1"))
#Move cell A1 to clipboard
ws.Range("A1").Cut()
#Cancel cut mode or copy mode
xlApp.CutCopyMode = False
# ------------------------------------------------------------------
#Batch copy the contents of the left column of the range A1 to D4 to the range
ws.Range("A1:D4").FillRight()
#Batch copy the contents of the upper line in the range of E1 to H4 to the range
ws.Range("E1:H4").FillDown()
#Batch copy the contents of the right column in the range of A5 to D8 to the range
ws.Range("A5:D8").FillLeft()
#Batch copy the contents of the lower line in the range of E5 to H8 to the range
ws.Range("E5:H8").FillUp()
# ------------------------------------------------------------------
#AutoFill from A1 to H1 to A1 to H8
ws.Range("A1:H1").AutoFill(ws.Range("A1:H8"))
#AutoFill the range from A1 to H2 to the range from A1 to H8
ws.Range("A1:H2").AutoFill(ws.Range("A1:H8"), xlFillDefault)
# ------------------------------------------------------------------
#AutoFilter the current area relative to the A1 cell
ws.Range("A1").CurrentRegion.AutoFilter()
ws.Range("A1").CurrentRegion.AutoFilter(Field = 1, Criteria1 = ">30")
#AutoFilter the current area relative to the A1 cell
ws.Range("A1").CurrentRegion.AutoFilter()
ws.Range("A1").CurrentRegion.AutoFilter(Field = 1, Criteria1 = ">30", Operator = xlAnd, Criteria2 = "<80")
#AutoFilter the current area relative to the A1 cell
ws.Range("A1").CurrentRegion.AutoFilter()
ws.Range("A1").CurrentRegion.AutoFilter(1, ">30")
#AutoFilter the current area relative to the A1 cell
ws.Range("A1").CurrentRegion.AutoFilter()
ws.Range("A1").CurrentRegion.AutoFilter(1, ">30", xlAnd, "<80")
#AutoFilter the current area relative to the A1 cell
ws.Range("A1").CurrentRegion.AutoFilter()
ws.Range("A1").CurrentRegion.AutoFilter(1, ">30", xlAnd, "<80")
ws.Range("A1").CurrentRegion.AutoFilter(2, ">40", xlAnd, "<60")
#Select the range of AutoFilter
ws.AutoFilter.Range.Select()
#Visible cell selection in the range of AutoFilter
ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Select()
#Number of visible cell rows in AutoFilter range
n = int(ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count / ws.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Columns.Count)
print( n )
#Applying AutoFilter
ws.AutoFilter.ApplyFilter()
#Unfiltering AutoFilter
if ws.FilterMode:
ws.ShowAllData()
#Cancel AutoFilter
if ws.AutoFilterMode:
ws.AutoFilterMode = False
# ------------------------------------------------------------------
#Sort current area based on A1 cell( Range Sort Method )
#If Type is omitted, the specification of Order2 etc. after Type is ignored.
#If Type is set to None, no error will occur, but it will malfunction.
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,
)
#Sort current area based on A1 cell( Sort Object )
#Although optional in Microsoft's VBA reference,
#Note that if omitted, it may not be sorted normally.
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()
# ------------------------------------------------------------------
#Sort after performing AutoFilter( Sort Object )
#Although optional in Microsoft's VBA reference,
#Note that if omitted, it may not be sorted normally.
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()
# ------------------------------------------------------------------
#Hide sheet
ws.Visible = xlSheetHidden
#Display of sheet
ws.Visible = xlSheetVisible
# ------------------------------------------------------------------
#Sheet protection settings
ws.Protect()
#Release of sheet protection
ws.Unprotect()
#Set protection of sheet with password
ws.Protect(Password = "hoge")
#Remove password protection for sheet
ws.Unprotect(Password = "hoge")
# ------------------------------------------------------------------
#Book protection settings
wb.Protect()
#Unprotecting the book
wb.Unprotect()
#Protect your workbook with a password
wb.Protect(Password = "hoge")
#Remove password protection for workbooks
wb.Unprotect(Password = "hoge")
# ------------------------------------------------------------------
#Zoom magnification setting
ws.Activate()
ws.Range("A1").Select()
xlApp.ActiveWindow.Zoom = 90
# ------------------------------------------------------------------
# https://stackoverflow.com/questions/43146073/
#Fixing the frame
ws.Activate()
ws.Range("C3").Select()
xlApp.ActiveWindow.FreezePanes = True
# ------------------------------------------------------------------
# https://qiita.com/Tachy_Pochy/items/64fe16ec076c52556b2d
# CTRL+HOME (Sorry when using 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()
# https://excel-ubara.com/excelvba4/EXCEL272.html
# CTRL+HOME-like A1 cell selection
ws.Activate()
ws.Range("A1").Select()
xlApp.Goto(ws.Range("A1"), True)
# ------------------------------------------------------------------
#Recalculation
xlApp.Calculate()
#Stop displaying Excel warning messages
xlApp.DisplayAlerts = False
#Start displaying Excel warning messages
xlApp.DisplayAlerts = True
#Stop updating the Excel screen
xlApp.ScreenUpdating = False
#Start updating the Excel screen
xlApp.ScreenUpdating = True
# ------------------------------------------------------------------
#Change the name of the sheet
wb.Worksheets("Sheet2").Name = "Sheet9"
#Add sheet
xlApp.Worksheets.Add()
#Copy of sheet(Copy before the designated sheet)
ws.Copy(Before = wb.Worksheets("Sheet9"))
# https://stackoverflow.com/questions/52685699/
#Copy of sheet(Copy after designated sheet)
ws.Copy(Before = None, After = wb.Worksheets("Sheet9"))
#Moving the seat(Move to the front of the specified sheet)
ws.Move(Before = wb.Worksheets("Sheet9"))
# https://stackoverflow.com/questions/52685699/
#Moving the seat(Move after the specified sheet)
ws.Move(Before = None, After = wb.Worksheets("Sheet9"))
#Delete sheet
wb.Worksheets("Sheet9").Delete()
# ------------------------------------------------------------------
#PDF output sheet
ws.ExportAsFixedFormat(Type = xlTypePDF, Quality = xlQualityStandard, Filename = f"{os.getcwd()}\\output.pdf")
# ------------------------------------------------------------------
#Save the workbook to a file
wb.Save()
#Save the workbook to an XLSX file
wb.SaveAs(f"{os.getcwd()}\\outputSaveAs.xlsx", FileFormat = xlOpenXMLWorkbook)
#Save workbook to XLS file
wb.SaveAs(f"{os.getcwd()}\\outputSaveAs.xls", FileFormat = xlWorkbookNormal)
#Save the workbook to a CSV file
wb.SaveAs(f"{os.getcwd()}\\outputSaveAs.csv", FileFormat = xlCSV)
# ------------------------------------------------------------------
#Close the book
wb.Close()
#Close without saving the book
wb.Close(SaveChanges = False)
# ------------------------------------------------------------------
#Excel exit
xlApp.Quit()
# ------------------------------------------------------------------
if __name__ == "__main__":
main()
Recommended Posts