Saturday, July 2, 2022

#1 2022-06-17 12:07:56 pm

nankm
Member
Registered: 2022-03-07
Posts: 1

Excel script to save template as a new name

I have two documents: A list of file names in a csv, and an excel Template that contains conditional formatting. I'd like to open the template amd save it to another folder with the name of each file name in the csv. This script seems to be working right up until saving the file. Any thoughts?

tell application "Finder"
   
    set theFolder to choose folder with prompt "Choose your ouput folder"
   
    set the theTemplate to choose file with prompt "Choose the template"
    set theSource to choose file with prompt "Choose your reference list"
   
    tell application "Microsoft Excel"
        activate
        open theSource
        tell worksheet theSource
            set myData to value of used range
            set myList to items of myData
            set theCount to count of items of myList
           
            repeat with i from -1 to theCount
                open theTemplate
                tell document theTemplate
                    activate
                    set TheName to i as string
                    set destinationPath to theFolder & TheName
                    save active workbook in destinationPath
                   
                end tell
            end repeat
        end tell
    end tell
end tell


Filed under: template, excel, save as

Offline

 

#2 2022-06-18 12:03:33 pm

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 203

Re: Excel script to save template as a new name

You don't say how it doesn't work. What happens? Why make people guess?

Some random thoughts…

Why put everything inside a Finder tell block?

You use 'i' as a counter. First, why start at -1? Try '1'. Second, you probably want to refer to (i.e. loop through) 'item i of mylist'. Third, mylist is a list of lists and probably needs to be worked on to turn it into a list of texts.

I think it is redundant to use 'active workbook' within a tell document block.

Try using 'save as' instead of 'save'. I think 'template file format' would be the file format you want.

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)