-1

I am trying to extract data from workbooks that are already open.

I have found the xlrd library, but it appears you can only use this with workbooks you open through Python. The workbooks I will use in my project have already been opened, so this method is unusable.

A second library I found, which is OpenPyxl, only returns errors for me, even though the workbook is open: 

from openpyxl import load_workbook

wb = load_workbook(filename = 'Components V2.4.3.xlsm')

returns:

FileNotFoundError: [Errno 2] No such file or directory: 'Components V2.4.3.xlsm'

Lastly, I have used win32com.client's Dispatch which I could not get cell values from, hence why I am looking for an alternative.

Am I doing something wrong with openpyxl, or is there another method I can use?

Tim Stack
  • 3,209
  • 3
  • 18
  • 39
  • can you pass a link with one workbook example? – kederrac Aug 19 '19 at 21:20
  • what do you mean "The workbook is open though"? Do you mean the spreadsheet is open in Excel? Have you tried to read the spreadsheets while Excel is closed? Does the spreadsheet need to be "open" ? – SomeGuyOnAComputer Aug 20 '19 at 03:34
  • @SomeGuyOnAComputer Exactly, an instance of Excel is open with the spreadsheet that needs to be read. The spreadsheet needs to be open in order to prevent confusing steps for inexperienced users – Tim Stack Aug 20 '19 at 06:39
  • @rusu_ro1 I don't see what that would add to the question. Any regular workbook would do: they just contain some regular data, no images/tables etc. – Tim Stack Aug 20 '19 at 06:40

1 Answers1

6

Open a workbook test.xlsx currently open in Excel, and read the value in cell A1 of the first worksheet:

from win32com.client import GetObject
xl = GetObject(None, "Excel.Application")
wb = xl.Workbooks("test.xlsx")
ws = wb.Sheets(1)
ws.Cells(1, 1).Value

Read a range as a tuple of tuples:

ws.Range("A1:D4").Value

Write back some values:

ws.Range("A1:D4").Value = [[16, 3, 2, 13], [5, 10, 11, 8], [9, 6, 7, 12], [4, 15, 14, 1]]

Answer to the comments: COM (Component Object Model), sometimes referred to as "Automation", allows a Windows application to provide a "COM server", which gives access to some of its APIs, to be accessed from a "COM client". Excel has such a server (and VBA has a client: you may use CreateObject and GetObject from VBA).

Other applications offer similar services through COM: for instance MATLAB, SAS, Stata, and all applications of Microsoft Office.

Python has a client with pywin32. You may also develop a server with Pywin32, see for instance this: Portable Python com server using pywin32

Note that in the case of Excel, as you noticed, you may access most of the object hierarchy, and control very precisely the behavior of Excel. Basically, if you can do it in VBA, you can do it from any COM client.

Regarding the last row of a range, I'm not sure I understand what you want. Is it this: Excel VBA Find last row in range ?

A few more points:

If Excel is not already open, you can still open a connection to Excel. In VBA the function to do this is CreateObject instead of GetObject, but in Python it's Dispatch:

from win32com.client import Dispatch
xl = Dispatch("Excel.Application")
xl.WorksheetFunction.Gamma(0.5)

In VBA you will often use Excel "constants", such as xlUp. They are available in Python too, with this (after starting the connection with Excel, with GetObject or Dispatch):

from win32com.client import constants as const
const.xlUp

To connect to a COM server installed on your computer, you need the name of the object to get. Here are a few cases:

For Microsoft Office:

Often used in VBScript:

Specialized software:

  • Matlab.Application
  • SAS.Application
  • stata.StataOLEApp

Last remark: as explained here, you can find the documentation of Pywin32 either in the directory where it's installed ([Pythonpath]\Lib\site-packages\PyWin32.chm), or on the web here: http://timgolden.me.uk/pywin32-docs/contents.html

  • Very useful information, would upvote a second time if I could. – Tim Stack Aug 20 '19 at 21:08
  • @user10307643 What if there is more than one instance of Excel running? Will your solution still work, regardless of which instance holds the workbook? If not - can you extend the solution to handle this case? – Fredrik Apr 23 '21 at 07:37