New learner needs help! Excel related

Jake11 · Post by **Jake11** » Thu Mar 19, 2009 7:46 pm

Hey to all,

I need some help!
Hereâ€™s some background on my problem.

I am new to revolution and am not actually a programmer (my specialty is actually in Industrial/Organizational Psychology). However, I spent some time with programmers this past summer who taught me a few things with revolution. I have since been working with a non-profit company working on improving their assembly line production. The goal is provide them a means to collect data about daily production for each employee without hindering their ability to work.
This part, I have taken care of. As a background, we have two systems. For the more intensive assembly line projects, we have developed rigs that allow them to step on a button to activate a stopwatch with a lap timer. This is imported to a computer. THAT part is done with as far as myself or revolution is concerned.

Part two, however, is a bit more difficult, and is what I need your help in. This is the part that involves revolution. The other means for data collection is to have the employees for the â€œsimpler tasksâ€

Janschenkel · Post by **Janschenkel** » Fri Mar 20, 2009 7:34 am

Hi Jake,

There are basically two ways to go about this:

Use AppleScript/VBScript to get/set the value of individual cells in your Excel sheet
Create text files that Excel can read

The quickest route programming-wise is probably to use AppleScript/VBScript - and Ken Ray has an excellent page on his website: http://www.sonsothunder.com/devres/revo ... iac001.htm

As for exporting text files, you can create tab-delimited text files or CSV files, or XML-files in Microsoft's SpreadsheetML structure.

HTH,

Jan Schenkel.

Jake11 · Post by **Jake11** » Sun Mar 22, 2009 5:23 pm

Janschenkel wrote:Hi Jake,

There are basically two ways to go about this:
Use AppleScript/VBScript to get/set the value of individual cells in your Excel sheet
Create text files that Excel can read
The quickest route programming-wise is probably to use AppleScript/VBScript - and Ken Ray has an excellent page on his website: http://www.sonsothunder.com/devres/revo ... iac001.htm

As for exporting text files, you can create tab-delimited text files or CSV files, or XML-files in Microsoft's SpreadsheetML structure.

HTH,

Jan Schenkel.

Thanks Jan,

I actually stumbled across the sonsothunder website in my searches for help. Unfortunately, I can't seem to get anything to work. Maybe because I am not familiar with applescript. One limitation I do have, however, is that I am creating this app on a mac, but it will eventually be used on a pc.

How do I export tab delimited text files? How could I set that up on a row x column basis?

Thanks a lot!

Janschenkel · Post by **Janschenkel** » Sun Mar 22, 2009 6:19 pm

Exporting a tab-delimited text file can be as simple as:

Code: Select all

on mouseUp
  local tFilePath, tData
  ask file "Save as tab-delimited text file:"
  if it is empty then exit mouseUp
  put it into tFilePath
  put "Row1Column1" & tab & "Row1Column2" & return & \
      "Row2Column1" & tab & "Row2Column2" & return & \
      "Row3Column1" & tab & "Row3Column2" \
      into tData
  put tData into URL ("file:" & tFilePath)
end mouseUp

So basically assemble the data, putting a tab between each column and a return between each row; then put it into a local file URL.

HTH,

Jan Schenkel.

mwieder · Post by **mwieder** » Sun Mar 22, 2009 8:11 pm

Jake- that'll get you a tab-delimited text file to import into Excel. I had to do something similar a few months ago, and went the next step - automating the import process to get the file into Excel. Ken Ray tells me that this is not importing data, but creating a link from Excel to the text file, but it worked for my purpose. I created a custom property of the stack called "ExcelImport" that contained the following VBScript:

Code: Select all

set objExcel= CreateObject("Excel.Application")
objExcel.Visible = True

dbString= $FILELOCATION
connString = "TEXT;" + dbString

objExcel.Workbooks.Add
Set shFirstQtr = objExcel.Workbooks(1).Worksheets(1) 

Set qtQtrResults = shFirstQtr.QueryTables.Add(connString,shFirstQtr.Cells(1,1))
with qtQtrResults
'	.TextFileParseType = xlDelimited
	.TextFileTabDelimiter = True
	.Refresh
end with

set objExcel = Nothing

Then I call the following handler to launch Excel and import the text file (after finding the text file with "answer file" and passing the path the file as pFile):

Code: Select all

on SendToExcel pFile
    local tScript
    put the ExcelImport of this stack into tScript
    -- put the actual file location into the script
    replace "$FILELOCATION" with quote & pFile & quote in tScript
    try
        do tScript as "VBScript"
    catch e
    end try
end SendToExcel

Jake11 · Post by **Jake11** » Mon Mar 23, 2009 4:40 pm

Janschenkel wrote:Exporting a tab-delimited text file can be as simple as:
Code: Select all
on mouseUp
  local tFilePath, tData
  ask file "Save as tab-delimited text file:"
  if it is empty then exit mouseUp
  put it into tFilePath
  put "Row1Column1" & tab & "Row1Column2" & return & \
      "Row2Column1" & tab & "Row2Column2" & return & \
      "Row3Column1" & tab & "Row3Column2" \
      into tData
  put tData into URL ("file:" & tFilePath)
end mouseUp
So basically assemble the data, putting a tab between each column and a return between each row; then put it into a local file URL.

HTH,

Jan Schenkel.

This seems to be the most "reasonable" for good ol' me. Here's a silly question however, is that code going into the card script, or the "submit" button script?

Is this as simple as replacing "row2column1" with the name of the textfield I want entered?

Then, if I can make this work, what's an efficient way to clear the data? A clear button that removes it all via code?

Thanks a lot, you all have been very helpful for me so far

mwieder · Post by **mwieder** » Mon Mar 23, 2009 5:56 pm

