All,

I have a question about an excel formula.

I am creating an excel formula to concatenate fields from one tab into another tab.

I curently do this with an excel macro but I have a case opened with IBI concerning that.

We are upgradng from 8.0.0.7 to 8.2.0.2M

We are pretty sure that the issue is in the Java Restful Services that we use since the macro runs in every other fashion (locally through App Studio and creating a schedule by hand, and of course in the 8.0.0.7 version).

I am still working with them to resolve. But enough about that.

I have everything I need to work without the macro except this piece; and I am not sure I will be able to get it to work but wanted to ask.

I have no spaces before the '=' sign and the format of the cell is General (these things I have asked an excel expert here at my work, but they are not WF saavy, so I wanted to pose it to this board).

I was just wondering if there is something else that I might be missing that would allow the data to show and not the formula (even the "Show Formula" icon in excel is not selected when I go into the cell, so it doesn't think that it is a formula, it is treating the whole line like it is a regular text. I think the macro does something when it runs in the background that allows the data to show and not the formula.

I don't think any WF code is necessary here other than what I am putting below.

 

COMPUTE FIELDA/A1200='=CONCATENATE(Columns!A1,Columns!B1')'; AS ''

 

I am actually concatenating 32 columns together, but the above gives you the idea.

I am doing an

ON TABLE PCHOLD FORMAT EXL07 OPEN

for the first tab/sheet and then for the second tab/sheet

ON TABLE PCHOLD FORMAT EXL07 CLOSE

I tried with EXL2K as well but that procuded the same results.

Any thoughts or suggestions would be appreciated.

I may have to stick with the macro, but it is the only one we have and I am trying to keep from going through all the work of trying to resolve the issue but if I have to I will.

Thanks.

Paul

This message has been edited. Last edited by: Paul M.,

Do you have a working example with an IB Sample file? That would help us at least test your requirement in another version. Also, have you tried FORMAT XLSX?

BabaNYC,

Thanks for looking.

The format of XLSX is not supported by IBI when using Java Restful Services (JRS) - we have already talked to them about this. I tested for you with XLSX but it does the same thing.

When the job is run in Production, JRS is used, and EXL07 is the only option for that.

Below is code from the CAR file - you actually have to go into the cell and hit enter for each line in order for the data to show up and not the formula (even though excel doesn't realize that the formula is a formula).

  

-SET SHOWBLANKS=ON
TABLE FILE CAR
PRINT
CAR AS 'C,A,R'
MODEL AS 'M,O,D,E,L'
SEATS AS 'S,E,A,T,S'
DEALER_COST AS 'D,E,A,L,E,R,,C,O,S,T'
RETAIL_COST AS 'R,E,T,A,I,L,,C,O,S,T'
SALES AS 'S,A,L,E,S'
LENGTH AS 'L,E,N,G,T,H'
WIDTH AS 'W,I,D,T,H'
HEIGHT AS 'H,E,I,G,H,T'

BY CAR NOPRINT
BY MODEL NOPRINT
HEADING
"Car Test"
ON TABLE PCHOLD FORMAT EXL07 OPEN
ON TABLE SET STYLE *
TYPE=REPORT,FONT=ARIAL,SIZE=9,JUSTIFY=CENTER,$
TYPE=DATA,JUSTIFY=CENTER,$
TYPE=TITLE,STYLE=BOLD,$
TYPE=REPORT,TITLETEXT='Columns',$
ENDSTYLE
END
-RUN

TABLE FILE CAR
PRINT

COMPUTE KONKAT/A1200='=CONCATENATE(Columns!A3,Columns!B3,Columns!C3,Columns!D3,Columns!E3,Columns!F3,Columns!G3,Columns!H3,Columns!I3)'; AS ''

BY CAR NOPRINT
BY MODEL NOPRINT
ON TABLE PCHOLD FORMAT EXL07 CLOSE
ON TABLE SET STYLE *
TYPE=REPORT,FONT=ARIAL,SIZE=9,JUSTIFY=LEFT,WRAP=12,SQUEEZE=ON,$
TYPE=REPORT,TITLETEXT='Lines',$
ENDSTYLE
END
-RUN
-EXIT

Thanks.

It looks like it's happening in 8203 too All I have to do is double click the formula and it turns into what it's supposed to be.

BabaNYC,

I am not so worried about what version of WF per se because the file gets created for excel itself.

I am curious about what you said though; you said if you double click the line it then shows the data.

That doesn't happen for me.

I can double click the line all I want and the line doesn't change; only when I click into the actual cell (one at a time), and press enter, does it then show the data.

Are you able to select all the rows in excel and double click on that and then they convert (all at once) to the data?

That would be a good work around for our customer but since that doesn't work for me, it isn't quite viable.

I have Excel 2016 on my machine - it also may be dependent on that as well - what version of excel do you have?

Maybe there is a setting in excel I could change if highlighting all of the rows and double clicking works for you, please let me know.

Thanks.

Paul

What I meant is I have to double click each cell and move to another cell to get the value to show up instead of the formula. Multiselecting all doesn't work for me. However, if you multiselect all of them and search and replace = with = then the formulas turn into the values.

I'm running MS Office 2010. I'm also wondering if this is something having to do with Excel not the value you've put into the cell.

This message has been edited. Last edited by: BabakNYC,

BabakNYC,

Thansk again for doing this.

If this is something to do with excel I am not sure what I could change in excel to get it to work, I have looked at all the options but don't really see anything at this time.

This may be a decent work-around until we get the macro issue resolved.

It may be too much to ask of the customer, but I will at least have this option available.

Thanks for looking at this.

I am going to keep this open for the rest of the week in case someone else thinks of something different, then I will close it.

Thanks again for your efforts/testing.

Paul