Count the number of files in the folder which are xls files
Save EVERY worksheet by it’s name as a pdf in the same folder (ideally, I’d like to create a new folder within the folder based on the value of cell A3)
Do #3 above for each file found in the folder designated.
The code I’ve started with is:
set fol_der to choose folder --This is the folder where all the Excel files are located
tell application "Finder"
set scat_files to the name of every file in folder fol_der whose name extension is "xls"
end tell
repeat with pi from 1 to (count scat_files)
set open_file to (item pi of scat_files) as Unicode text
tell application "Microsoft Excel"
activate
open workbook workbook file name open_file
save mySheet in (ptd & filename & ".pdf") as PDF file format
close every workbook saving no
end tell
end repeat
For some reason, save as will not save one sheet by itself.
set fol_der to choose folder --This is the folder where all the Excel files are located
set ptd to "Mac OS X:Users:John:Desktop:Output"
tell application "Finder" to set scat_files to (every file of fol_der whose name extension begins with "xls") as alias list
repeat with aFile in scat_files
tell application "System Events" to set {filename, fileExt} to {name, name extension} of aFile
set baseName to text 1 thru ((get offset of "." & fileExt in filename) - 1) of filename
tell application "Microsoft Excel"
open aFile
activate
set myWorkbook to active workbook
set mySheets to every worksheet of myWorkbook
repeat with aSheet in mySheets
set cellValue to value of range "A3" of (contents of aSheet)
--if cellValue = "" then set cellValue to (current date)'s time string as text
set destFolder to ptd & ":" & cellValue
tell me to makeFolder(destFolder)
set destFile to destFolder & ":" & baseName & ".pdf"
save as (contents of aSheet) filename destFile file format PDF file format
end repeat
close every workbook saving no
end tell
end repeat
on makeFolder(myPath)
do shell script "mkdir -p " & quoted form of (myPath's POSIX path)
end makeFolder