HI I’m trying to paste from open workbook to workbook with unknown used range
tell application "Microsoft Excel"
activate
activate object worksheet 1
set columnToTest to range "A:A" of active sheet -- adjust
set cellCount to count of cells of columnToTest
set usedRanges to used range of worksheet object of columnToTest
set rowsCount to first row index of (get end cell cellCount of columnToTest direction toward the top)
set lastCol to (first column index of usedRanges) + (count of columns of usedRanges)
copy range usedRanges (*copy to clipboard*)
tell application "Finder"
activate
select window of desktop
open document file "copy to" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
tell application "Microsoft Excel"
activate
activate object worksheet 1
set columnToPaste to range "A:A" of active sheet
set cellCount to count of cells of columnToPaste
set usedRanges to used range of worksheet object of columnToPaste
set rowsCount to first row index of (get end cell cellCount of columnToPaste direction toward the top)
set pasteRange to rowsCount + 1
paste range pasteRange
end tell
end tell
end tell
I get “Microsoft Excel got an error: The object you are trying to access does not exist”
could somebody set me straight.
I’ve tried many different approaches with the same answer in the last eight hours.
HELP I’M GOING BONKERS
What line is giving you that error?
Script Editor’s Event Log History would show this.
It looks like the goal of the script is to copy the used range of the active workbook to the bottom of column A of a workbook “copy to” (?no .xls or .xlsm?) in the specified folder.
Hi
the line giving me trouble is “paste range pasteRange”
“copy to” (?no .xls or .xlsm?)— no just “copy to” in the title of workbook
all 08 books come up .xlsx, would this make a difference if I left it in the title?
pasteRange is an integer, that’s why AppleScript won’t let you paste to it.
Depending on how you save your files. (I set my Excel2004 to automaticaly add the .xls tag) the tag is important.
As I understand it, Mac differs from Windows in that file tags are optional and are considered by the OS as just part of the file name.
Thus, if “MyExcelWorkbook” is saved (with tag automaticaly appended) there is no file “MyExcelWorkbook” for the OS to open, it can only open “MyExcelWorkbook.xls”
got me going in the right direction.
some searching and a lot of playing, this is the result.
it works if the workbook has only 1 page
thanks for your input
bills:cool:
tell application "Finder"
activate (* is not always needed*)
set getPath to "your folder" (*put folder on desktop*)
set fileList to every file of folder getPath
set loopFinish to count fileList
repeat with i from 1 to number of items in the fileList
set thisFile to item i of the fileList
open thisFile
tell application "Microsoft Excel"
activate
activate object worksheet 1
set columnToTest to range "A:A" of active sheet (*adjust*)
set cellCount to count of cells of columnToTest
set usedRanges to used range of worksheet object of columnToTest
set rowsCount to first row index of (get end cell cellCount of columnToTest direction toward the top)
set lastCol to (first column index of usedRanges) + (count of columns of usedRanges)
copy range usedRanges (*copy to clipboard*)
save active workbook
close active workbook
end tell
tell application "Finder"
activate
open document file "copy to" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
(*or new workbook*)
(*get next empty cell*)
tell application "Microsoft Excel"
activate
if get value of range "A1" is "" then
tell application "System Events"
keystroke "v" using {command down}
end tell
else
set TargetRange to column 1 of the active sheet
set AfterCell to cell "A1" in the active sheet
set FoundRange to find TargetRange what "" after AfterCell look in values look at whole search direction search next
select FoundRange
end if
tell application "System Events"
keystroke "v" using {command down}
end tell
tell application "Microsoft Excel"
save active workbook
close active workbook
end tell
end tell
end tell
end repeat
end tell