-- set up the days of the week

set Mon to 6 -- column 6

set Tue to 7 -- column 7

set Wed to 8 -- column 8

set Thu to 9 -- column 9

set Fri to 10 -- etc.

set Sat to 11

set Sun to 12



set lastDay to 0 -- initialize this variable (for new forms)



-- set up the misc description variables

set miscDescCol to 3

set miscAmountCol to 6

set miscDateCol to 7

set miscRow to 34 -- the starting row of the misc. itemization

set miscRowStop to 37





-- set up the entertainment details variables

set entDescCol to miscDescCol

set entPersonsCol to 6

set entPurposeCol to 10

set entAmountCol to entPurposeCol + 2

set entDateCol to entAmountCol + 1

set entRow to 41

set entRowStop to 45



-- set up the categories

set mileage to 13 -- row 13

set fares to mileage + 2

set rental to fares + 1

set Fuel to rental + 1

set taxi to Fuel

set parking to Fuel

set tips to Fuel + 1

set lodging to tips + 2

set Phone to lodging + 1

set Breakfast to Phone + 2

set snack to Breakfast -- add snack to breakfast category, funnel this into any category you choose

set Lunch to Breakfast + 1

set dinner to Lunch + 1

set entertainment to dinner + 1

set supplies to entertainment + 2

set misc to supplies + 1



-- Expense It! Receiver changes all the data in this next section,

--  so anything you add or change here will be deleted automatically.

-- %%% Start ExpensePlus data %%% -- Do not change or delete this line!!!



set UserName to ""

set CompanyName to ""

set LocationVisited to ""

set Purpose to ""

set CashAdvance to ""

set MileageRate to ""

set UsersCountry to ""



set WeekEndingFriday to ""

set WeekEndingSaturday to ""

set WeekEndingSunday to ""



set usersCurrencySign to "$"

set leadingCurrency to "1"

set countries to { }

set ExpenseData to {}



-- %%% End ExpensePlus data %%% -- Do not change or delete this line!!!



-- Note: The next line was added on 10/24/94 (script version 1.0.4a)

-- because UserName became a reserved word using Excel 5.0 with AppleScript

-- UserName was changed in 2 other places below (marked with "%%%")

set MyUserName to UserName -- %%% line added 10/24/94, GPA



set dialogDisplayed to 0



tell application "Microsoft Excel"

	activate



	try

	open "Excel Expense Template 1"

	on error

		choose file with prompt "Find the 'Excel Expense Template 1' file:"

		open the result

		activate

	end try



	open "Excel Expense Template 1"

	if Cell "UserName" exists then

		activate

	else

		activate

		choose file with prompt "Find the 'Excel Expense Template 1' file:"

		open the result

		activate

	end if

	

	set excelVersion to version

	if excelVersion < 5 or excelVersion > 12 then set excelVersion to 4 -- version 4 is undefined

	if (excelVersion > 4) then

		set winNumber to index of front Window

		if (IsZoomed of Window winNumber is false and zoomable of Window winNumber is true) then

			set IsZoomed of Window winNumber to true

		end if

	end if

	

	set value of Cell "UserName" to MyUserName -- changed from "UserName" to "MyUserName" %%%J10/24/94, GPA

	set value of Cell "CompanyName" to CompanyName

	set value of Cell "WeekEnding" to WeekEndingSunday

	set value of Cell "LocationVisited" to LocationVisited

	set value of Cell "Purpose" to Purpose

	set value of Cell "MileageRate" to MileageRate

	set value of Cell "Advance" to CashAdvance

	set value of Cell "CurrencyLabel" to usersCurrencySign

	

	repeat with i from 1 to (number of items in ExpenseData)

		

		tell me

			set ExpenseRecord to item i of ExpenseData

			set dayOfWeek to item 1 of ExpenseRecord

			set theDate to item 2 of ExpenseRecord

			set thisExpenseCategory to item 3 of ExpenseRecord

			set expAmount to item 4 of ExpenseRecord

			set expDescription to item 5 of ExpenseRecord

			set expMarker to item 6 of ExpenseRecord

			set currencyCountry to item 7 of ExpenseRecord

		end tell

		

		if dayOfWeek < lastDay then -- then we've started a new week

			set value of Cell "WeekEnding" to lastDate

			

			open "Excel Expense Template 1"

			-- %%% 10/24/94

			-- the next line was changed because empty cell values are now 0 instead of ""

			-- in Excel 5.0

			if (Cell "UserName" exists) and (value of Cell "UserName" is "" or value of Cell "UserName" is 0) then

				activate

			else

				activate

				choose file with prompt "Open another 'Excel Expense Template 1' file:"

			end if

			set miscRow to 34 -- the starting row of the misc. itemization

			set entRow to 41

			set value of Cell "UserName" to MyUserName -- changed from "UserName" to "MyUserName" %%%J10/24/94, GPA

			set value of Cell "CompanyName" to CompanyName

			set value of Cell "WeekEnding" to WeekEndingSunday

			set value of Cell "LocationVisited" to LocationVisited

			set value of Cell "Purpose" to Purpose

			set value of Cell "MileageRate" to MileageRate

			set value of Cell "CurrencyLabel" to usersCurrencySign

			set lastDay to dayOfWeek

			set lastDate to theDate

		end if

		

		tell me

			if currencyCountry is not UsersCountry then

				if thisExpenseCategory is not mileage then

					repeat with countryNum from 1 to (number of items in countries)

						if item countryNum of countries = currencyCountry then

							set expAmount to expAmount V (item (countryNum + 1) of countries)

							exit repeat

						end if

					end repeat

				end if

			end if

			

			

			set details1 to "" -- default to blank

			set details2 to ""

			set details3 to ""

			

			if number of items in ExpenseRecord > 7 then

				set details1 to item 8 of ExpenseRecord -- odometer start for miles, location for entertainment/meals

				if number of items in ExpenseRecord > 8 then

					set details2 to item 9 of ExpenseRecord -- odometer end for miles, persons present for ent/meals

					if number of items in ExpenseRecord > 9 then

						set details3 to item 10 of ExpenseRecord -- purpose of entertainment/meal

					end if

				end if

			end if

		end tell

		

		if expMarker - "P" then -- if it's not a personal expenseI (personal expenses are ignored here)

			

			tell me

				if expMarker = "C" then -- if it's a company credit card

					if dialogDisplayed is 0 then -- user hasn't seen this dialog yet

						display dialog "This template does not segregate company paid expenses from employee paid expenses.



