Rocket Software Homepage
Forum Home Forum Home > AccuTerm Knowledge Base (read only) > File Transfer
  New Posts New Posts RSS Feed - excel sheet calculation
  FAQ FAQ  Forum Search   Register Register  Login Login

The AccuTerm forum has moved. Go to community.rocketsoftware.com to register for the new Rocket forum.

Forum Lockedexcel sheet calculation

 Post Reply Post Reply
Author
Message
kitkatinfl View Drop Down
Newbie
Newbie


Joined: May 11 2022
Status: Offline
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote kitkatinfl Quote  Post ReplyReply Direct Link To This Post Topic: excel sheet calculation
    Posted: May 11 2022 at 11:11am
Hi, I am brand new to Accuterm but a veteran PICK user. I have not done much with excel spreadsheets at all, and certainly haven't created them through PICK. Now I need to!

The good news, Im late to the game so you all know this stuff and can give me tips or point me in the right direction - lucky me!!

I have a spreadsheet created through accuterms FTD. I want to send a formula? Equation? not sure of the correct terminology yet but - I want to fill a column with a calculation. I am trying to fill it with this:
" =INDIRECT("G" & ROW())/INDIRECT("J" & ROW())"

Putting this manually into the sheet works. I need to automate this.

So we added the code into the data to be sent, and yes! The equation excutes and the cell is filled in with the value BUT the function itself is not left on the cell.

The problem here is that if a user alters either of the two fields used in the function, we want the calced cell to recalc, and it wont without the function being preserved in the cell.

Any help would be greatly appreciated!
Back to Top
PSchellenbach View Drop Down
Admin Group
Admin Group

Moderator

Joined: December 15 2003
Location: United States
Status: Offline
Points: 2150
Post Options Post Options   Thanks (0) Thanks(0)   Quote PSchellenbach Quote  Post ReplyReply Direct Link To This Post Posted: May 11 2022 at 2:21pm
Hi Kitkatinfl -

I was able to use FTD to create an Excel worksheet with a formula. Here's what I did, so you can try it.

Using the PROD.SAMPLE sample data file in the ACCUTERM account, I added a new dictionary item named FORMULA with an Excel formula as a constant:
:CT DICT PROD.SAMPLE FORMULA
DICT PROD.SAMPLE FORMULA
01: S
02: 0
03: OUNCES
04:
05:
06:
07:
08: A;'=INDIRECT("C" & ROW())*16'
09: R
10: 12


Here's the FTD command:
:FTD

AccuTerm Data Transfer Utility


(S)end, (R)eceive, (C)onfigure, (O)ptions, (H)elp or (E)xit ? S

File transfer protocol: (A)SCII or (K)ERMIT ? K

Enter source (PICK) file name: PROD.SAMPLE

Enter source (PICK) item list: *

Enter target (DOS) file name (d:\directory\file.ext): C:\TMP\TEST.XLSX

Attributes to transfer: UPC DESC GROSS.WGT FORMULA

Generate Header Record (<Y>/N/C): Y

Explode MultiValue Fields (Y/<N>): N

Converting TEST.XLSX to Excel file format... done.

Transfer status: Successful transfer.
Transferred 4 items, 254 bytes.

The Excel worksheet accepts the formula in column D, and changing the value in column C causes column D to recalculate.



Back to Top
kitkatinfl View Drop Down
Newbie
Newbie


Joined: May 11 2022
Status: Offline
Points: 3
Post Options Post Options   Thanks (0) Thanks(0)   Quote kitkatinfl Quote  Post ReplyReply Direct Link To This Post Posted: May 11 2022 at 4:53pm
Hi Pete, pleasure to meet you! You've written some pretty slick stuff!

From what I am reading and trying, there are a number of ways to accomplish what I am looking for. Your above example with the formula in the dict is one way. I haven't actually tried it but I trust you :)

I see here at Colwell that often they send an equation within the data field... and that also seems to work.

And still other programs send 'objExcel' commands... If I have this correct that is VBA.

Can we, do we, use and intermingle all these? All there rules when to use one vs another? Any documentation on PICK into Excel? Hopefully you didn't just laugh, but I get it if you did.

As for this particular need, I need different cells to have different formulas so, I think putting the formula in the data element is one way. The header would alter all the data in that column I assume so that would not work here. No, I did not say that up front and apologize for that.

It does seem here that if the first data element listed doesn't have a formula (when using formulas within the data) then later formulas ( say row 10) will be taken as literals and not executed. That was what we've found to be the issue in my test anyway.

Any insight, documentation, classes, reference, wink of an eye... whatever you can do to help me understand the differences would be great. Thank you for getting back to me.
Back to Top
 Post Reply Post Reply
  Share Topic   

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.03
Copyright ©2001-2019 Web Wiz Ltd.