Read Excel name / cell range with Python VBA

Read the cell range defined by the name of Excel Python VBA

Working image

1, There is information such as start date / end date, selection items in pull-down in the cell range defined by the name in Excel 2, The range defined by the name of Excel is about 2.3 lines 3, Read a large amount of data such as iterative processing for each store from the sheet Previous memo 4, python uses OpenPyxL module 5, Data required for scraping will be read from Excel

Read a range of cells defined by the name of excel in python

The name of Excel is "test name range" The Excel file name is "test.xlsx" Use book-level names (so that they don't have the same name at sheet level)

Get a range of cells with an excel name


import openpyxl
wb=openpyxl.load_workbook('test.xlsx')
my_range = wb.defined_names['Test name range']
dests = my_range.destinations
cells = [] 
for title, coord in dests:  #The sheet name is taken in the title.
	ws = wb[title]
	cells.append(ws[coord]) 
 C=[]
 for row in cells:
     for aa in row:
         C.append([col.value for col in aa])
        

Since C is a list of lists, the upper left cell of the Excel name range is It can be used with C [0] [0]. This is fine when the name range is small.

Read a range of cells defined by name in Excel VBA

If you put a cell range in a Variant type variable, you can use it like C (1,1). Unlike python, it starts from 1. Excel often opens many files at the same time and the operation is messed up. It is safer to specify by book → sheet → name.

Get the definition of the name of excel



Dim C As Variant
'The menu sheet has a test name range
C = ThisWorkbook.Sheets("menu").Range("Test name range")

You can get the value of the upper left cell with C (1, 1).

If you can read the name of Excel in detail, scraping operation will be easier.

Recommended Posts

Read Excel name / cell range with Python VBA
Excel with Python
Run Python with VBA
[Python] How to read excel file with pandas
Read excel with openpyxl
Operate Excel with Python (1)
Operate Excel with Python (2)
Run Python from Excel VBA with xlwings & tutorial supplement
Read csv with python pandas
[Python, Excel] Eliminate cell merging
Read json data with python
Introduction to Python for VBA users-Calling Python from Excel with xlwings-
How to read an Excel file (.xlsx) with Pandas [Python]
solver> Link> Solve Excel Solver with python
Create an Excel file with Python3
Let's play with Excel with Python [Beginner]
[python] Read information with Redmine API
Handle Excel CSV files with Python
Read files in parallel with Python
Read fbx from python with cinema4d
Excel aggregation with Python pandas Part 1
Read an Excel sheet and loop it line by line Python VBA
[Python] Set the graph range with matplotlib
Read CSV file with python (Download & parse CSV file)
Uncle SES modernizes VBA app with Python
Draw Nozomi Sasaki in Excel with python
[Python] Get the variable name with str
Quickly create an excel file with Python #python
[C] [python] Read with AquesTalk on Linux
Let's read the RINEX file with Python ①
Excel aggregation with Python pandas Part 2 Variadic
[Easy Python] Reading Excel files with openpyxl
Create Excel file with Python + similarity matrix
[Python] Read images with OpenCV (for beginners)
[Automation] Read a Word document with Python
[Easy Python] Reading Excel files with pandas
Excel table creation with Python [Progress management table]
Python / numpy> Read the data file with the item name line> Use genfromtxt ()
Read text in images with python OCR
[Automation] Read mail (msg file) with Python
For those who want to learn Excel VBA and get started with Python
De-VBA Excel tool! Eliminate VBA tool with Python in environment where Python cannot be installed
How to read a CSV file with Python 2/3
Read data with python / netCDF> nc.variables [] / Check data size
(Note) Importing Excel with the same column name
Read Python csv data with Pandas ⇒ Graph with Matplotlib
Read JSON with Python and output as CSV
Medical image analysis with Python 1 (Read MRI image with SimpleITK)
Get git branch name and tag name with python
[Python3] Read and write with datetime isoformat with json
Extract zip with Python (Japanese file name support)
Read line by line from a file with Python
Read wav files with only Python standard packages
Send an email with Excel attached in Python
Materials to read when getting started with Python
FizzBuzz with Python3
Scraping with Python
Statistics with python
Scraping with Python
Python with Go
Twilio with Python