Help With Db Query Of Excel File.

[Guest Mark Connelly]

Hi -- we are testing the Katalina software to use for getting survey data as part of our research studies. We've set up a basic application that seems to work okay. However, I would like to set it up such that the information the caller enters gets stored into an Excel file. I tried setting up an Excel file, configured the ODBC Data Sources as per the Katalina help guide, and then tried setting up a DB query in the script so that it would insert the caller's information into the appropriate rows of the Excel sheet (i.e., it would insert the caller's ID that he/she enters at the beginning of the phone call followed by the answers he/she gives to each question). I did this using an "Insert Into" SQL query:

e.g., INSERT INTO Sheet 1 (ID, q1, q2, q3) VALUES ('$RV[GetID]', '$RV[Getq1]', '$RV[Getq2]', '$RV[Getq3]')

 

I am a relative novice at this and in particular using query language. But it would be much appreciated if you could let me know how to write the query so that each time a caller calls, the ID number and responses to all questions are recorded in an Excel sheet (that already has the appropriate headings), with the goal being that we would not have to hand-enter all the information from the caller log but rather all the information would already be entered into the appropriate columns of an Excel sheet.

 

Thanks!

 

Mark

[SupportTeam]

For samples of VBScript code that saves data into Excel please see: http://www.voiceguide.com/vghelp/html/modVbs.htm

 

You should probably have a set cell which stores the 'next row to write into' value - and add 1 to that cell when adding a new row of data - so that next time the data gets written into the next row...

[Guest Mark Connelly]

Thanks for the response. Being an extreme novice, can you take me through how to "set a cell in Excel to store the 'next row to write into' value - and add 1 to that cell when adding a new row of data - so that next time the data gets written into the next row... ". This is exactly what I want to do I think, but am really unfamiliar with the language. Also, from the sample of saving data to Excel using a VB script, can I paste that directly without having to modify anything?

 

Sorry in advance for my inexperience! Thanks and take care,

Mark

[Guest Mark Connelly]

One more addendum -- I assume I would need to change the VB script in order for the program to know that it needs to go to the next Excel row to enter the data from a given call (so as not to overwrite preexisting data). Again, being a novice, it would be really helpful to know the exact addition to the VB script to get it to do so. For example, if I'm undestanding correctly, I would need to set up cells in Excel that store the current row information. How would I then incorporate that into the VB script so that it writes into the row corresponding to 1 plus the cell entry?

 

Also, will this get screwed up if someone enters an erroneous number and then goes back and changes their resposne? (i.e., will this then put the data into a different column so that it wouldn't correspond to headings that I'd like to put in there in advance, such as ID, response to question 1, response to question 2, etc.)?

 

Hope those questions make sense. Thanks!

 

Mark