XLSetCell not working for me

[jbas]

The 'XLSetCell' statement in the following code is not working for me. I want to insert a 'C' in the 4th column but the column remains blank.

I am using Excel 2007.

XLOpen>MyFil,1,XLBook
Let>XL_Loop=1
Let>XL_Row=2
Repeat>XL_Loop
XLGetCell>XLBook,Sheet1,XL_Row,1,XL_C1
XLGetCell>XLBook,Sheet1,XL_Row,2,XL_C2
XLGetCell>XLBook,Sheet1,XL_Row,3,XL_C3
XLGetCell>XLBook,Sheet1,XL_Row,4,XL_C4
MidStr>XL_C3,1,1,XL_C3_1
Let>VBEval_In=UCase("%XL_C3_1%")
VBEval>%VBEval_In%,XL_C3_1
MidStr>XL_C3,2,1,XL_C3_2
Let>VBEval_In=UCase("%XL_C3_2%")
VBEval>%VBEval_In%,XL_C3_2
If>{(%XL_C1%="") or (%XL_C2%="")}
Let>XL_Loop=0
Else
If>XL_C3_1=Y
//GoSub>NDC_Updt,XL_C1,XL_C2
XLSetCell>XLBook,Sheet1,XL_Row,4,C
Let>XL_Row=XL_Row+1
Endif
If>XL_C3_2=P
GoSub>GetOk
Endif
If>XL_C3_2=S
Let>XL_Loop=0
Endif
Endif
Until>XL_Loop,0

Suggestions / Ideas?

Thanks!

[jbas]

P.S. The macro is updating the cell with a 'null' value - I can't seem to get it to insert text using either a variable or a constant

[gdyvig]

Hi jbas,

Try:

Code: Select all

//Does not need quotes
//Does need 6th param - the result variable.
XLSetCell>XLBook,Sheet1,XL_Row,4,C,result
//small wait so you can see the text added to the cell
wait>1

Gale

[jbas]

That did not work either.
Does that work for you?

[gdyvig]

I modified my previous post.
I removed the quotes around the C.
I also added the 6th param to store the result.
When I omitted the result variable, no text was added to the cell.
The user guide examples do not show the result variable.

I also cound not get XLSave to work. My xls file was in SCRIPT_DIR.


Gale

[Marcus Tettmar]

Sorry.

XlSetCell requires a result variable:

XLSetCell>xlBook,Sheet1,28,2,ABC,res

If successful the result var will hold 0, otherwise if an error occurred it will store the error message.

This works fine for me. Looks like the help file is wrong.

I've also just seen that XLSave is only working where the filename is literal, so will fail if there's a variable (e.g. SCRIPT_DIR) in the filename.

Will get both sorted asap.

[jbas]

That works.
Thanks!

[jpuziano]

Sorry.

XlSetCell requires a result variable:

XLSetCell>xlBook,Sheet1,28,2,ABC,res

If successful the result var will hold 0, otherwise if an error occurred it will store the error message.

This works fine for me. Looks like the help file is wrong.

Yeow... I missed that omission in the Help file... as I started with your example macros and in there, you included the result variable... so I was able to use this command without trouble. My compliments by the way, this method is much faster and more reliable than the code I was previously using to enter values into spreadsheets.

Question: Doesn't the XLSetCell> command complain if you are one parameter short i.e. there are 5 parameters supplied instead of 6? If not then it should.

I've also just seen that XLSave is only working where the filename is literal, so will fail if there's a variable (e.g. SCRIPT_DIR) in the filename.

Will get both sorted asap.

Thanks Marcus and take care

[Marcus Tettmar]

Question: Doesn't the XLSetCell> command complain if you are one parameter short i.e. there are 5 parameters supplied instead of 6? If not then it should.

Should do, but obviously doesn't.

The code builder, and syntax highlighter are correct. Only the help file and the parameter count warning seem to be wrong.