What I read about VBA x Python fastest work technique Memo chapter3

■ Before writing a program ...

★ This time I use Visual Studio Code

Before writing a program, first identify the work procedure. ex) I want to extract a part of the Excel sales slip data saved on the server and post it to the system sales management system. Step 1 / Open the workbook in the specified folder Step 2 / Select the data of a part of the sales slip sheet Step 3 / Discharge as CSV data

• In the abbreviations of the program, to is abbreviated as 2 and puns are used.

■ Library used

import pathlib ⇒ Standard library. Make file and folder paths easier to handle in your program import openpyxl ⇒ external library import SCV ⇒ Standard library. Used when outputting a csv file

** About the library ** The standard library is installed when python is installed. The files in the Lib folder are standard modules External libraries need to be installed separately.

** What is a package ... ** A collection of multiple modules such as the python module <○○○ .py>. Collected in folders ** What is a module ... ** Consists of a single python file. Several

** What is a CSV file? ** Abbreviation for Comma Separated Value (comma separated value) The extension is .csv and it becomes a text file. It can be opened with a text editor such as Notepad or Visual Studio Code. Data can be imported / output using Excel, Access, server database, etc.

■ Code explanation / summary

1wb = openpvx1. ** workbook () ** Variable to create a new workbook 1wb = openpvx1. ** active ** Variable to get active sheet path = pathlib. ** path (".. \ data \ slaes") ** Variable that specifies the path

for pass_obj in path.interdir():

• ".." is a relative path, and the path is specified with the current folder as the base point.

** pass_obj.match ("* .xlsx"): ** Operation to check if it is an Excel file wb = openpyxl. ** load_workbook (pass_obj) ** Operation to read workbook ** dt_row in range (9,19): ** Specify the start and end of the cell row (Note that the value is not returned in the 19th row)

• About Excel range specification Two types, range and cell range (column, row) cell (row, column)

In Excel, there is a way to specify A1 or G2 when setting the cell address, In python, specify in the order of row number → column number. Also, column numbers often use numbers instead of alphabets.

\ n⇒ Meaning of line breaks

• ⇒ Wildcard example) pass_obj.match ("* .xlsx"):

■ if conditional branch

A function that can divide processing depending on whether or not a certain condition is satisfied. Set the <~ to> statement when the condition is met (true = true).

if function

if conditional expression:* Add a colon at the end
Statement 1
Statement 2

• None = nothing

if function

if pass_obj.match("*.xlsx"):
            wb = openpyxl.load_workbook(pass_obj)
            for sh in wb:
                for dt_row in range(1,1):
                    if sh.cell(dt_row, 1).value != None:← This

else Example) When setting "Pass" if the answer is correct and "Fail" if the answer is incorrect for a specific condition

if function(use else)

score = 100
If score => 100:
     print("Pass")
else:
     print("failure")

if function(use else)

score = 100
If socore => 100:
     print("Pass")
else:
     print("failure")