File Transfer - excel sheet calculation

excel sheet calculation

Printed From: Rocket Software
Category: AccuTerm Knowledge Base (read only)
Forum Name: File Transfer
Forum Description: Help with uploading & downloading database and document files
URL: https://forum.asent.com/forum_posts.asp?TID=2761
Printed Date: November 30 2023 at 2:38pm
Software Version: Web Wiz Forums 12.03 - http://www.webwizforums.com

Topic: excel sheet calculation

Posted By: kitkatinfl
Subject: excel sheet calculation
Date 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!

Replies:

Posted By: PSchellenbach
Date 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.

Posted By: kitkatinfl
Date 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.