Simple (?) Scripting in Excel Hurts My Head

[Mackey]

I still find trying to AppleScript in Excel a maddening experience. Perhaps someone could help me out. I’ve got a table of info about classical music, including file paths. I autofilter for a criteria -- an album title, for instance -- and want to copy the paths from column A to the clipboard or an AppleScript variable. Seems simple enough: copy the shown values from column A. Can’t pull it off and it’s driving me nuts. I keep getting the entire column of data, or the paths I want along with 65,000 blank values.

I thought I’d gotten it by writing a VBA macro (on a PC) that successfully copies the info from column A for just the filtered values. I thought I’d trigger it with an AppleScript and have AppleScript grab them from the clipboard and send them on to iTunes. But when I ran the macro on my Mac, it gives me “Compile error. Can't find project or library.”


The VBA Macro:
Sub CopyPaths()
Sheets("Classical").Activate

' DETERMINE THE LAST ROW WITH ACTUAL DATA IN IT (AS OPPOSED TO WHAT EXCEL CALLS THE LAST ROW.
Lastdatarow = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Do While Not (Range("C" & Lastdatarow).Value > 0) And Lastdatarow > 1
Lastdatarow = Lastdatarow - 1
Loop

Range("A3:A" & Lastdatarow).Select
Selection.Copy

End Sub


I have both Office 2001 and 2004 and would settle for either version and either an all AppleScript or an AppleScript / VBA solution -- though 2004 produces so many “missing value” errors that 2001 is preferable.

Thanks,
Chris