Converting Excel document to PDF manually (halfway there)...
Good day,
I would like to convert an existing Excel spreadsheet (Expense Claim Sheet) containing formulas and linked data to a fillable PDF form retaining all the functionality of the formulas. I know there is no direct conversion so I have begun doing this manually with Acrobat Javascript derived from other forum posts searched here as I am not a programmer at all.
Please if someone can assist with the below issues I would greatly appreciate it or advise if the Excel document should just be used instead, as I cannot find solutions to the below with the knowledge I have; I can provide links to the original Excel file and PDF file I'm still working on if required - thanks:
- Firstly, my intention with the "Department" dropdown list is to populate the "Expense Account Description" dropdown with a specific list dependant on the specific department chosen. Once the list is populated, you must be able to then choose an account from the dropdown which will automatically insert the respective account number in the next column for "Expense Acc. No." Also, for a new line, a unique Expense Account Description with it's respective Expense Acc. No. must be able to be selected. I've managed this with the below code under 'custom keystroke script' under the 'format' tab in 'properties' (these are for the first three lines of the form - i.e. supplier X,Y,Z, there are more items in the lists to be added in the code, these are only a sample), but is there a simpler way to code this than what I have done (screenshots below code)?
var Itemlist = this.getField("Expense Account Description.0");
var Price = this.getField("Expense Account No.0");
if(event.willCommit)
{
switch(event.value){
case "Production":
Itemlist.setItems(["-Select Production Account-", "Purchases Design Consumables", "Production Consumables - Roll"]);
Price.value = "";
break;
case "Maintenance":
Itemlist.setItems(["-Select Maintenance Account-", "Tool Replacement", "R & M Ink Electrical"]);
Price.value = "";
break;
default:
Itemlist.setItems([""]);
Price.value = "";
break;
}
}
var Itemlist = this.getField("Expense Account Description.1");
var Price = this.getField("Expense Account No.1");
if(event.willCommit)
{
switch(event.value){
case "Production":
Itemlist.setItems(["-Select Production Account-", "Purchases Design Consumables", "Production Consumables - Roll"]);
Price.value = "";
break;
case "Maintenance":
Itemlist.setItems(["-Select Maintenance Account-", "Tool Replacement", "R & M Ink Electrical"]);
Price.value = "";
break;
default:
Itemlist.setItems([""]);
Price.value = "";
break;
}
}
var Itemlist = this.getField("Expense Account Description.2");
var Price = this.getField("Expense Account No.2");
if(event.willCommit)
{
switch(event.value){
case "Production":
Itemlist.setItems(["-Select Production Account-", "Purchases Design Consumables", "Production Consumables - Roll"]);
Price.value = "";
break;
case "Maintenance":
Itemlist.setItems(["-Select Maintenance Account-", "Tool Replacement", "R & M Ink Electrical"]);
Price.value = "";
break;
default:
Itemlist.setItems([""]);
Price.value = "";
break;
}
}
2. Secondly, the amounts in the "Amount Excl." and "VAT" column do not update to the correct amounts as they should when for instance the value is changed in the "Amount Incl." column and the "Yes" option was initially checked under the "VAT?" dropdown (they do however update automatically). The dropdown needs to be clicked "No" / "blank" and then clicked "Yes" for a second time for the columns to then update to the correct amounts (VAT needs to remain consistent at 15% of the “Amount Excl.”) - this is done automatically in Excel.
Can this be fixed to auto-update correctly in the code? And where?
This poses a problem if, say someone entered the incorrect amount initially and selected "Yes" for "VAT" and then wanted to change that amount before completing the form, but wasn't aware that they needed to click through the options again to ensure the correct “VAT” and “Amount Excl.” amounts were displayed and neglected to check the percentage. (see below code for "VAT?" dropdown under 'custom keystroke script' under the 'format' tab in 'properties' and screenshots below):
if(event.willCommit)
{
console.println(event.value);
switch(event.value)
{
case "Yes":
this.getField("VATRow1.0").value = this.getField("Amount InclRow1.0").value / 1.15 * 0.15;
break;
case "No":
this.getField("VATRow1.0").value = 0;
break;
default:
this.getField("VATRow1.0").value = 0;
break;
}
}
AdChoices