Adding excel spreadsheet component

Scripting Forums AppleScript | Mac OS X
,
#1

Greetings guys, I’ve got a rather simple application that I need to modify. I’m just having a little difficulty with one part of it with regards to the new dictionary for Excel 2004.

What I essentially have is a script that retrieves information from a form in the following format:

First name
Last Name
[Radio buttons]: Current smoker, Ex smoker, Never smoked
[Radio] Male, female
[Check box] Proteinuria ( check or not check )

( And then about 5 other various columns of information ).

All of this information is assigned to global variables. I didn’t write some of the script, but I the radio buttons are linked to a matrix. (The matrixes go 0, 1, 2 from left to right, correct?)

This information is then calculated and output into a personalized document for medical patients.

But, the second task…the one I need help with, is putting the initial entry information into a .xls spreadsheet. Should be fairly easy, just not that familiar with doing it and I’m far from a proficient applescript user. What I would probably want to do would be take each of the initial variables and assign them to a row or column in an excel document, then just feed the information in. I would want to use a saved .xls file on a local drive (Don’t worry, multiple users wouldn’t be accessing at the same time).

So basically, I need code that will call Microsoft excel, activate it, open my specified worksheet, make sure it is open. Then this script should find the NEXT open column and automatically put the information into each sequential field, then save it. I just have to make sure not to overwrite existing information.

Main concerns would likely be:
#1. Finding next available columns ( I just couldn’t find a good way to do this in the excel dictionary ).
#2. Interpreting the Radio button matrices as smoking, nonsmoking, ex smoker, etc. I could theoretically leave these as their original [0,1,2] though.

This stuff seems pretty basic, I’ve just run into a few problems so I was wondering if anyone could help me. Some code is available on request.

#2

Sorry if I wasn’t clear enough on any part of my message, because I’ve got a lot of views but no replies and I know it can’t be all too difficult. Is there anything I should clarify or do to make it easier to respond to? I just couldn’t figure a few things out :shock: Help!

#3

Hi,

There are probably many ways to do this and I don’t know which is the best. Maybe others wait to post on this subject for the same reason. One way might be to use the usedRange property of worksheet. Then you can get the column after. Something like this:

tell application “Microsoft Excel”
tell front Workbook
tell front Worksheet
set data_range to UsedRange
set last_cell to last Cell of data_range
set new_col to (Column of last_cell) + 1
first Cell of Column new_col
end tell
end tell
end tell

I wonder what all those arrays and matrix commands and propeties are for. You might want to do some experimenting with these also. It might make things easier.

gl,

#4

thank you very much. in a few hours I will be at work and be able to provide some of the code. Does the last_cell intrinsically go by row or column? say…if i have 8 categories of information in each respective row…and i have two columns of information, i’d want it to go specifically to the next column ( which you have it doing), but will it know that based on data_range ?

#5

Hi,

When I run this in the Script Editor:

tell application “Microsoft Excel”
tell front Workbook
tell front Worksheet
set data_range to UsedRange
end tell
end tell
end tell

I get:

Range “R1C1:R3C3” of Worksheet “Sheet1” of Workbook “Workbook1” of application “Microsoft Excel”

The last cell’s row and column is the last row and column that contains data. The last cell may not contain data (eg. in my test spreadsheet, row 3 is the last row containing data and column 3 is the last column containing data).

Here, you don’t want to use Excel’s ‘lastCell’ command because the last cell may not contain data.

gl,

#6

Hi atomiks,

I had some time to make an example. Create an Excel spreadsheet file with some data in it. When the following script asks for the file, choose your test file.

– building a list of lists, sub_db
set rec1 to “field 1a
field 2a
field 3a”
set rec2 to “field 1b
field 2b
field 3b”
set rec1_list to paragraphs of rec1
set rec2_list to paragraphs of rec2
set sub_db to {} – list of lists
set end of sub_db to rec1_list
set end of sub_db to rec2_list
– the list of lists looks like this
– {{“field 1a”, “field 2a”, “field 3a”}, {“field 1b”, “field 2b”, “field 3b”}}
– usually, records are entered in rows, but in your case records are columns
set the_file to (choose file) as string
tell application “Microsoft Excel”
launch
Activate
Open the_file
tell front Workbook
tell front Worksheet
set d_range to UsedRange
set last_cell to last Cell of d_range
set temp_cell to last_cell
repeat with this_rec in sub_db
set new_col_num to (Column of temp_cell) + 1
set temp_cell to (first Cell of Column new_col_num)
repeat with this_field in this_rec
set Value of temp_cell to this_field
set temp_cell to (Offset temp_cell RowOffset 1)
end repeat
end repeat
end tell
end tell
end tell

