0

I am creating a spreadsheet from scratch using xlwings and populating it with data from a text file.

I am trying to figure out a way to create a comment for cells using xlwings. From everything I have seen this is not possible. Does anyone know of a way to do this?

If not, then I have already created a VBA script in the past that I can use but is there a way that I can port this VBA script into my python script? I would like to do this so I dont have to run the python script and then separately run a VBA script.

Thank you in advance.

Grady Negronida
  • 9
  • 2
  • Have you tried this post? http://stackoverflow.com/questions/30308455/how-do-i-call-an-excel-macro-from-python-using-xlwings – Weasemunk May 09 '16 at 19:17
  • Thank you I guess i did miss that. I did find that XlsxWriter-0.8.6 supports writing comments so i may go the route of creating and populating the spreadsheet with xlwings and then running through the cells with XlsxWriter-0.8.6 to write in the comments... Not the best solution but better then messing around with a VBA script. – Grady Negronida May 09 '16 at 20:48
  • @GradyNegronida: XlsxWriter cannot be used for updating in the way you describe. It's a pure writer; it can only create new workbooks. If you use it to write a workbook with the same name as an existing workbook, then the old workbook will be destroyed, and you'll create a new workbook that has nothing but the comments (or whatever you've explicitly written using XlsxWriter). – John Y May 11 '16 at 13:29
  • In fact, if you are creating a workbook from scratch (as you say in your question), then you should be doing the entire project in XlsxWriter, and forget xlwings (and of course VBA) entirely. – John Y May 11 '16 at 13:32
  • @JohnY Yeah I actually came to this realization very shortly after going down the xlxsWriter path.. I have since rewritten the script with xlsxwriter and gotten ride of xlwings entirely as you suggested. Thank you all for your help!! – Grady Negronida May 12 '16 at 03:40

3 Answers3

0

As explained here, you can always fall back to the underlying pywin32 objects to workaround unimplemented features. In the case of comments, that would look something like this on Windows:

import xlwings as xw
wb = xw.Workbook.active()
xw.Range('A1').xl_range.AddComment()
xw.Range('A1').xl_range.Comment.Text('Some Text')
Felix Zumstein
  • 6,737
  • 1
  • 30
  • 62
  • It looks like the comments by default get displayed as bolded. How to selectively 'unbold' certain text of the comment? Thanks. – D.prd Nov 22 '17 at 16:28
0

If you are actually updating a spreadsheet and so using xlwings, then xlwings has changed its its api.

import xlwings as xw
wb = xw.Workbook.active()
xw.Range('A1').api.AddComment('Some Text')

I second that xlsxwriter is nice for building from scratch.

hum3
  • 1,563
  • 1
  • 14
  • 21
0

If wb = xlwings.Book() on macOS 10.12 with Excel 2011, then wb.range('A1').api.comment's get and set methods don't seem to work with XLWings 0.11.4. As a workaround, you can create a VBA macro in your workbook as follows

Sub AddCommentHook(address As String, message As String)
    Call Range(address).AddComment(message)
End Sub

(Don't forget to use the Developer toolbar's "Add Macro" dialog box to make sure the Excel workbook knows about the VBA code.) Then you can add an Excel cell comment from Python as follows:

ach = wb.macro('AddCommentHook')
ach('Sheet1!A1', 'test 123')
wkschwartz
  • 3,817
  • 2
  • 29
  • 33