-- set up the days of the week

set DayStart to 4 -- column 3, the starting column of the first date



set Mon to "" -- we're not using days of the week in this form, so set to blank.

set Tue to ""

set Wed to ""

set Thu to ""

set Fri to ""

set Sat to ""

set Sun to ""

set dateList to {1, 2, 3, 4, 5, 6, 7}

set itemsInDateList to 0



-- set up the description variables

set miscDescCol to 1 -- the column of the Misc items' description (Column 'B', or 2)

-- Note: we're using the "Item" field here.  If you prefer the

-- description info to go into the "Explanation" field, then change

-- this number to 5 (column 'E')





set miscAmountCol to 5 -- the column of the Misc items' amounts (Column 'D', or 4)

set miscDateCol to miscAmountCol + 1 -- the column of the Misc items' dates (Column 'E', or 5)

set miscRow to 38 -- the starting row of the Misc itemizations (Row 38)

set miscRowStop to 40



-- set up the entertainment details variables

set entDateCol to 11

set entPlaceCol to 1

set entPersonsCol to 4 -- the column of the Misc items' description (Column 'C', or 3)

set entPurposeCol to 7

set entAmountCol to entPurposeCol + 3

set entRow to 44

set entRowStop to 47



-- set up the categories

set mileage to 5 -- row 5

set fares to mileage + 2

set rental to fares + 1

set Fuel to rental + 1

set parking to Fuel + 1

set taxi to parking + 1

set tips to taxi + 1

set lodging to tips + 1

set Phone to lodging + 1

set Breakfast to Phone + 1

set Lunch to Breakfast + 1

set dinner to Lunch + 1

set snack to dinner + 1

set entertainment to snack + 1

set supplies to entertainment + 1

set misc to supplies + 1



set businessRows to {B

	mileage, mileage, B

	fares, 23, B

	rental, 24, B

	Fuel, 25, B

	parking, 26, B

	taxi, 27, B

	tips, tips, B

	lodging, 28, B

	Phone, 29, B

	Breakfast, 30, B

	Lunch, 30, B

	dinner, 30, B

	snack, 30, B

	entertainment, 31, B

	supplies, 32, B

	misc, 33}











-- ExpensePlus 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 "Gary Amstutz"

set CompanyName to "State Of The Art"

set LocationVisited to ""

set Purpose to ""

set CashAdvance to "0"

set MileageRate to "0.28"

set UsersCountry to "USA"



set WeekEndingFriday to "10/28/94"

set WeekEndingSaturday to "10/29/94"

set WeekEndingSunday to "10/30/94"



set usersCurrencySign to "$"

set leadingCurrency to "1"

set countries to { "USA", 1}

set ExpenseData to {{Wed, "10/19/94", Taxi, 23, "", " ", "USA"},  B

{Wed, "10/19/94", Tips, 12, "", " ", "USA"},  B

{Wed, "10/19/94", Lodging, 230, "", " ", "USA"},  B

{Thu, "10/20/94", Lodging, 230, "", " ", "USA"},  B

{Fri, "10/21/94", Lodging, 230, "", " ", "USA"},  B

{Sat, "10/22/94", Lodging, 230, "", " ", "USA"},  B

{Sun, "10/23/94", Lodging, 230, "", " ", "USA"},  B

{Mon, "10/24/94", Lodging, 230, "", " ", "USA"},  B

{Tue, "10/25/94", Lodging, 230, "", " ", "USA"}}



-- %%% 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



tell application "Microsoft Excel"

	activate



	try

	open "Excel Expense Template 3"

	on error

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

		open the result

		activate

	end try



	open "Excel Expense Template 3"

	if Cell "UserName" exists then

		activate

	else

		activate

		choose file with prompt "Find the 'Excel Expense Template 3' file"

		open the result

		activate

	end if

	

	set excelVersion to version

	if excelVersion < 5 or excelVersion > 12 then set excelVersion to 4

	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

	set value of Cell "CompanyName" to CompanyName

	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)

		set ExpenseRecord to item i of ExpenseData

		set thisExpenseDate to item 2 of ExpenseRecord

		set myCategory 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

		

		if currencyCountry is not UsersCountry then

			if myCategory 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

		

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

			

			if dateList does not contain thisExpenseDate then

				set itemsInDateList to itemsInDateList + 1

				if (itemsInDateList > (number of items in dateList)) then

					set itemsInDateList to 1

					set dateList to {1, 2, 3, 4, 5, 6, 7}

					set miscRow to 38 -- the starting row of the Misc itemizations (Row 38)

					set entRow to 44 -- the starting row of the Entertainment itemizations (Row 44)

					open "Excel Expense Template 3"

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

						activate

					else

						activate

						choose file with prompt "Open another 'Excel Expense Template 3' file"

					end if

					

					set value of Cell "UserName" to myUserName

					set value of Cell "CompanyName" to CompanyName

					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

				end if

				set item itemsInDateList of dateList to thisExpenseDate

				set value of Cell (itemsInDateList + DayStart - 1) of Row 4 to thisExpenseDate

			end if

			

			

			if myCategory is misc then

				set value of Cell miscRow of Column miscAmountCol to expAmount

				set value of Cell miscRow of Column miscDateCol to thisExpenseDate

				set value of Cell miscRow of Column miscDescCol to expDescription

				-- if the miscellaneous itemization section is fullI

				if miscRow is miscRowStop then

					set miscRow to 60

				end if

				set miscRow to miscRow + 1

			end if

			

			if myCategory is entertainment or (B

				myCategory is Breakfast or myCategory is Lunch or myCategory is dinner or myCategory is snack) 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 thisExpenseDate

					

					if details1 is not "" then

						if expDescription is not "" then

							set value of Cell entRow of Column entPlaceCol to expDescription

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

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

						end if

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

							& details1

					end if

					set value of Cell entRow of Column entPersonsCol to details2

					set value of Cell entRow of Column entPurposeCol to details3

					-- if the entertainment section is fullI

					if entRow is entRowStop then

						set entRow to 87

					end if

					set entRow to entRow + 1

				end if

			end if

			if expMarker is "C" then -- if it was paid on the company credit card

				repeat with busIndex from 1 to (number of items in businessRows) by 2

					if item busIndex of businessRows is myCategory then

						set myCategory to item (busIndex + 1) of businessRows

						exit repeat

					end if

				end repeat

			end if

			

			set curCol to (itemsInDateList + DayStart - 1) -- expenses will always arrive sorted by date

			set value of Cell myCategory of Column curCol to (value of Cell myCategory of Column curCol) + expAmount

		end if

		

	end repeat

	

	

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

	-- Now do the currency conversion tables

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

	-- set up the location of the conversion table

	set currencyCountryCol to 1

	set currencyRateCol to 3

	set currencyRow to 50

	

	

	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 i to i + 1

		if item i of countries - 0 then

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

		end if

		set currencyRow to currencyRow + 1

	end repeat

	

	

end tell