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!
Browser: Safari 531.22.7
Operating System: Mac OS X (10.4)