I have a script that I use to export expense entries from Excel to QIF format. To create the output files, I have a table in Excel that lists the expense (account) types:
PaymentType Category Acct Type ----------- ----------------- --------- A American Express CCard B Bob Cash C Capital One CCard E Expense Income Bank N PNC Bank P PayPal CCard S Sears CCard U USAir MC CCard
The content of the table is labeled: PaymentSourceTable
And I have this script to read the contents of the table and subsequently create an output file for each:
tell application "Microsoft Excel"
#
# Get the list of payment sources from the spreadsheet and
# create a QIF file for each
#
# Also create the list of filenames corresponding to the sources.
# That list will be used when appending expense records to the files.
#
set PaymentSources to {}
tell worksheet ListsWorksheet
set PaymentSources to value of range PaymentSourceTable
end tell
Everything works very well in the rest of my script but sometimes PaymentSources contains the actual table values twice - as if I had appended my table values twice. The entries appear A-U then A-U again.
It only happens when I run the script from the script menu while in Excel, and then only sometimes. It never happens if I run the script from the Applescript editor - or at least hasn't happened yet in my many tests. That in itself seems really odd and I keep trying it over and over, thinking I'm just imagining it. :-)
I'm baffled.
Has anyone else seen this or could suggest a way to debug?
btw, I'm using Excel for Mac 2011.
Update As it turns out, PaymentSources was not the problem. the following code was the real culprit:
repeat with source in PaymentSources
set _ptype to item 1 of source
if _ptype is not equal to "" then
set _type to "!Type:" & item 3 of source
set _furl to my constructFileName(source)
my createFile(_type, _furl)
set end of FileNames to {item 1 of source, _furl, 0}
end if
end repeat
that and the fact that I defined FileNames as a property:
property FileNames : {}
.
I totally missed the fact that properties persist between runs. Lesson learned.
