Simple (?) Scripting in Excel Hurts My Head

[chrisgillespie]

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

 

[JE McGimpsey]

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

Try (XL04):

copy range range ("A3:" & (get address (get offset (get end ¬
(last cell of column 3 of sheet "Classical") direction ¬
toward the top) row offset 0 column offset -2)))