Some more vb.net and excel grief

Question

0.00/5 (No votes)

See more:

hi there,
so from my vb.net code i send this through to excel

FormulaR1C1 = "=SUM(" & pubfuncCheckNull(privFuncRetrieveExcelColumnName(i, 2) & ":" & privFuncRetrieveExcelColumnName(i, dtaTable.Rows.Count + 1)) & ")"

which basically just says SUM(B3:B4) in vb but when it gets to excel, it reads SUM('B3':'B4') and doesn't work out the sum. I know its a long shot but can someone tell me what i'm doing wrong? is it cause i use a string concatenation in the sum method?
Thanks in advance

Posted 21-Aug-11 23:54pm

Ace_Hardlight

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2011-08-21T23:59:00

Solution 1

I would look at the definition of privFuncRetrieveExcelColumnName - it looks to be encapsulating the Cell IDs.
I assume it is one of yours - google couldn't find it.

Posted 21-Aug-11 23:59pm

OriginalGriff

Comments

Ace_Hardlight 22-Aug-11 6:02am

pubfuncCheckNull and privFuncRetrieveExcelColumnName are my own yes. pubfuncCheckNull checks that the input string is not empty it works fine. privFuncRetrieveExcelColumnName retrieves the column e.g. "A","B" etc and returns it as a string.

OriginalGriff 22-Aug-11 6:06am

Check the value returned (either use the debugger to breakpoint and have a look at the string FormulaR1C1, or write it to the console) - I suspect that privFuncRetrieveExcelColumnName returns a string encapsulated in quotes, rather than just as the bare string.

Ace_Hardlight 22-Aug-11 6:08am

yes that's right it does return a string encapsulated in quotes like "B4". How do i remove the quotes then?

OriginalGriff 22-Aug-11 6:12am

Personally? I would create a second method that didn't generate them in the first place, then call it from the one that does... :laugh:
But if that isn't an option, there is always
string s = "SUM('B3':'B4')";
s = s.Replace("'", "");

Ace_Hardlight 22-Aug-11 6:24am

if my function returns "B2:B3" exactly like that, would it be a problem? cause my code would then read "=SUM(" & "B3:B4" & ")".

Ace_Hardlight 22-Aug-11 6:24am

let me just make sure of something. If my function returns "B2:B3" like that exactly, could that be the problem? Cause my code would then read "=SUM(" & "B2:B3" & ")"

OriginalGriff 22-Aug-11 6:30am

Your function probably doesn't: if you look at things in the debugger it can be confusing. You get used to it, but it can be a pain. Try sending it to the console, and you will see what I mean.