Hi All,
How do you create a table with specific headers and a totals row in an Excel worksheet? I have been trawling the forums and the Excel scripting dictionary to find the answer to this, and I can find no clear answer anywhere!!!
Any help would be gratefully received…
DDHawk
Hi
got this from mikerickson
tell application "Microsoft Excel"
activate
accept all changes active workbook
set columnToTest to range "G:G" of active sheet -- adjust to last column to be added
set cellCount to count of cells of columnToTest
set usedRanges to used range of worksheet object of columnToTest
set rowsCount to first row index of (get end cell cellCount of columnToTest direction toward the top)
set lastCol to (first column index of usedRanges) + (count of columns of usedRanges)
set formulaCells to get resize (cell 1 of column (lastCol) of active sheet) row size rowsCount
set formula r1c1 of formulaCells to ¬
"=SUM(RC[-7]:RC[-1])" --[-7] number of consecutive columns in a row to be added, the formula will apply to all used rows
end tell
hope it gives you a start
as to header row you can try this
set value of cell 1 of range "1:1" to "whatever" as text
regards
bills
Hi mikerickson,
Thank you very much for your post. I have run a test on the script that you sent through, and I do not think that it is quite what I was trying to achieve. I imagine that you are assuming that I have data in the Excel worksheet to start with, and that I am creating the table from a range of previously included data.
Unfortunately, what I am trying to create is a brand new worksheet based on a particular month value for the date of an event. This, I have done without a problem. Then, there should be a new table created into the new worksheet in order to subsequently add the data for that month. Basically, I am trying to future proof a script, so that no matter what the date of future entered events, there will always be a new worksheet added, and a new table produced.
So, I can set the header row topics, and this would dictate the column count. Then, I need to create and populate the table from there, with a sum of the last column.
I hope that this makes sense…
Kindest regards,
DDHawk
One approach to your problem would be to create a sheet with all the formulas and formatting the way that you want it.
Hide that sheet.
Then copy it, rename the copy to your taste, and work with the copy.
Next month, copy the hidden sheet, etc…
This template sheet (“template sheet” is not an Excel term, although “template workbook” is) approach will insure uniformity between the different sheets in your workbook.
All the formatting and writting headers, etc that create the template sheet would be done by hand, in Excel.
The copying and renaming could be done with AppleScript. (I’m at work on a Windows machine 
and cant write a script for that right now.)
Hi again,
The idea of a template worksheet was indeed the way that I was trying to go in the first place. It seemed to be the easiest solution. The ability to create the new worksheet as a copy of the template was easy enough. But, what got me stumped was that I seemed unable to effectively rename the named table range in the template so that I would be able to refer to it in the script.
In other words, I need new worksheets for each month, plus I need a uniquely named table in each worksheet. I am all set up in my head to do this by naming the worksheet something like, ‘Jan10Sheet’ and the range, ‘Jan10Info’. This, I can populate by using the monthValue and yearValue from my script entered by the user.
However, I was getting stuck because i did not seem to be able to satisfactorily rename the range of the table. Perhaps there is something that I am missing - perhaps this is an easy thing to do? I am sorry for the difficult questions, but this is beyond my current scripting understanding.
Thanks, as always, for your help…
DDHawk
I’m in a weird situation. At home my browser can’t find this site (can’t find macscripter.net), but at work its fine.
Here’s the script I came up with last night.
-- input month and year
set mmm to text 1 thru 3 of ((month of (current date)) as text)
set yyyy to (year of (current date)) as text
set uiMonth to choose from list {"Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Auq", "Sep", "Oct", "Nov", "Dec"} with title "Choose a Month" default items {mmm}
if uiMonth ≠false then
set uiyear to text returned of (display dialog "Enter Year" default answer yyyy)
set newSheetName to (item 1 of uiMonth) & uiyear
tell application "Microsoft Excel"
set myWorkbook to workbook "Workbook1.xlsm"
set templateSheet to sheet "Template" of myWorkbook
set screen updating to false
set visible of templateSheet to sheet visible
copy worksheet templateSheet after templateSheet
set newSheet to sheet (1 + (entry_index of templateSheet)) of myWorkbook
try
set name of newSheet to newSheetName
on error
display dialog "Sheet name already exists"
set display alerts to false
delete newSheet
set display alerts to true
end try
set visible of templateSheet to sheet very hidden
set screen updating to true
end tell
end if
I’ll see what I can come up with for naming the table.
What is the name of the table on sheet “Jan2011”? What cells is it?
Alternate thought, relative sheet addressing for the table:
The named range Name: myRange RefersTo: !$A$1 will refer to A1 on the same sheet that a formula refereing to myRange is. The ! acts almost like a ThisWorksheet qualifier.
I have followed the suggested idea, and I am posting the current small set of test script:
tell application "Microsoft Excel"
activate
set myWorkbook to open workbook workbook file name "Macintosh HD:Users:Jackson:Documents:myTest.xlsx"
if (exists (worksheet ("Dec10Lessons"))) is false then
copy worksheet sheet "Template" after last sheet
set name of last sheet to "Dec10Lessons"
tell worksheet "Dec10Lessons"
set name of range "a2:h2" to "Dec10Table"
end tell
end if
end tell
I created a test workbook, myTest, with a worksheet “Template”, within which is a sample table with the header row and total rows as I wish. The script is operating exactly as I wish - creating the new worksheet with a new table, and renaming the table as requested, but it is posting an error anyway:
error “Microsoft Excel got an error: Can’t set name of range "a2:h2" of worksheet "Dec10Lessons" to "Dec10Table".” number -10006 from name of range “a2:h2” of worksheet “Dec10Lessons”
If I can stop the error, I may have the solution. I suspect that there is something inherently wrong with the script. Any ideas?
DDHawk
Hi
Your named range worked for me.
set documentsFolder to path to documents folder as text
tell application "Microsoft Excel"
activate
open documentsFolder & "my test.xlsx"
if (exists (worksheet ("Dec10Lessons"))) is false then
copy worksheet sheet "Template" after last sheet
set name of last sheet to "Dec10Lessons"
tell worksheet "Dec10Lessons"
set name of range "a2:h2" to "Dec10Table"
end tell
end if
select range "Dec10Table"
end tell
I’m on office 08
bills
Hi Bills!!
I am using Office 2011. That is exactly the point - it worked as expected, but still raised the error???
Sorry - I have just looked at the Applescript replies box, and it is stating:
error number -1708
If that helps to identify the problem…
DDHawk
I’ve found that there is no error if you avoid telling a sheet
tell application "Microsoft Excel"
set name of range "A1:A10" of worksheet "Sheet1" to "myName"
end tell
Sorry!! I am still getting the same error message, even when I take out the ‘tell’ block on the worksheet!!!
If you are both able to do this using the logical script that I have produced, what else could be causing the error???
Going NUTS!!!
DDHawk!!
Is the worksheet protected? or anything like that anywhere in the workbook? All sheets are visible?
There is no protection. The only thing that I cannot understand is that the script is working exactly as intended!! It is creating the new worksheet. It is renaming the table range, as I have asked it to do. It is only then that it is coming up with the error???
The only thing that I can assume is causing a problem is something to do with these table types in Office 2011. There does not seem to be anything unusual, but…
What I know is happening is as follows:
The “Template” worksheet with the “TempTable” which has a header row, a totals row, and a data input row of a2:h2
is being copied to a new worksheet. The name of the worksheet is successfully changing to “Dec10Lessons”. The table also seems to be changing it’s name to “TempTable3” at that point by default. Subsequently, it is changing the name of the table range to “Dec10Table”, and then the error is coming up.
I don’t know if this helps to direct your thoughts at all…
Thanks for the trouble…
DDHawk
Any merged cells?
Try this test. Run your script. See the error message. And then run this script. Just to test if there is something unusual about the workbook/worksheet.
tell application "Microsoft Excel"
set name of range "Dec10Table" to "Smith"
end tell
Hi again!!
As I thought, the situation is exactly the same…the error reply that comes back from the script is as follows:
tell application “Microsoft Excel”
set name of range “Dec10Table” to “Smith”
→ error number -1708
Result:
error “Microsoft Excel got an error: Can’t set name of range "Dec10Table" to "Smith".” number -10006 from name of range “Dec10Table”
However, the table changes it’s name as ordered in the script. It is very clear that the table is changing it’s name as ordered.
It has to be something to do with the new table styles???
Wait… I will just try something…
Yes - I have saved the original workbook with a template sheet “Template” without a name that I have defined (previously, I called the table range “TempTable”). In this situation, the new worksheet is created, the new table is created, and the script worked. So, the script seems to be only coming up with an error when the template table has been assigned a particular name.
This is not making a great deal of sense to me because when I examine the Excel document after running the script, it has defined two range names itself, “Table1” for the template table, and “Table13” for the newly created table. Therefore, I am left with two names for the same range when the script is finished?!?!
So, I guess I have success, but it seems to be success with confusion attached…
Is there a way to change the name of a defined range - perhaps this would completely resolve the situation???
Gradually moving forwards…
DDHawk
So, now, I am doing some experimentation to find out what is going on…I am running the following scipt
Have you tried wrapping the script in a try block.
Sorry, I don’t understand why you would use a try block in these circumstances?
The script now works. It is now only the issue of the range being named with two names that is confusing.
What I had hoped to achieve was a single named range for each worksheet which I have defined so that I can manipulate it later.
Where are you referring to a try block being used?
Regards,
DDHawk.
A try block is Applescript error handling. (The more I think about it, the less I think it will help.)
http://developer.apple.com/library/mac/#documentation/AppleScript/Conceptual/AppleScriptLangGuide/reference/ASLR_control_statements.html%23//apple_ref/doc/uid/TP40000983-CH6g-128973
I can’t reproduce the error and am running out of possibilites.
Have you tried opening a new workbook and mocking up your sheet and trying the script?
Hi again mikerickson!!!
I am not really sure if this is an error any more, or a standard way that Excel works. I am trying everything on a dummy file, and it is working fine. The issue is only these table names which seem to be impossible to delete or change in any way. They are self-generated by Excel, and there is no apparent way to get hold of them. It does not seem to interrupt the working of my script, so I am only really posting this for future readers, or for anyone who knows why this occurs and how to control it…
I tried the following Applescript to test the system:
tell application "Microsoft Excel"
activate
set myWorkbook to open workbook workbook file name "Macintosh HD:Users:Jackson:Documents:myTest1.xlsx"
accept all changes active workbook
if (exists (worksheet ("Dec10Lessons"))) is false then
copy worksheet sheet "Template" after last sheet
set name of last sheet to "Dec10Lessons"
set name of range "a2:h2" of worksheet "Dec10Lessons" to "Dec10Table"
apply names range "Dec10Table"
end if
if (exists (worksheet ("Nov10Lessons"))) is false then
copy worksheet sheet "Template" after last sheet
set name of last sheet to "Nov10Lessons"
set name of range "a2:h2" of worksheet "Nov10Lessons" to "Nov10Table"
apply names range "Nov10Table"
end if
get name of every named item of active workbook
end tell
[b]The reply from this was as follows:
Result:
{“Dec10Table”, “Nov10Table”}[/b]
This would be exactly as you would expect. However, if you go to the ‘defined names’ menu item in Excel it also lists three table ranges - Table1, Table13, and Table14 - Table13 is referring to the same range as Dec10Table, and Table14 is referring to the same range as Nov10Table. So the question is, why is the reply from the script not including those named items as well?
I would like to thank mikerickson and bills for their incredible help with this puzzling matter. I hope that someone will eventually be able to answer the remaining question…
All the best,
DDHawk
Hi
I have been unable to duplicate your errors, or your list of named ranges,though I did notice that the list refers to whatever range is selected.
If you are intrested in the error codes, they were posted by regulus6633 at http://macscripter.net/viewtopic.php?id=30109.
bills