Note that the Project Gallery needs to be turned off.

gl,

#7

Greetings! Thanks again kel for all your help and for going through that work.

So with the first part of your last script, what exactly is it doing? I’ll have data assigned to specific variables that should all match up evenly with the number of rows (if data is entered sequentially by column each time). All it will have to do really is find the used range, move over one column from the used range, then just spew out the information in each specific column (about 9 columns).

One problem I had building your script:


set temp_cell to (Offset temp_cell RowOffset 1)

It gave me a "Expected “,” but found identifier (-2741) error. I’m not exactly sure that that is about, but it wouldn’t let me finish building because of it. I will post some of my existing code in a second…

Also, in my existing script I often get a “No user interaction allowed” error. How can I fix that?

#8

You were right, I actually would want each record in rows. Thanks for pointing that out, as columns would be a real hassle for any statistics program. So yeah, rows are what I’d want.

Here is some of the original code. The part where it grabs the information from the form should be abll of the variables I need in the spreadsheet:


global theFN
global beta1, beta2, beta3, beta4, beta5, beta6, beta7
global delta, q0, R10
global test
global PROT, SEX, SMOK, AC, AGE, SBP, HDL, TC, DUR, LR, H, SMOK_original
global nonSmoke, exSmoke, currSmoke

global mySlide, this_presentation, myTable, myShapeTable
global statin_row_names
global statin_column_names
global gender_names
global age_names
global duration_names
global protein_names
global A1c_names, BP_names, chol_names, smoking_names
global myRowVars
global nRows
global myShape
global myPath

--row name constants so that we change alter the order of the table if needed
global rowSex, rowAge, rowDur, rowProt, rowA1c, rowBP, rowChol, rowSmoke


on clicked theObject
	--display dialog first item of statin_column_names
	if the name of theObject is "btnGenerate" then
		-- get all of the data
		set theFN to the contents of text field "fldFN" of window 1
		set theLN to the contents of text field "fldLN" of window 1
		--set contents of text field "fldAge" of window 1 to myVar
		set theFullName to theFN & " " & theLN
		--display dialog theFullName
		--set x to the number of matrices of window 1
		--set x to the name of the first cell of matrix 1 of window 1
		set SEX to (the current column of matrix "mtxGender" of window 1) - 1
		set AC to (the current column of matrix "mtxRace" of window 1) - 1
		set SMOK_original to the current column of matrix "mtxSmoking" of window 1
		if SMOK_original is not 2 then
			set SMOK to 0
		else
			set SMOK to 1
		end if
		--get the patient information from the form
		set H to the contents of text field "fldA1C" of window 1 as real
		--display dialog H
		set AGE to the (contents of text field "fldAge" of window 1) as number
		set SBP to the (contents of text field "fldSBP" of window 1) as number
		set HDL to the (contents of text field "fldHDL" of window 1) as number
		set TC to the (contents of text field "fldTC" of window 1) as number
		set DUR to the (contents of text field "fldDur" of window 1) as number
		set PROT to the state of button "btnProteinuria" of window 1
		
		--set PROT to the state of the button "btnProteinuria" of window 1
		--display dialog PROT
		
		--do some derived calculations
		set LR to TC / HDL

Then there is a bunch of unrelated stuff that creates a customized document based on some calculations done with that data, then I was starting my second part.


(* ***** creates a new excel worksheet to house the information ** *)
		createNewDocument()
		

on createDB()
	tell application "Microsoft Excel"
	launch
	activate
	open myPath & ":" & "statin.xls"
	set this_db to active sheet
	if not (exists sheet 1) then
	error "No database is open"
	end if
	set mySheet to the first sheet of this_db

(and this is likely where the data range part would come in, that is, assuming there are no problems in my code thus far).

Also…myPath should be previously defined in a part of the script that I didn’t include, although I could probably copy that code over again to this section of the script for now.

#9

Err any ideas on any of this? Most importantly, those error messages?