Hi
every time I save newbook to a path, it goes to documents, not the file I want it to go to.
Ive tried this to get the path with existing workbook in file
tell application "Microsoft Excel"
activate
get path of active workbook
end tell
it returns this " :Users:bestbuy:Desktop:ttt"
when I use this path, it goes to documents.
tell application "Finder"
activate
open document file "20/101.xlsx" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
end tell
tell application "Microsoft Excel"
activate
set screen updating to false
copy range range "A1:C1140"
set screen updating to true
save active workbook
close active workbook
set newBook to make new workbook
set column width of range "A:AZZ" to 5 -- characters (columns)
end tell
tell application "System Events"
keystroke "v" using {command down}
end tell
tell application "Finder"
activate
open document file "20/201.xlsx" of folder "Documents" of folder "bestbuy" of folder "Users" of startup disk
end tell
tell application "Microsoft Excel"
activate
copy range range "A1:C1"
delete range range "A1:C1" shift shift up
save active workbook
close active workbook
end tell
tell application "Microsoft Excel"
activate
paste worksheet sheet "Sheet1" destination range "D1:F1"
save workbook as newBook
set thePath to " Users:bestbuy:Desktop:ttt"
close active workbook
end tell
set destinationPath to (path to desktop as text) & "ttt.xlsx"
tell application "Microsoft Excel"
save active workbook in destinationPath
end tell
by the way, Excel can open documents, the Finder is not needed
set documentsFolder to path to documents folder as text --> returns the path to the documents folder of the current user
tell application "Microsoft Excel"
activate
open documentsFolder & "20/101.xlsx"
end tell
and the GUI scripting paste is also not needed, Excel can copy and paste from one workbook to another in one line
tell application "Microsoft Excel"
copy range (range "A1:C1140" of sheet "Sheet1" of workbook "workbook1") destination range "A1:C1140" of sheet 1 of workbook "workbook2"
end tell
Open this Scriplet in your Editor:
set documentsFolder to path to documents folder as text --> returns the path to the documents folder of the current user
tell application "Microsoft Excel"
activate
open documentsFolder & "20/101.xlsx"
end tell
which works perfect in documents.
I have been trying to replace this finder script with Excel path for the last few hours,
tell application "Finder"
set getPath to "ttt"
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
(*replace values in workbook with "excel"*)
end repeat
end tell
this works,the problem is that it always opens on the front window, I can’t run it in the background.
thanks
bills
I’m sorry, I don’t understand exactly what you mean.
If you want to specify the files with the Finder but open it in Excel use this
set getPath to "ttt"
tell application "Finder" to set fileList to every file of folder getPath
repeat with i from 1 to count fileList
set thisFile to item i of fileList as text
tell application "Microsoft Excel"
open thisFile
-- do something
(*replace values in workbook with "excel"*)
end tell
end repeat