Beginners Guide to PLX DAQ v2 by Net^Devil (Revision 1)
Table of content 1.
About the the progra program m and its its use....... use............... ............... .............. ............... ............... .............. ............... ............... .............. .............. ............... .......................1 ...............1
2.
How to use........ use................ ............... .............. ............... ............... .............. .............. ............... ............... .............. ............... ............... .............. .....................................2 ..............................2
3.
The Excel Excel UI part............... part...................... .............. ............... ............... .............. .............. ............... ............... .............. ............... ............... .............. ............... ............... ...............2 ........2
4.
The Arduino Arduino part part o!er!ie o!er!iew....... w............... ............... .............. ............... ............... .............. .............. ............... ............... .............. ............... ............... ...................4 ............4
".
The Arduino Arduino part #asic setup setup and commun communica$ ica$on........ on............... ............... ............... .............. ............... ............... .............. ...................4 ............4
%.
The Ardui Arduino no part part speci&c speci&c communi communica$ ca$on on and manipula$ manipula$on......... on................ .............. ............... ............... ........................" ................."
'.
The Ardui Arduino no part part Excel Excel wor(boo wor(boo( ( commands. commands......... ............... .............. ............... ............... .............. ............... ............... .........................' ..................'
).
The Arduino Arduino part part *iscellaneo *iscellaneous us comman commands...... ds............. .............. ............... ............... .............. ............... ............... ..............................) .......................)
+.
,oing -our own stu....... stu............... ............... .............. ............... ............... .............. .............. ............... ............... .............. ............... ............... ..............................+ .......................+
1/.
Upgrading Upgrading 0 ,A !2 Excel Excel !ersions.. !ersions......... ............... ............... .............. .............. ............... ............... .............. ............... ............... .....................+ ..............+
11.
ull blown demo........ demo................ ............... .............. ............... ............... .............. .............. ............... ............... .............. ............... ............... .............. .........................+ ..................+
1.
About the program program and its use 0 ,A !2 is a program used to establish an eas- communica$on between *icroso5 Excel on a 6indows 7omputer and an- de!ice that supports serial port protocol. It was inten$onallinten$onall- wri8en to allow communica$on between Arduino and Excel. 9ou can: ;or example: measure temperature data with -our Arduino: send the results to Excel e!er- 1/ seconds: printed the data on a sheet and draw a graph with all in;orma$on. All communica$on will be done b- Serial.println commands
void loop() { Serial.println( (String) "DATA,DATE,TIME," + millis() ); delay(!!);
#eginners >uide to 0 ,A !2 b- ?et@,e!il re!1B
0age 1 o; 11
loo(s li(e the ;ollowing in ExcelC
2.
How to use The program uses two parts to wor(C the special *icroso5 Excel =preadsheet with the 0 ,A !2 UI and commands plus an- Arduino de!ice that sends special commands ;or communica$on. The latest Excel =preadsheet can be downloaded on the Arduino ;orum in(C h8pCDD;orum.arduino.ccDindex.phptopicF43'3+).msg3/13'%1Gmsg3/13'%1 please alwa-s download the latest !ersionB: the Arduino code can be wri8en b- e!er-one themsel!es with below guideline.
3.
The Excel UI part A5er opening the Excel =preadsheet please allow running the macro -ellow warning message at the topB. A5erwards -ou can see the 0 ,A !2 UI. In case not please clic( the bu8on JKpen 0 ,A UIL on the J=imple ,ataL sheet. The UI oers the ;ollowing op$onsC
#eginners >uide to 0 ,A !2 b- ?et@,e!il re!1B
0age 2 o; 11
a. 0ortC set to Arduino port same as in Arduino I,E FM Tools FM 0ort: e.g.: 4 ;or 7K*4 b. #audC set to the baud rate -ou run -our Arduino on e.g.: +%// i; -ou are using
Serial.#egin($%!!); in -our Arduino codeB c. 7onnectC connects to -our Arduino and starts logging d. 0ause loggingDresume loggingC when connected will pause the logging o; data e. Neset TimerC will set the Timer to /. The Timer can be used to measure how long ;.
Excel is alread- logging 7lear 7olumnsC will delete all logged data ;rom the sheet. 6onOt clear the labels o;
the columns g. ,ispla-DHide direct debugC will show or hide the text &eld on the right. The ,irect ,ebug 6indow can be used to manuall- monitor commands recei!ed b- 0 ,A !2 in Excel h. =heet name to post toC this will list all sheets in the Excel wor(boo(. 6hate!er sheet -ou select in the dropdown the logged data will be posted to it. This sheet will be re;erred to as the JAc$!e=heetL throughout this document. ?oteC a5er adding D dele$ng sheets please press the small JoadL bu8on on the le5 i.
side o; the dropdown box ;or the sheet list to be updated 7ontroller *essagesC in the &eld below the most recent commands and status in;orma$on will be shown. *ost li(el- the in;orma$on is changing wa- too ;ast ;or
<.
-ou to read: thus use the ,irect ,ebug 6indow Neset on 7onnectC the chec(box should be $c(ed at all $me. I; $c(et the &rst command ;rom Excel to Arduino will be to restart: thus -our code starts ;rom the beginning as well. This wa- -ou can ha!e a ;resh session. I; -ou want to connect to
(.
-our Arduino without restar$ng it
l.
detail on these can be ;ound below. og incoming data chec(boxC in;orma$on recei!ed ;rom Arduino will be displa-ed in
the direct debug window. HintC disable i; -ou experience per;ormance issues m. og outgoing data chec(boxC in;orma$on send to -our Arduino will be displa-ed in the direct debug window. HintC disable i; -ou experience per;ormance issues #eginners >uide to 0 ,A !2 b- ?et@,e!il re!1B
0age 3 o; 11
n. og s-stem messages chec(boxC in;orma$on ;rom Excel will be displa- in the direct debug window e.g.: errorsB . HintC disable i; -ou experience per;ormance issues o. Add $mestamp chec(boxC will add a $mestamp to e!er- in;orma$on logged in the direct debug window. This is hand- ;or debugging. p. FMC will increase the siQe o; the direct debug window to a certain maximum P. RFC will decrease the siQe o; the direct debug window to a certain minimum r. 7learC will clear all in;orma$on in the direct debug window
4.
The Arduino part - overview or 0 ,A !2 to wor( correctl- -our Arduino needs to send speciall- ;orma8ed commands. All commands need to be send ;rom Arduino to the 07 using the Serial.println commands. These commands can include parameters: !ariables and ;unc$ons to send to as well. These parameters need to be separated b- commas. This can be done li(e thisC
Serial.println( (String) "DATA,DATE,TIME," + millis() ); These commands can be split up into dierent categoriesC a. #asic setup and communica$onC 7ommands here are used to ;ormat the sheet to log to and to send data to the sheet b. =peci&c communica$on and manipula$onC 7ommands here are used to wor( with ;urther parameters:
Excel wor(boo( commandsC 7ommands here are used to control the logging process or e!en sa!ing wor(boo(s in window
d. *iscellaneous commandsC E!er-thing that is not reall- crucial or does not ha!e an- bene&t an-moreB
".
The Arduino part - Basic setup and communication a.
7EAN=HEETC This command clears all data the Ac$!e=heet including labelsB. It should be the &rst command on e!er- s(etch. =-ntaxC Serial.println(&'EAS*EET);
b. 7EAN,ATAC This commands clears onl- logged data on the Ac$!e=heet star$ng at row 2B =-ntaxC Serial.println(&'EADATA); c.
A#EC 6ith this command -ou can set the labels ;or the top most row o; the Ac$!e=heet
#eginners >uide to 0 ,A !2 b- ?et@,e!il re!1B
0age o; 11
=-ntaxC Serial.println(&AE,st 'ol-mn,nd 'ol-mn,T/ird one0); d. ,ATAC This is the most basic and crucial command o; 0 ,A !2. It is used to send data ;rom -our Arduino to Excel and ha!e it printed on the Ac$!e=heet. 9ou can send an-thing -ou want but -ou should ma(e sure -ou split the data up b- commas and match the number o; columns -ou de&ned with the A#E command. The reser!ed code words DATE: TIME and TIME will be recogniQed b- 0 ,A and will be replaced with !alues.
DATE will be switched to the current 6indows computerOs date e.g.: 12./3.2/1'B TIME will be switched to the current 6indows computerOs $me e.g.: 1)C1'C42B TIME will be switched to the $me the logging is alread- ac$!e e.g.: 1:3%" secondsB 9ou can add the (e-word A1T2S'23! to -our string. This in;orma$on will ma(e 0 ,A to automa$call- scroll -our Excel window down with e!er- new line o; data that is recei!ed. The number e.g.: !B indicates how man- lines should be shown abo!e the latest line that is scrolled to automa$call-. i. =-ntax 1 S this can be used to ma(e -ou code more readable but wor(s ;or sta$c in;o onl-C Serial.println("DATA,DATE,TIME); ii. =-ntax 2 S this can be used to include ;unc$on calls or !ariables in the same lineC Serial.println( (String) "DATA,DATE,TIME," + millis() ); iii. =-ntax 3 S this is a wa- to present the code with rather more lines but with a be8er readabilit- i; -ou use man- !ariables in one line. 0lease note that onlthe print command is used and println is onl- used at the end to send the ;ull- build up stringC Serial.print("DATA,DATE,TIME,); Serial.print(my4aria#leA);
Serial.print(",); Serial.print(millis()); Serial.print(",); Serial.println(my4aria#le);
%.
The Arduino part - speciic communication and manipulation a.
7E:=ET #- this command -ou can set the !alue o; an- cell in the Excel wor(boo( with an!alue -ou want. This can either be done on the Ac$!e=heet or on an- other sheet. =-ntax to set a !alue on the Ac$!e=heetC Serial.println("'E,SET,'$,My4al-e); =-ntax to set a !alue on a named sheet somewhere in the wor(boo(C Serial.println("'E,SET, 25S*EET,AnyS/eet,',$,My4al-e); PL!A"! N#$! %% there might occur problems at higher baud rates with the
7E:=ET command. It might happen that !alues are not trans;erred correctl- S #eginners >uide to 0 ,A !2 b- ?et@,e!il re!1B
0age & o; 11
instead J/L might be transmi8ed. In these cases please either decrease -our baud rate or insert small delay be;ore to -our 7E:=ET command e.g.: dela-3BB. T-picall- !alues between 3 and 1// should do the tric(. In case -ou ma(e excessi!e use o; 7E:=ET or 7E:>ET please increase the dela- !alue e!en ;urther in case -ou con$nue recei!ing wrong !alues. b. 7E:>ET 6ith this command !alues ;rom the Excel sheet either Ac$!e=heet or named sheetB can be Pueried and read b- Arduino. or example -ou could put a !alue ;or the deladura$on in cell +: Puer- it e!er- loop itera$on and control how long -our Arduino should pause between each loop. The !alue in + can be changed b- -ou during run$me at an- $me. Ple'se note -ou need to read the !alue on -our Arduino. This can be done b- Integer
or =tring. 9ou should ta(e care to onl- send integers when -ou are reading integers and !ice !ersa. Use
Serial.readString1ntil(!);
or
Serial.readString1ntil(!).toInt();
or
Serial.readString1ntil(!).to6loat();
The number ! is important as A=7II char 1/ represents the end o; a line to read. 6ith the ;ollowing command -ou could read the !alue in + ;or the example abo!eC void loop() { int myDelay4al-e;
Serial.println("'E,7ET,8$"); myDelay4al-e 9 Serial.readString1ntil(!).toInt(); Serial.println( (String) "ead val-e is: " + myDelay4al-e); delay(myDelay4al-e); To read a !alue ;rom an- other named sheet please use the ;ollowing s-ntaxC Serial.println("'E,7ET,62M*EET,AnyS/eet,',$); PL!A"! N#$! %% there might occur problems at higher baud rates with the
7E:>ET command. It might happen that !alues are not trans;erred correctl- S instead J/L might be transmi8ed. In these cases please either decrease -our baud rate or insert small delay be;ore to -our 7E:>ET command e.g.: dela-3BB. T-picall- !alues between 3 and 1// should do the tric(. In case -ou ma(e excessi!e use o; 7E:=ET or 7E:>ET please increase the dela- !alue e!en ;urther in case -ou con$nue recei!ing wrong !alues. c.
NK6 9ou can set the row on which data should be logged to manuall-. Thus a5er e.g.: ha!ing logged 2/ data sets -ou can reset the row counter to J2L and start ;rom the beginning again. Kr -ou can read the row that is c urrentl- logged to please note -ou need Serial.readString1ntil(!).toInt() command a5erwards in ArduinoB =-ntaxC
#eginners >uide to 0 ,A !2 b- ?et@,e!il re!1B
0age o; 11
Serial.println("2,SET,); =-ntaxC
Serial.println("2,7ET); myo< 9 Serial.readString1ntil(!).toInt(); d. 7U=TK*#K1 D 7U=TK*#K2 D 7U=TK*#K3 9ou can set a label to the chec(boxes on the 0 ,A UI: set the !alues to the chec(boxes $c(ed or not $c(edB and read the !alue o; the chec(boxes into boolB. =-ntax ;or exampleC Serial.println("'1ST2M2=,AE,Meas-re /-midity as ); Serial.println(&'1ST2M2=,7ET); myool4al-e 9 Serial.readString1ntil(!).toInt(); e. 7EANNA?>E Kther than 7leardata or 7learsheet this command will onl- clear a certain range on the Ac$!e=heet. ,ata will be deleted in a rectangle ;rom top le5 to bo8om right. =-ntaxC Serial.println(&'EAA57E,,!,D,!); ;.
NE=ETTI*EN It will reset the $mer that is used to count the $me 0 ,A is alread- logging =-ntaxC Serial.println(&ESETTIME);
'.
The Arduino part - Excel worboo commands a. 0AU=EK>>I?> D NE=U*EK>>I?> D =TK0K>>I?> #asicall- these commands explain themsel!es. Use to pause 0 ,A to post logged data to the sheet. K; course 0 ,A will s$ll listen to incoming commands to recogniQe NE=U*EK>>I?> a5er 0AU=EK>>I?>B but it wonOt be printed to Ac$!e=heet. In case -ou want to see what in;orma$on is recei!ed use the ,irect ,ebug 6indow. =TK0K>>I?> will completel- stop the logging process. ?o more data can be recei!ed a5erwards 9ou ha!e to restart the logging process manuall- bclic(ing the connect bu8on =-ntaxC Serial.println(&?A1SE277I57); b. =AE6KNV#KKV 6ill
=AE6KNV#KKVA= 6ill sa!e the wor(boo( b- an- gi!en name. The new wor(boo( will be sa!ed in the same ;older as the currentl- open wor(boo( except i; -ou include sub;olders in the name to be sa!edB. =-ntaxC Serial.println(&SA4E2@22@AS,My5e<or#oo5ame); =-ntaxC Serial.println(&SA4E2@22@AS,S-#BolderCor#ooname);
d. KN7EE7EUIT This is a hea!- command It will ;orce Puit Excel. #eginners >uide to 0 ,A !2 b- ?et@,e!il re!1B
0age * o; 11
0EA=E ?KTE TK =AE 9KUN 6KNV#KKV IN=T =-ntaxC Serial.println(&62'EE='E1IT);
).
The Arduino part - !iscellaneous commands a.
#EE0 6ill simpl- ma(e a beep noise in Excel. >ood i; -ou want to be no$&ed b- -our Arduino e.g.: a5er a certain threshold is past on a !alue -ou measure room temperature or otherB =-ntaxC Serial.println(&EE?);
b. *=> 7an be used to put a string on the 7ontroller *essage label on the 0 ,A UI =-ntaxC Serial.println(&MS7,?-t yo-r tet /ere); c.
,K?E 6ill Wush the =erial port on Excel side. 7an be used to send data which might be in the buer on Excel =-ntaxC Serial.println(&D25E);
d. >ETNA?,K* 6ill return a random number ;rom Excel to Arduino. This is use;ul because Arduino canOt create true random numbers. ArduinoOs random ;unc$on rePuires prior ini$aliQa$on b- random=eed!alueB. I; !alue is not uniPue e.g .: user inputB the number sePuence return b- ArduinoOs random will alwa-s be the same. To get a uniPue !alue ;or random=eedOs !alue -ou can there;ore Puer- Excel. =-ntaxC
Serial.println("7ETA5D2M,FGGG,GGG"); int rndseed 9 Serial.readString1ntil(!).toInt(); randomSeed(rndseed); Serial.println( (String) &st n-m#er: & + random(!, H!)); Serial.println( (String) &nd n-m#er: & + random(!, H!)); Serial.println( (String) &Grd n-m#er: & + random(!, H!));
+.
"oing #our own stuff Each and e!er- one S including -ou B S is allowed and should and can see the ;ull source code o; 0 ,A !2: read it: learn ;rom it and adenerall-: all -ou need to (now is that in the sub J,ataNead-L -ou can add new commands. =impl- cop- Y paste an old one: gi!e it -our name and go ;or it 9ou could also use the 7ustom,e!0oints that I included in the code. These ;unc$ons are called at dierent $mes during the run. E.g.: e!er- $me a new line is read ;rom 0 ,A thus a5er each Serial.println();B: a5er a new line with ,ATA is read thus a5er each
Serial.println(&DATA,);B or a5er this data is added to the sheet.
#eginners >uide to 0 ,A !2 b- ?et@,e!il re!1B
0age + o; 11
1/.
Upgrading $%& "A' v( Excel versions I; -ou want to use the new !ersion o; 0 ,A !2 but want to (eep all -our con&gured sheets in old Excel !ersion o; 0 ,A tr- the ;ollowing steps to cop- all sheets: re;erences and ;ormulas easil- to the new !ersion. PL!A"! N#$! %% it is ?KT possible to cop- the sheet J=imple ,ataL ;rom the old !ersion to
the new !ersion. Excel would automa$call- cop- the old !ersion o; 0 ,A as well Z. a. ,ownload new 0 ,A Excel and open: open -our Excel as well. b. In -our old Excel select &rst sheet: then hold shi5 (e- and select last sheet: right clic( c.
&rst sheet and select [*o!e or 7op-[ =elect new 0 ,A Excel in the dropdown: select [mo!e to end[: select [7reate a
cop-[ and press KV. d. It should cop- all selected sheets to the new !ersion. 11.
)ull blown demo ast but not least here is a ;ull- wor(ing demo s(etch that is using most o; the abo!e men$oned commands and should gi!e -ou a ;air o!er!iew on how to program -our Arduino to communicate with Excel using 0 ,A !2. Enree$ngs onathan Arndt a(a ?et@,e!ilB
JK K Demo setL/ Bor ne< "?= DA v" K inLl-ding most all Lommon Lommands to #e -sed KJ int i 9 !; void set-p() { JJ open serial LonneLtion Serial.#egin($%!!);
JJSerial.println("'EADATA"); JJ Llears starting at ro< Serial.println("'EAS*EET"); JJ Llears starting at ro<
JJ deBine H Lol-mns named "Date", "Time", "Timer", "'o-nter" and "millis" Serial.println("AE,Date,Time,Timer,'o-nter,millis");
#eginners >uide to 0 ,A !2 b- ?et@,e!il re!1B
0age , o; 11
JJ set t/e names Bor t/e G L/eL#oes Serial.println("'1ST2M2=,AE,Stop logging at H!>"); Serial.println("'1ST2M2=,AE,es-me log at GH!>"); Serial.println("'1ST2M2=G,AE,-it at H!>"); JJ L/eL oB t/e G L/eL#oes (Birst t
JJ Llear some Lells in ELel (reLtangle range Brom ! to D!) iB(i99!!) Serial.println("'learange,,!,D,!");
JJ do a simple #eep in ELel on ?' iB(i99H!) Serial.println("EE?");
JJ read a val-e (in t/is Lase integer) Brom a s/eet #y name iB(i99!!) JJSerial.println("'E,7ET,E"); 99O aLtive s/eet in ELel Serial.println("'E,7ET,62MS*EET,Simple Data,E,"); JJnamed s/eet int readval-e 9 Serial.readString1ntil(!).toInt(); JJ res-lt displayed in ELel DireLtDe#-gindo< to do-#le L/eL Serial.println( (String) "4al-e oB Lell E is: & + readval-e);
JJ L/eL val-e oB L-stom#o on ?= DA in ELel and iB JJ L/eL#o is L/eLed t/en send t/e Lommand to pa-se logging iB(i99H!) { Serial.println("'1ST2M2=,7ET"); int stoplogging 9 Serial.readString1ntil(!).toInt(); JJ t/is inBormation Lan #e seen in t/e JJ direLt de#-g
#eginners >uide to 0 ,A !2 b- ?et@,e!il re!1B
0age 1- o; 11
Serial.println("?A1SE277I57");
JJ get a tr-e random n-m#er Brom t/e Lomp-ter iB(i99G!!) { Serial.println("7ETA5D2M,FG,GH"); JJ #et
JJ and no< res-me logging iB(i99GH!) { Serial.println("'1ST2M2=,7ET"); int res-melogging 9 Serial.readString1ntil(!).toInt(); iB(res-melogging) { Serial.println("ES1ME277I57"); JJ post to speLiBiL Lells on deBa-l s/eet and named s/eet iB(i99!!) { JJ deBa-lt s/eet aLtive in ?= DA ELel Serial.println("'E,SET,7!,!! test string"); JJ named s/eet availa#le in ?= DA ELel Serial.println("'E,SET,25S*EET,Simple Data,7,,!! test ); JJ and Bor BorLed Q-it oB ELel
