Advertisement  

Monday, 26 June 2017
     
 
Main Menu
Home Home
Shop Shop
News News
BASCOM-AVR BASCOM-AVR
BASCOM-8051 BASCOM-8051
Products Products
Application Notes Application Notes
Publications Publications
Links Links
Support Center Support Center
Downloads Downloads
Forum Forum
Resellers Resellers
Contact Us Contact Us
Updates Updates
MCS Wiki MCS Wiki
Online Help
BASCOM-AVR Help BASCOM-AVR Help
BASCOM-8051 Help BASCOM-8051 Help
Contents in Cart
Show Cart
Your Cart is currently empty.
Search the Shop

Products Search

User Login
Username

Password

If you have problem after log in with disappeared login data, please press F5 in your browser

RSS News
 
     
 

 
   
     
 
AN #145 - Transfer data between MS Excel & MCU Print
Transfer data between MS Excel & MCU by Jean-Pierre Duval  jeanpierre.duval2@wanadoo.fr

FOREWORD

In this article a certain number of trade names are given (Excel®, VBA® etc) to avoid putting ® everywhere I make a point of saying how much I respect their authors and how much I thank them.

What we want to do

To make dialogue a micro controller with a PC, one can build an owner application in Visual-BASIC, Delphi, C… it is long and one is never safe from a bug.

And then there is VBA Visual-BASIC-for-Application the programming language of Excel.

It is then enough to write macro of some lines to be simplified the life considerably.

The exposed example shows:

- To transfer the contents from a cell towards the microcontroller. In this example, the transmitted cell is A3 (column A line 3) thus it is necessary that there is something in A3.  

- Posting of this cell on the display associated with the microcontroller.

- To transfer a message from the microcontroller towards Excel. 

- Posting of this message in a cell of B3 reception.

We will consider that the cell sent can contain characters as many as the buffer can receive, here is 32. (What we defined in our program microcontroller)

We turn over a word of 20 characters, if it makes less than 20, it is supplemented by spaces chr (32) . It is obvious that by incrementing the variables Colonne and Line, the transfer can relate to a complete array;  but that exceeds the goal of our matter.
 

For you familiarise with VBA it exists a very good course, clearly, pragmatic which will make it possible the electronics specialist to very quickly control this language. Run which is quite simply formidable of simplicity and facility. I make a point of thanking Mr. Cherbé who authorised me to quote it in this article. http:/cherbe.free.fr/

To communicate by the port series, we use a add on: Mscomm32.ocx which is not installed with the various versions of Excel and that Mr Bill it is not nice!

One can get it either while going on the site of Mr. Cherbé (see program XL_RS232) or while following www.martin2k.co.uk/vb6/vb6download4.php  Or the mscomm.ocx from Mcselec but I did’nt test.  This OCX must be placed in the c:windowssystem32 repertory. Program XL_RS232 copies it automatically but does not install it, to see further.

Construction of the macro .

In Excel you will have to activate the tool bar Visual-BASIC

It is composed of 3 icons: an inkstand, one tools box, a gauge and a square.

- To click on tools box
- to take a command button, it takes shape with the left button of the mouse and fleet on the grid Excel, it is called
commandButton1 if you click above a screen VB appears.

 

On the left in top, the window of project, in lower part the window of property, on the right the window of code which posts the first sub.

Attention, Attention, Attention, Attention, Attention

VB and VBA authorise the implicit declaration of variable what is aberrant, dangerous, stupid!

Also as of the opening of the program it is necessary to write:
 

Option Explicit `to write this order before the first sub, in all the sheets, it wants to say that the variables must be declared for this module, sheets.

--------------------------------

Private Sub CommandButton1_Click()

 

End Sub

 
T
hen it is necessary to ask VBA to add a userform in the page of writing of code to click on the insertion index then userform.

To return on the Excel grid, click again the button to add:

 

Option Explicit

Private Sub CommandButton1_Click()

UserForm1.Show `to show the userform

End Sub

 
In the pr
operties of the button, modify Caption “to launch the transfer”

To click on userform1 in the window of project.

To open tools box @ if the É is not present to install Mscomm32.ocx (see higher)

to click on the index tools click on additional controls and to seek “Microsoft communication control, version…” To notch the box, the telephon must be posted.


To slip the telephone into the userform, to add 3 buttons and two textbox and two label

The userForm contains

3 buttons:  To send;  to open Comm1;  To close Comm1 icon:

2 labels à icon (ab|)

2 windows: Envoy; Receipt à icon (A)

1 Telephone

In the properties of Userform one will be able to change the colour, the size, the title…

 

The property name of the Open buttons Comm1 and Close Comm1 will be changed respectively into Opencomm1 and Closecomm1 their title (Caption) in the same way.

 

The caption of commandButton1 will be changed of “To send File” one will not change the name

 

 

The object  communication has a certain number of properties; it should be checked in the window of properties that those are well configured as follows:

CommPort the number of the serial port of the PC (ie=1)

DTREnable Data Transmission Ready Enable must be “true” to authorise the communication.

EOFEnable End Of File Enable we thus do not use for our applications “false”

Handshaking Defines the communications protocol used, 0=pas of protocol

InBufferSize Size of entry 1024 per default

InputLen Defines and return the number of natures read in the buffer of reception by the property Input. Si=0 reads the totality of the buffer 

Inputmode Defines or return the type of data received by the Input property. In text mode

NullDiscard Determine if the idle characters are transferred from the port towards the buffer of reception.

OutBufferSize defines the size of the buffer of transmission in bytes.

ParityReplace defines the character returns which replaces a nonvalid character in the flow of data in the event of error of parity.