Not a silly question at all: the question of *where* to put code is IMO the paramount one in programming objects. There's no hard and fast rule, but my rule of thumb in this situation is

mouseUp handlers go into the button that gets the mouseUp event *unless* that code will be used by multiple buttons, in which case I might move it down to the card level.

Is this as simple as replacing "row2column1" with the name of the textfield I want entered?

Almost. Replace "Row2Column1" with the word "field" and the name of the field.... sheesh... English syntax gets very weird trying to describe programming... how about this

Code: Select all

    put field "data1" & tab & field "data2" & return & \
          field "data3" & tab & field "data3" & return
    ...etc.

and yes, I think a button to clear the fields would be a simple solution. I suppose once you're sure the export code is working you could add that code at the end of the mouseUp handler to make it a single-click action.

Jake11 · Post by **Jake11** » Mon Mar 23, 2009 6:58 pm

Phew. Finally starting to get somewhere... Thanks.

I am finally getting it to prompt me to save the txt file somewhere. Once I saw that, I was happy. So I put in some random data into the first 6 sets of fields that I want to save to a .txt and tried to make it work. Here is the code I put in the submit button script.

Code: Select all

on mouseDown 
   local tFilePath, tData 
   ask file "Save as tab-delimited text file:" 
   if it is empty then exit mouseDown  
   put it into tFilePath
   put field "Task1Time" & tab & field "Task1Number" & return & \
   field "Task2Time" & tab & field "Task2Number" & return & \
   field "Task3Time" & tab & field "Task3Number" 
   into tData 
   put tData into URL ("file:" & tFilePath) 
end mouseDown

Now, unfortunately, when clicking "Save" on the prompt, it jumps me back to the script editor and this line:

put field "Task1Time" & tab & field "Task1Number" & return & \

Leaves me with an error saying this:

button SUBMIT: Execution error at line 10 (Chunk: no such object) near "Task1Time", char 4

I am lost on what to do. I have double checked that the text field is really named "Task1Time" and it is, and I'm not sure what other problems there may be. Am I trying to "signal" the field in the wrong way?

Thanks a lot,
Jake

mwieder · Post by **mwieder** » Mon Mar 23, 2009 7:23 pm

Close. You're missing a "\" (line continuation character) at the end of the line before "into tData".

Jake11 · Post by **Jake11** » Mon Mar 23, 2009 7:29 pm

mwieder wrote:Close. You're missing a "" (line continuation character) at the end of the line before "into tData".

Hm.. added that "", yet I am still getting the same error (referring to the "Task1Time" section still).

Any ideas?

EDIT:

I have a programmer (who is NOT familiar with revolution) sitting across from me, and asked him what he thought about the error. He figured I should give you guys some more background on this setup.

It seems that it may be because "field Task1Time," as well as every other field listed next is on a separate card.

I never explained this before, but The submit button is on one card, as well as the radio buttons to get to the corresponding cards for the various tasks are also on the first card.
However, the actual task fields that I am trying to call from to enter into a .txt file are on different cards.

Should there be a part of the code I have been using to link to this? For instance "put card "2" field "Task1Time" & tab & ....."

Maybe that is off base. Could I also refer to the fields by ID number rather than name?

Jake11 · Post by **Jake11** » Mon Mar 23, 2009 8:10 pm

Ok.

So it seems that above post had some truth to it.

i started a new project, put the submit button, as well as the fields all on the same stack and used that code above. It worked. Contents (numbers) from the field saved as a .txt. file with tabs.

IT WORKED

However, I added a second card to this, added another button, and tried to submit the data from another card. It no longer worked and I received the same error.

Unfortunately, I cannot revamp the card to have everything on the same card, the employer wants individual pages for ease of use.

Any ideas? Group the fields together with the submit button?

EDIT:

Wow, Got it!

Tried

Code: Select all

Put field "Task1Time" of card "Task1Card" & tab & ...

ANd so on.

Now that's working...On to the next step![/quote]

mwieder · Post by **mwieder** » Mon Mar 23, 2009 9:42 pm

Dang! I didn't even get a chance to reply before you solved it... happily, most of the time programming in rev if you can think of the problem in English you've got the answer coded.

On rethinking what you're doing, if you've got a lot of these to deal with and you're labeling things like I think you are, you might be better off coding this in a separate function, as in:

Code: Select all

function getFieldData pFieldNumber
  local tTime, tTask, tCard, tNumber
  local tData

  put "Task" & pFieldNumber into tTask -- "Task1"
  put tTask & "Time" into tTime -- "Task1Time"
  put tTask & "Number" into tNumber -- "Task1Number"
  put tTask & "Card" into tCard -- "Task1Card"
  --gather time info and separate with tab
  put field tTime of card tCard & tab into tData
  --gather task number and use cr to end data row
  put field tNumber of card tCard & cr after tData
  return tData
end getFieldData

then your mouseUp handler starts to look like:

Code: Select all

   put getFieldData(1) &  \
   getFieldData(2) & \
   getFieldData(3) \
   into tData

Jake11 · Post by **Jake11** » Mon Mar 23, 2009 9:51 pm

mwieder wrote:Dang! I didn't even get a chance to reply before you solved it... happily, most of the time programming in rev if you can think of the problem in English you've got the answer coded.

Seems so...except in my current case...
I am trying to do the "clear fields" type command (As a separate button for now) and not succeeding.
I am attempting now just to clear one of the fields as a test, but it never clears. I can't find help in the dictionary unfortunately, and this is a code I used awhile ago, but can't remember.

Any help?

mwieder · Post by **mwieder** » Mon Mar 23, 2009 10:02 pm

put empty into field "xyz"

OK - it's not quite as intuitive as "clear field", but it's pretty close.