Re: formula write errors

[John Machin]

On Oct 10, 6:02 am, Cory Kates <coryka...@gmail.com> wrote:
> I want to start by saying the code I have is working most of the time and I
> have to say, this package has been a total godsend, after using it the idea
> that I might someday have to work in VBA again makes my eyes bleed.  That
> said, the problem I'm encountering is as follows.  I'm reading through a
> sizable text file (over a thousand printed pages) and parsing out a months
> worth of transaction data for representatives.  These representatives have
> four digit IDs which can be numbers or letters.  I use these IDs for the
> sheet names.  After writing the individual reps data I have a summary
> sheet, the first sheet of the workbook, where I sum categories of values
> (for example mutual funds shares, government bonds etc) across all the reps
> sheets.  So 90% of the time I'm golden but, when the reps ID is all numeric
> and that number is higher than the number of sheets in the workbook I get
> the following error traceback.  My understanding is that its interpreting
> the number as an sheet index reference (below the first rep ID is 0216).  I
> think the problem is actually in how I'm using the formula function.  So
> long story short, how do I convince the formula function that 0216 is not
> an index reference but a named reference.

Sheet names like 1234 and The First Sheet need to be "quoted" with
apostrophes '1234' and 'The First Sheet' so that they are interpreted
as a single string token.

[Cory Kates]

I've tried putting single quotes around the sheet names as follows:

temp_form = "sum('"+office_AE_dict[PastOffice][0] + "':'"+office_AE_dict[PastOffice][-1] + "'!" + content[12:15] +")"

I've also tried escaping the single quotes, I get exactly the same error and traceback as I first posted.

Any thoughts?

[Brent Marshall]

On Fri, Oct 12, 2012 at 6:03 PM, Cory Kates <cory...@gmail.com> wrote:

I've tried putting single quotes around the sheet names as follows:

temp_form = "sum('"+office_AE_dict[PastOffice][0] + "':'"+office_AE_dict[PastOffice][-1] + "'!" + content[12:15] +")"

I've also tried escaping the single quotes, I get exactly the same error and traceback as I first posted.

Any thoughts?

Cory, I hope that, given the passage of time, you have solved your problem. If not, here are a couple of ideas.

First, as I read your code, you are trying to put single quotes separately around the tab names at the beginning and end of your range so that your formula works out to something like this:
=SUM('123':'789'!A1)

Try instead using a single set of single quotes around the entire tab range so that your formula is more like this:
=SUM('123:789'!A1)

Thus, in the midst of your temp_form line, instead of "':'" try  ":". I am not sure that this is your key problem, but for me in Excel 2010, the second formula does the desired calculation while the first does not. I recognize that xlwt does not fully match how the Excel user interface works, but it seems worth checking.

If you are still having problems thereafter, one temporary workaround might be to prepend a text prefix to your tab names so that the tab name is never only digits. For example, since these are id numbers, you might put id- at the beginning of every tab name, giving you id-0216 instead of simply 0216.

HTH.

Brent

[Cory Kates]

I appreciate the effort Brent, ultimately I did just have it write the ID and the last name of the user.  I'm going to revisit the problem later because now the sheet tabs are enormous but its more important to get the rest of this cranking.  Your 'id-' solution would probably be better as it'd save me a lot of space (who cares about their names these are cubicle drones and this isn't some fucking touchy feely non-profit!)

I did try both combinations of writing the single quotes within the formula and it just wouldn't take.  Ultimately I wrote another loop to just build the long formula accessing the correct cell on each sheet individually. ie Sheet1!A2 + Sheet2!A2 + Sheet3!A3 instead of Sheet1:Sheet3!A

I'll be sure to try your way, I think I did that before but it can't hurt trying once more.

Thanks again.

-cory.

[Cory Kates]

So I tried adding in the single quotes and I get a different error:

Traceback (most recent call last):

  File "C:\lib\bdb.py", line 387, in run

    exec cmd in globals, locals

  File "H:\PyPlay\ExcelCreator\ExcelCreate020.py", line 20, in <module>

    import time

  File "H:\PyPlay\ExcelCreator\ExcelCreate020.py", line 194, in main

    write_Summary(wBook, summary_style, yellow_fill, office_AE_dict, PastOffice)

  File "H:\PyPlay\ExcelCreator\ExcelCreate020.py", line 382, in write_Summary

    wSheet.write(rowNum-1, colsToWrite[x]-1,Formula(temp_form) , summary_style)

  File "C:\xlwt\ExcelFormula.py", line 22, in __init__

    raise ExcelFormulaParser.FormulaParseException, "can't parse formula " + s

FormulaParseException: can't parse formula sum(0AEC VANBURIK R:ALL!F26)

I find it odd that the single quotes are omitted in the traceback.  I tried escaping the single quotes and they're still missing from the traceback.  It seems like its interpreting the whole item before the ! as a single sheet name.  I really hope I'm just being foolish about something here because I'm at a loss.

-cory.

On Friday, November 2, 2012 1:48:32 PM UTC-4, Brent Marshall wrote: