Python pywin32 (win32com) Excel operation memorandum

Introduction

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.

Named and optional arguments

The method argument is Similar to C ++ default arguments It seems that it is not possible to omit the argument in the middle.

The site that I used as a reference for pywin32 (win32com)

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 site that I used as a reference for Excel VBA

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/

Excel operation memorandum with pywin32 (win32com) of Python

# 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()

Excel operation memorandum sample with pywin32 (win32com) of Python

sample.py sample.csv

Recommended Posts

Python pywin32 (win32com) Excel operation memorandum
Python pywin32 (win32com) Excel Range.Offset Range.Resize Range.Address
Python memorandum
Python Memorandum 2
Python memorandum
python memorandum
python memorandum
Python memorandum
python memorandum
Python memorandum
Python basics memorandum
Python pathlib memorandum
[python] vector operation
Python memorandum (algorithm)
Python OS operation
[Python] Matrix operation
Pandas operation memorandum
Python memorandum [links]
Excel with Python
python memorandum (sequential update)
Run python from excel
Python memorandum (personal bookmark)
Handle Excel with python
Python basic memorandum part 2
Python directory operation summary
Python logical operation stumbling
Python programming in Excel
[Python] Iterative processing_Personal memorandum
Python decorator operation memo
Memorandum @ Python OR Seminar
python memorandum super basic
Operate Excel with Python (1)
[python] Array slice operation
Operate Excel with Python (2)
Effective Python Learning Memorandum Day 15 [15/100]
Cisco Memorandum _ Python config input
Effective Python Learning Memorandum Day 6 [6/100]
Let's run Excel with Python
Effective Python Learning Memorandum Day 12 [12/100]
Effective Python Learning Memorandum Day 9 [9/100]
Effective Python Learning Memorandum Day 8 [8/100]
S3 operation with python boto3
ABC memorandum [ABC163 C --managementr] (Python)
About python beginner's memorandum function
Memorandum @ Python OR Seminar: matplotlib
[Python] SQLAlchemy error avoidance memorandum
A memorandum about correlation [Python]
Effective Python Learning Memorandum Day 14 [14/100]
Effective Python Learning Memorandum Day 1 [1/100]
Memorandum @ Python OR Seminar: Pulp
Effective Python Learning Memorandum Day 13 [13/100]
A memorandum about Python mock
[Python, Excel] Eliminate cell merging
Effective Python Learning Memorandum Day 3 [3/100]
Effective Python Learning Memorandum Day 5 [5/100]
Memorandum @ Python OR Seminar: Pandas
Aim python library master (12) excel
[python] Random number generation memorandum
Effective Python Learning Memorandum Day 4 [4/100]
Memorandum @ Python OR Seminar: scikit-learn
Effective Python Learning Memorandum Day 7 [7/100]