changing pivot table ODBC source via Applescript: crashes

[iGods]

Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC Hello Pat, I noticed that you seem to be the helpful soul when it comes to all things weird and wonderful with Excel 2008 for the Mac.

I'm trying to change the source data of a pivot table with Applescript.

I've tried pulling this off with a parameter in the query, but 50% of the time the application either crashes or corrupts the document... I seem to have more luck using Applesript.

Although I *am* able to change the source data, and Excel nicely updates it instantly, requesting my credentials in the ODBC dialog, Excel decides to crash the moment I try to save the spreadsheet.

sigh.

here's the Applescript code that I have working so far, hope this helps someone out there:

global sqlSource, sqlOriginal, sqlFinal
global originalList, finalList

tell application "Microsoft Excel"

set currentDoc to workbook 1

set listOfWorksheets to every sheet 3 in currentDoc
    repeat with currentSheet in listOfWorksheets

set listOfPivotTables to every pivot table in currentSheet
        repeat with pivotTable in listOfPivotTables



tell source data of pivotTable
                copy it to originalList

tell originalList
                    copy item 1 as text to sqlSource
                    copy items 2 through end as text to sqlOriginal
                end tell

set sqlFinal to my searchandreplace(sqlOriginal, "hcf", "ictrends&quot

set finalList to {sqlSource} as list
                copy sqlFinal to tempSQL
                repeat while tempSQL ≠ ""
                    if (length of tempSQL > 230) then
                        set theChunk to characters 1 through 230 of tempSQL as string
                        copy theChunk to the end of finalList
                        copy (characters 231 through end of tempSQL) as string to tempSQL
                    else
                        set theChunk to characters 1 through end of tempSQL as string
                        copy theChunk to the end of finalList
                        set tempSQL to ""
                    end if
                end repeat

end tell
            tell application "Microsoft Excel" to activate
            set (source data of pivotTable) to finalList

end repeat

end repeat



end tell

--------- subroutines

(**** fast search and replace methods ****)
on searchandreplace(the_string, search_string, replace_string)
    return my list_to_string((my string_to_list(the_string, search_string)), replace_string)
end searchandreplace

 

[iGods]

sorry, here's the rest of the utility routines:

--------- subroutines

(**** fast search and replace methods ****)
on searchandreplace(the_string, search_string, replace_string)
        return my list_to_string((my string_to_list(the_string, search_string)), replace_string)
end searchandreplace

on list_to_string(the_list, the_delim)
        my atid(the_delim)
        set the_string to (every text item of the_list) as string
        my atid("&quot
        return the_string
end list_to_string

on string_to_list(the_string, the_delim)
        my atid(the_delim)
        set the_list to (every text item of the_string) as list
        my atid("&quot
        return the_list
end string_to_list

on atid(the_delim)
        set AppleScript's text item delimiters to the_delim
end atid