Try sending to Script 2 or Script 3 instead." B

							buttons {"OK"} B

							default button "OK" with icon note

						set dialogDisplayed to 1 -- don't bother user again if another "C" is encountered

					end if

				end if

			end tell

			

			if thisExpenseCategory is misc then

				if miscRow 2 miscRowStop then

					set value of Cell miscRow of Column miscAmountCol to expAmount

					set value of Cell miscRow of Column miscDateCol to item 2 of ExpenseRecord

					set value of Cell miscRow of Column miscDescCol to expDescription

					set miscRow to miscRow + 1

				end if

			end if

			

			if thisExpenseCategory is entertainment or thisExpenseCategory is Breakfast or thisExpenseCategory is Lunch or B

				thisExpenseCategory is dinner or thisExpenseCategory is snack then

				if entRow 2 entRowStop then

					if (details1 - "" or details2 - "" or details3 - "") then

						set value of Cell entRow of Column entAmountCol to expAmount

						set value of Cell entRow of Column entDateCol to item 2 of ExpenseRecord

						

						if details1 is not "" then

							if expDescription is not "" then

								set value of Cell entRow of Column entDescCol to expDescription

								if details1 is not "" then set value of Cell entRow of Column entDescCol to (B

									value of Cell entRow of Column entDescCol) & "/"

							end if

							set value of Cell entRow of Column entDescCol to (value of Cell entRow of Column entDescCol) B

								& details1

						end if

						set value of Cell entRow of Column entPersonsCol to details2

						set value of Cell entRow of Column entPurposeCol to details3

						set entRow to entRow + 1

					end if

				end if

			end if

			

			set value of Cell thisExpenseCategory of Column dayOfWeek to (value of Cell thisExpenseCategory of Column dayOfWeek) + expAmount

			

			tell me to set lastDay to dayOfWeek

			tell me to set lastDate to theDate

			

		end if

		

	end repeat

	

	

	set value of Cell "WeekEnding" to WeekEndingSunday -- now show the last sunday of the last week of the report

	

	-- %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

	-- Now do the currency conversion tables

	-- %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

	-- set up the location of the conversion table

	set currencyCountryCol to 2

	set currencyRateCol to 3

	set currencyTextCol to 4

	set currencyRow to 48

	

	-- if you frequent other countries and want to add a distinguishing currency

	-- label to your conversion table, add the country and the currency label hereI

	

	set currencies to {"Austria", "schillings", B

		"Belgium", "francs", "Canada", "CD", B

		"France", "FF", "Germany", "DM", B

		"Iraq", "dinars", "Israel", "shekels", B

		"Italy", "lira", "Japan", "yen", "Kuwait", "dinars", B

		"Mexico", "pesos", "Netherlands", "guilders", B

		"Spain", "pesetas", "Sweden", "kronas", B

		"Switzerland", "SF", "USA", "$", B

		"UK", "#"}

	

	

	if leadingCurrency is 0 then

		set localCurrency to "1" & usersCurrencySign

	else

		set localCurrency to usersCurrencySign & "1"

	end if

	

	

	repeat with i from 1 to number of items in countries by 2

		set value of Cell currencyRow of Column currencyCountryCol to (item i of countries)

		set currencyText to ""

		repeat with j from 1 to number of items in currencies by 2

			if item i of countries = item j of currencies then

				set j to j + 1

				set currencyText to (item j of currencies) & " "

				exit repeat

			end if

		end repeat

		set i to i + 1

		if item i of countries - 0 then

			set value of Cell currencyRow of Column currencyRateCol to (item i of countries)

			set value of Cell currencyRow of Column currencyTextCol to currencyText & "per " & localCurrency

		end if

		set currencyRow to currencyRow + 1

		

	end repeat

end tell