Hi there i am fairly new to applescript although not new to programming, i am trying to write an end to end solution that will convert an excel work book with multiple sheets in to a single CSV file. The CSV will be converted to xml to be used with adobe illustrator to make multiple graphs/charts.
set templateFile to choose file with prompt "Select the Excel workbook to convert:"
set theWorkbookName to name of (info for templateFile)
set theFileInfo to info for templateFile
tell application "Microsoft Excel"
open templateFile
set theSheets to sheets of the active workbook
repeat with nextSheet in theSheets
set sheetName to name of nextSheet
activate object worksheet sheetName
tell active sheet
insert into range range "a1:z1" shift shift down
set value of cell "a1" to sheetName & "_ZZZ"
end tell
copy range (range "A1:Z100" of sheetName of active workbook)
tell sheet "Sheet3" of active workbook
if value of range "A1" = "" then
set rNext to range "A1"
else
set rNext to get offset of range ("A" & (first row index of (get end range ("A" & (count rows)) direction toward the top))) row offset 1
end if
paste worksheet destination rNext
end tell
end repeat
save the active workbook in theWorkbookName & "go.csv"
The problem is that when i open the exported csv i get the first 3 sheets plus the first 2 sheets again
i.e. i get 5 sheets in total ( actually 6 as sheet3 is also repeated, however this is because it is part of the active sheets).
Is there anyway to either copy to an external file then export that file as 1 sheet, or failing that copy the current sheet to the next sheet and then export the final sheet as the active sheet.
and finally how do i create a variable that could store all the copied sheets then write that to a single sheet…
any help would be fantastic!
keith
Model: G4
AppleScript: 2.1
Browser: Safari 531.22.7
Operating System: Mac OS X (10.4)