I am trying to save an excel workbook in a particular folder on an external drive but failed to do so.
The path to where to save is a cell value in my excel sheet.
Here is the real value of the cell"AA1": /Volumes/Groups/sales/Selling Prices Categories/Home | Garden/Christmas Balls
Actually this is the place where i want to save my workbook.
And here is my script! If anyone could help debugging it?
----------------------------------
-- Transfer Data to Excel
tell application "Microsoft Excel"
-- Get Excel to activate
activate
set FOBSheet to worksheet "FOB" of active workbook
set fileName to value of cell "D3" of FOBSheet
----------------------------------------------
set sellingPricesCategoryPath to value of cell "D3"
set fn to fileName
set CSPrefix to "C&F & DDP "
set CSFileEnd to " clb"
set productpath to value of cell "AA1"
--Saving the Calculation sheet file in Kiki.
save active workbook in (POSIX file productpath & CSPrefix & fn & CSFileEnd & ".xls") as Excel98to2004 file format
--close active workbook saving no
end tell
--Print opened FOB Sheet # 2014/09/17 Added by Claude
---------------------------------------
tell application "System Events"
-- Select the Print menu item
keystroke "p" using command down
delay 3
tell window "print"
display dialog "Choose Printer"
delay 5
keystroke return
end tell
end tell
POSIX file returns a file URL specifier, it’s not possible to extend the path with literal text.
The solution is coercing the file URL specifier to text.
I suspect that “Christmas Balls” is also a folder so I added an extra colon path separator
save active workbook in ((POSIX file productpath as text) & ":" & CSPrefix & fn & CSFileEnd & ".xls")
It seems that the path is not found as nothing is saved to the christmas ball folder and here is the replies that I have from editor.
tell application “Microsoft Excel”
activate
get worksheet “FOB” of active workbook
→ worksheet “FOB” of active workbook
get value of cell “D3” of worksheet “FOB” of active workbook
→ “ZWMi14091902 - 20-50K Plastic Flat Christmas Ball TELEKOM”
get value of cell “D3”
→ “ZWMi14091902 - 20-50K Plastic Flat Christmas Ball TELEKOM”
get value of cell “AA1”
→ “/Volumes/Groups/sales/Selling Prices Categories/Home | Garden/Christmas Balls”
get POSIX file “/Volumes/Groups/sales/Selling Prices Categories/Home | Garden/Christmas Balls”
→ current application
save active workbook in “:C&F & DDP ZWMi14091902 - 20-50K Plastic Flat Christmas Ball TELEKOM clb.xls” as Excel98to2004 file format
end tell
(1) I don’t use Excel but I am not sure that it may save a document in a string as you urge it to do with :
save active workbook in “:C&F & DDP ZWMi14091902 - 20-50K Plastic Flat Christmas Ball TELEKOM clb.xls” as Excel98to2004 file format
(2) POSIX path don’t begin with the name of the storage volume but HFS paths don’t.
Here the path is defined by a string starting with a colon which is odd.
(3) As POSIX file is a component of an OSAX (Standard Additions), it’s bad practice to call it in a tell application block.
Would be better to code :
tell me to set pPathAsText to (POSIX file productpath) as text
save active workbook in file (pPathAsText & CSPrefix & fn & CSFileEnd & ".xls") as Excel98to2004 file format
If Excel may use a string as parameter, use
tell me to set pPathAsText to (POSIX file productpath) as text
save active workbook in (pPathAsText & CSPrefix & fn & CSFileEnd & ".xls") as Excel98to2004 file format
(4) Re reading your script, I point the fact that productpath is grabbed from a cell so it’s already a string.
So you have just to use :
save active workbook in file (productPath & CSPrefix & fn & CSFileEnd & ".xls") as Excel98to2004 file format
or
save active workbook in (productPath & CSPrefix & fn & CSFileEnd & ".xls") as Excel98to2004 file format
Yvan KOENIG (VALLAURIS, France) lundi 22 septembre 2014 14:58:53
set fn to "fileName"
set CSPrefix to "C&F & DDP "
set CSFileEnd to " clb"
set productpath to "/Volumes/Groups/sales/Selling Prices Categories/Home | Garden/Christmas Balls"
set filepath to productpath & CSPrefix & fn & CSFileEnd & ".xls"
-->"/Volumes/Groups/sales/Selling Prices Categories/Home | Garden/Christmas BallsC&F & DDP fileName clb.xls"
Spot the errors…
I can’t, I dont’ see what it really should be.
Did you check Excel’s dictionary?? It says the save command takes a plain path, not a specifier. So text, not file this-or-that.