Read an Excel sheet and loop it line by line Python VBA

This is a memo for myself when reading data from Excel and scraping

Processing image * Source is easy for beginners ■ There are about 300 stores ■ Acquire or update some data for each store ↓ For that Read various information such as store number, store code, URL, login ID in Excel Loop with the read content (process line by line)

Load Excel sheet in Python

• Processing image: List the settings and loop line by line.

Load the excel setting sheet using openpyxl

import openpyxl　　　　#Make openpyxl available
wb=openpyxl.load_workbook("test.xlsx")
sheet=wb["Setting"]    #シート名はSetting
C=[]
for row in sheet:
　C.append([col.value for col in row])

#Process the contents of the Excel sheet in a loop
#C starts from 0. The first line of the setting sheet is a heading, so the loop starts from 1.
for i in range(1,len(C)):
 print(C[i][0])
 print(C[i][1])

• C has the contents of the setting sheet in the list. Get each line of Excel in a loop and process it. ※ ** Make sure the setting sheet is a clean table starting with A1. Then you can loop cleanly. ** **

Read Excel sheet with VBA

It's a little complicated, but it's flexible. Read the contents of the setting sheet into the Variant type array, line by line Loop processing

Read the contents of the Excel setting sheet and loop processing line by line

Dim CRastRow As Long                         'Judgment by column A in the last row of the setting sheet
Dim i as Long
Dim C As Variant
'Read the last row of column A of the settings sheet
CRastRow = ThisWorkbook.Sheets("Setting").Cells(Rows.Count, 1).End(xlUp).Row  

'Since the first row is often a heading, it is assumed that A2 to the last row of column G are read.
C = ThisWorkbook.Sheets("Setting").Range("A2:G" & CRastRow)

'Loops start from 1 and process line by line
For i = 1 To UBound(C)
    Debug.Print(C(i,1))’A row
    Debug.Print(C(i,2)’B column
    
next i