Make a table in an Excel worksheet

[bills]

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

[DDHawk]

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

[mikerickson]

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.)

[DDHawk]

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

[mikerickson]

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.

[DDHawk]

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

[bills]

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

[DDHawk]

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

[mikerickson]

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

[DDHawk]

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!!

[mikerickson]

Is the worksheet protected? or anything like that anywhere in the workbook? All sheets are visible?

[DDHawk]

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

[mikerickson]

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

[DDHawk]

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

[mikerickson]

Have you tried wrapping the script in a try block.

[DDHawk]

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.

[mikerickson]

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?

[DDHawk]

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

[bills]

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

[DDHawk]

Hi bills,

Thanks for that - it could prove to be a very useful link in the future!!!

All the best,

DDHawk