Rthreshold not used must be left to 0

RTSEnable Ask to emit , false  here


Settings Adjustment of the port series per default 9600 baud, No Parity, 1 bit of stop, word of 8 bits

Sthreshold not used must be left to 0
 

We quote these properties because the assistance of VBA does not indicate anything for the option telecommunication.

Result:



Material and microcontroller used

The microcontroller used is ATmega32 assembled on module XAMEGA32, the power supply is provided by a module PS200, these modules are available at www.sidena.com.





Bond:

In the event of problem with BASIC – BASCOM visit my site:

http://perso.wanadoo.fr/bils-instruments/

 

The codes are given in the examples of this site.


 
VBA  Code

While clicking on each button one will give the following code:

 

Option Explicit

------------------------------------------------------

Bouton closecomm1

Private Sub closecomm1_Click()

'close the port if it is open

If MSComm1.PortOpen = True Then MSComm1.PortOpen = False

 'close le userform at the end of the reception

 UserForm1.Hide

End Sub

------------------------------------------------------

Bouton closecomm2

Private Sub Opencomm1_Click()

'To empty the buffer ---------------------------

MSComm1.InBufferCount = 0

'To adjust the connexion -------------------

MSComm1.Settings = "9600,n,8,1"

' 20 characters are awaited ------------------

MSComm1.InputLen = 20

' Opening of the port ------------------

If MSComm1.PortOpen = False Then MSComm1.PortOpen = True

End Sub

Bouton commandbutton1

Private Sub CommandButton1_Click()

Dim ligne As Byte, colonne As Byte

Dim received As String * 20

Dim send As String * 20

If MSComm1.PortOpen = True Then

  ' Send data ------------------

 ligne = 3

 colonne = 1

 send = Worksheets("Données").Cells(ligne, colonne)

 MSComm1.Output = send

 TextBox1 = send

 ' end of transfer --------------------

 MSComm1.Output = Chr(3) ‘see the program microcontroller ETX=End_Of_Text)

 ' réception des données---------------- 

 CommandButton1.Caption = "reception"

 ligne = 3

 colonne = 2

 'loop of waiting of reception --------

 Do

 DoEvents

 Loop Until MSComm1.InBufferCount >= 20 ‘on attend 20 caractères

 received = MSComm1.Input

 TextBox2 = received

 'removal of the tank (32) and posting -----

 Worksheets("données").Cells(ligne, colonne) = Trim(received)

 Else

 ' message if the port is not open ---

 MsgBox ("attention, le port série n'est pas ouvert")

 End If

 ' setting with 0 of the buffer -------------------------

 MSComm1.InBufferCount = 0

 CommandButton1.Caption = "bien reçu"

 

End Sub

--------------------------------

Here it is all for Excel !

 

BASIC-BASCOM

 

' ------Jean-Pierre Duval 26-01-2006-----------------
'  test la liaison série avec Excel
' utilisation d'un module Xamega32 SIDENA
'----------------------------------------------------------
$regfile = "M32def.dat" ' the used chip
 
$crystal = 11052000
 
'----[LCD configuration---- REMarks if not used]-----
Config Lcdmode = Port ' config 4 bit
Config Lcdpin = Pin , Db4 = Portd.4 , Db5 = Portd.5 , Db6 = Portd.6 , Db7 = Portd.7 , E = Portd.3 , Rs = Portd.2
Config Lcd = 20 * 2
'i2C bus for the XAmega32 module from Sidena
Config Sda = Portb.0
Config Scl = Portb.1
Config Portb.3 = Output
Led
Alias Portb.3
'--RS232 configuration --------------------------------
$baud = 9600
Config Serialin = Buffered , Size = 32
Config Serialout = Buffered , Size = 32
Enable Interrupts
'---sub and functions----------------------------------
 
Declare Sub Rsread() 'read the data from the excel file
 
Declare Sub Rswrite()
' ----variables ----------------------------------------
Dim Jbyte As Byte
Dim Bytereceived(30) As Byte
Dim Send As String * 20
' --chek of the module ok----------------------------
For Jbyte = 1 To 3 'blinking of the led
 
Set Led
 
Waitms 200
 
Reset Led
 
Waitms 200
Next
Reset Led
'---------------------------------------------------------
Main
:
Cls
Locate 2 , 1 : Lcd " wait" 'time to run Excel Etc....
Wait 5
Locate 2 , 1 : Lcd "lecture"
Call Rsread

Locate 2 , 1 : Lcd "transfert:"
 
Call Rswrite
 
Waitms 500 ' Very important because you need to let the buffer flushes
 
'  before the End
End
'------rswrite----RS232--------------------------
Sub Rswrite()
Local Kbyte As Byte , Lbyte As Byte
Send
= "ABCDEFGHIJK"
Kbyte
= Len(send)
Kbyte
= 20 - Kbyte
For Lbyte = 1 To Kbyte 'filling the string with space until len=20
 Send
= Send + " "
Next
Print Send ' envoi par RS232
End Sub
'------rsread----RS232------------------------
Sub Rsread()
Local Rs_index As Byte
Jbyte
= 0
Rs_index
= 1 ' array starts to 1 in Basic-Bascom
Locate 1 , 1 : Lcd ""
Do
 
If Ischarwaiting() = 1 Then 'if 1 char is waiting in the buffer
 Jbyte
= Inkey() 'read the ascii value of the character
 
If Jbyte = 3 Then 'Chr(3)=End of Text
 
Exit Do
 
End If
 
Lcd Chr(jbyte);
 Bytereceived
(rs_index) = Jbyte 'filling of the array
 Rs_index
= Rs_index + 1
 
End If
 
Loop
End Sub