Visual Basic and Excel

H

Hotbird

I am using a Visual Basic application (which receives and presents serial
(text) data on a full-frame form in real time) and would like to add a line
of code in the MSComm1 module so that the same serial data can be sent to a
cell in an excel spreradsheet for simultaneous analysis. Is this possible?
And what command structure is needed to write from one application (VB in a
minimised window) to Excel (maximised)? Am able to post a portion of the
MSComm1 code handling received data if the idea is sound.
 
K

K Dales

Your idea should work. Here is the basic idea (no pun
intended!): Add a reference to the Microsoft Excel object
model to your VB project. You can then create an object
variable of the type Excel.Application to hold a Microsoft
Excel session (which you could populate either by finding
an existing open Excel session, if that is what you want
to do, or you could start a new session through code).
You can then use the object model to create the
workbook/spreadsheet structure you need to do your
analysis. Have your code put the value you have read into
a cell in the workbook.

Something like this:

Dim XLApp as New Excel.Application, XLBook as
Excel.Workbook

' To open an existing workbook from a file:
Set XLBook = XLApp.Workbooks.Open FileName
' Or, to create a new workbook:
Set XLBook = XLApp.Workbooks.Add

' Make the session visible so the user can view and
interact with it:
XLApp.Visible = True

' Code here would set up ranges, formulas, formats, etc...
needed on the worksheet for processing your input
' Assuming you then want to take your serial data you have
read (I will give it the variable name SerialData) and put
it in cell B2 on Sheet1, you could write:

XLBook.Sheets("Sheet1").Range("B2").Value = SerialData

'etc...

' To clean everythig up at the end:
XLBook.Close
XLApp.Quit
Set XLBook = Nothing
Set XLApp = Nothing

Just an outline, but should give an idea of how this
works - check the MSDN library for Excel programming info
to get more details of how to use the Excel object model
in VB.
K Dales
 
H

Hotbird

Thanks for this encouragement. Although I am comfortable programming Excel
VBA, I am unfamiliar with Visual Basic and am hoping to achieve my objective
with minimal alterations to an existing Visual Basic project. I am pasting
below, part of the MSComm1_OnComm() Procedure (which handles a variety of
serial commands), and I would initially hope to add a line of the form
XLBook.Sheets("Sheet1").Range("B2").Value = dummy, perhaps before the
initial Select Case dummy statement (line 3 of Private Sub
MSComm1_OnComm()).

I want to open an existing spreadsheet "C:\Book1.xls" to receive and
analyse the data, but remain unsure where the statements you suggest should
be placed:.

Dim XLApp as New Excel.Application, XLBook as Excel.Workbook
Set XLBook = XLApp.Workbooks.Open "C:\Book1.xls"
XLApp.Visible = True


K Dales said:
Your idea should work. Here is the basic idea (no pun
intended!): Add a reference to the Microsoft Excel object
model to your VB project. You can then create an object
variable of the type Excel.Application to hold a Microsoft
Excel session (which you could populate either by finding
an existing open Excel session, if that is what you want
to do, or you could start a new session through code).
You can then use the object model to create the
workbook/spreadsheet structure you need to do your
analysis. Have your code put the value you have read into
a cell in the workbook.

Something like this:

Dim XLApp as New Excel.Application, XLBook as
Excel.Workbook

' To open an existing workbook from a file:
Set XLBook = XLApp.Workbooks.Open FileName
' Or, to create a new workbook:
Set XLBook = XLApp.Workbooks.Add

' Make the session visible so the user can view and
interact with it:
XLApp.Visible = True

' Code here would set up ranges, formulas, formats, etc...
needed on the worksheet for processing your input
' Assuming you then want to take your serial data you have
read (I will give it the variable name SerialData) and put
it in cell B2 on Sheet1, you could write:

XLBook.Sheets("Sheet1").Range("B2").Value = SerialData

'etc...

' To clean everythig up at the end:
XLBook.Close
XLApp.Quit
Set XLBook = Nothing
Set XLApp = Nothing

Just an outline, but should give an idea of how this
works - check the MSDN library for Excel programming info
to get more details of how to use the Excel object model
in VB.
K Dales
Private Sub MSComm1_OnComm()
Do
dummy = MSComm1.Input
Select Case dummy
Case ""
' do nothing for null characters
nullchar = True
Case Chr$(STX)
BufPoint = 1
InBuffer$(BufPoint) = dummy
Case Chr$(ETX)
BufPoint = BufPoint + 1
InBuffer$(BufPoint) = dummy
Select Case InBuffer$(2)
Case "O"
' Question Number
getch$ = ""
getpoint = 3
Do
j = InBuffer$(getpoint)
If j <> ":" Then
getch$ = getch$ + j
End If
getpoint = getpoint + 1
Loop Until j = ":"
QstNumb = getch$

getch$ = ""
Do
j = InBuffer$(getpoint)
If j <> ":" Then
getch$ = getch$ + j
End If
getpoint = getpoint + 1
Loop Until j = ":"
If FinalRound = True Then
If Len(Overall$) < 5 Then
QuestNumb.Text = "Question : " + QstNumb +
Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, Len(Overall$) - 1) +
")"
Else
QuestNumb.Text = "Question : " + QstNumb +
Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 1) + "," +
Right$(Overall$, 3) + ")"
End If
Else
QuestNumb.Text = "Question : " + QstNumb
End If
QuestBox.Text = getch$

getch$ = ""
Do
j = InBuffer$(getpoint)
If j <> Chr$(ETX) Then
getch$ = getch$ + j
End If
getpoint = getpoint + 1
Loop Until j = Chr$(ETX)
AnswerBox.Text = getch$
BufPoint = 0
Case "B"
' get bank
getch$ = ""
For I = 3 To BufPoint - 1
getch$ = getch$ + InBuffer$(I)
Next
bankedinf.Text = Str(Val(getch$))
ChainTxt(0).Caption = Str(Val(getch$))
BufPoint = 0
Case "H"
' set clock
ClockInf.Text = InBuffer$(3) + InBuffer$(4) +
InBuffer$(5) + InBuffer$(6)
Case "I"
' set round
If InBuffer$(3) = 0 Then
FinalRound = False
QuestBox.Left = 1560
QuestBox.Width = 10455
QuestNumb.Left = 1560
QuestNumb.Width = 10455
AnswerBox.Left = 1560
AnswerBox.Width = 10455
Else
FinalRound = True
QuestBox.Left = 0
QuestBox.Width = 12015
QuestNumb.Left = 0
QuestNumb.Width = 12015
AnswerBox.Left = 0
AnswerBox.Width = 12015
End If
Call SortForm
Case "J"
' contestant names

getch$ = ""
getpoint = 3
Do
j = InBuffer$(getpoint)
If j <> ":" Then
getch$ = getch$ + j
End If
getpoint = getpoint + 1
Loop Until j = ":"
Name1.Text = getch$

getch$ = ""
Do
j = InBuffer$(getpoint)
If j <> ":" Then
getch$ = getch$ + j
End If
getpoint = getpoint + 1
Loop Until j = ":"
Name2.Text = getch$
Case "K"
' show correct option
getpoint = 3
For qst = 0 To 5
For bod = 1 To 2
FinalRes(qst, bod) =
Val(InBuffer$(getpoint))
getpoint = getpoint + 1
Next
Next
Call SortQst
Case "L"
FinalQst = Val(InBuffer$(3) - 1)
Call SortQst
Case "N"
Overall$ = Str$(Val(InBuffer$(3) + InBuffer$(4) +
InBuffer$(5) + InBuffer$(6) + InBuffer$(7)))
If FinalRound = True Then
If Len(Overall$) < 5 Then
QuestNumb.Text = "Question : " + QstNumb +
Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, Len(Overall$) - 1) +
")"
Else
If Len(Overall$) <= 6 Then
QuestNumb.Text = "Question : " + QstNumb
+ Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 1) + "," +
Right$(Overall$, 3) + ")"
Else
QuestNumb.Text = "Question : " + QstNumb
+ Chr$(Val(FinalTeam) + 64) + "(£" + Mid$(Overall$, 2, 2) + "," +
Right$(Overall$, 3) + ")"
End If
End If
End If
Case "Z"
' set question font
getpoint = 3
ftsize$ = ""
Do
If InBuffer$(getpoint) <> ":" Then
ftsize$ = ftsize$ + InBuffer$(getpoint)
End If
getpoint = getpoint + 1
Loop Until InBuffer$(getpoint) = ":"
QuestBox.FontSize = Format(ftsize)

ftsize$ = ""
Do
If InBuffer$(getpoint) <> ":" Then
ftsize$ = ftsize$ + InBuffer$(getpoint)
End If
getpoint = getpoint + 1
Loop Until InBuffer$(getpoint) = ":"
AnswerBox.FontSize = Format(ftsize$)
BufPoint = 0
Case Else
BufPoint = 0
End Select
BufPoint = 0
Case Else
BufPoint = BufPoint + 1
InBuffer$(BufPoint) = dummy
End Select
Loop Until dummy = ""
End Sub
 
K

K Dales

Rather than give you a straightforward answer ("MY"
answer), I will try to help you decide how to handle it
yourself (but will also give my recommendations).
Basically, here's what happens:

At the point you create an Excel.Application object, it is
the same thing as starting up a new Excel session, i.e. it
is as if you had gone to your Start menu and chosen to
start Excel. The only difference is it happens behind the
scenes and, also, is invisible for now. You would see an
EXCEL process in the Windows Task Manager but will not see
anything on screen until you set the Visible property to
True.

You can start Excel when you first start running your
module by using the line:
Dim XLApp as New Excel.Application
This not only defines XLApp as an Excel.Application object
but creates a new instance of it; i.e. starts it.

Alternatively, you can hold off on starting Excel until
you actually need to use it by defining the object
variable and then, later in code, actually activating it:
Dim XLApp as Excel.Application
....
....
Set XLApp = New Excel.Application

Why would it make a difference? Well, first, at the point
where you create you actually call on Excel to start it
will take some time: it has to read your hard drive and
load and initialize Excel. Also, if you do this inside a
loop, with all that Excel starting up and shutting down,
you can end up slowing down your computer - and if you
happen to forget to close the session you run the risk of
having multiple invisible Excel sessions running in the
background!!! But then, Excel will use some overhead -
memory and processor cycles - so you may want to hold off
on opening it until you really have to.

In short, it is up to you where to actually put the
New.Excel.Application declaration that will start Excel -
just activate it sometime before you need to use it and
make sure you are not inadvertently doing it inside a loop!

Once you have that Excel application you can either create
a new workbook (by the Set XLBook = XLApp.Workbooks.Add
statement) or you can open an existing file (with Set
XLBook = XLApp.Workbooks.Open "C:\Book1.xls". This is no
different than what you would do if you were manually
controlling Excel, you are just doing it through code.
Since it sounds like you will be using the same workbook
structure over and over, the Open option seems best to me
(otherwise you need to create your workbook with all its
cell values and formats and formulas through code every
time you run your module). Also, rather than repeatedly
opening and closing Excel for every new 'dummy' value, it
probably makes more sense to create your Excel session att
he beginning of the module, then just set it visible or
invisible as needed while the rest of your code runs.

Once you have your Excel session and workbook open you can
do pretty much anything you can do with Excel in VBA, with
all the same Objects and Methods, by referring to XLApp or
XLBook. So this is where you can set the value of the
cell in the workbook to be equal to your 'dummy' and do
any further processing you want to do. At this stage you
probably also want to make the session visible to the
user, so set XLApp.Visible to True and there will be the
Excel session you created.

Finally, when everything is done you need to clean things
up. This means:
1) Close your workbook and save it if necessary
2) Exit the Excel session and clear Excel out of your
computer's memory
3) Any object variables you declared (e.g. XLApp, XLBook)
have memory and resources assigned to them; clean this up
by setting them equal to "Nothing."
If you don't do this housekeeping it may not be
noticeable, but it is at the least poor practice and
wasteful of computer resources - at worst you can crash
your system.

So, finally, my suggestions:
- use the Dim XLApp as New Excel.Application at the
beginning of your code
- also at the beginning of your code, after all variables
are defined, open the PRE-BUILT workbook "C:\Book1.xls"
(you will need to set this up and have it saved before
running your code). This would be the line Set XLBook =
XLApp.WOrkbooks.Open "C:\Book1.xls"
- Set XLApp visible at the point you want the user to
become aware of it
- Now all you need to do is to grab your 'dummy' variable
at the appropriate point (as you suggest, just before the
Select Case might be good) and put it in the desired cell.
- As soon as possible, i.e. as soon as your need for Excel
is gone, close everything and set the variables to Nothing
as described above.

I hope that does it...
K Dales
 
C

Chip Pearson

Dim XLApp as New Excel.Application
This not only defines XLApp as an Excel.Application object
but creates a new instance of it; i.e. starts it.

Technically, this is not true. Excel isn't started when the Dim
statement is processed. The Excel application isn't actually
started until the first reference in code (not a Dim statement)
to the object is encountered. Using the New keyword creates what
is called an "auto-instancing" variable. When the VBA code is
compiled, VBA actually creates code that, if it were written in
VBA, would look like

If XLApp Is Nothing Then
Set XLApp = New Excel.Application
End If

It creates this code just before *every* reference to XLApp. The
generally prefered method is to avoid auto-instancing variables
and use Set/New to create the instance of the object a the
appropriate point in your code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




Rather than give you a straightforward answer ("MY"
answer), I will try to help you decide how to handle it
yourself (but will also give my recommendations).
Basically, here's what happens:

At the point you create an Excel.Application object, it is
the same thing as starting up a new Excel session, i.e. it
is as if you had gone to your Start menu and chosen to
start Excel. The only difference is it happens behind the
scenes and, also, is invisible for now. You would see an
EXCEL process in the Windows Task Manager but will not see
anything on screen until you set the Visible property to
True.

You can start Excel when you first start running your
module by using the line:
Dim XLApp as New Excel.Application
This not only defines XLApp as an Excel.Application object
but creates a new instance of it; i.e. starts it.

Alternatively, you can hold off on starting Excel until
you actually need to use it by defining the object
variable and then, later in code, actually activating it:
Dim XLApp as Excel.Application
....
....
Set XLApp = New Excel.Application

Why would it make a difference? Well, first, at the point
where you create you actually call on Excel to start it
will take some time: it has to read your hard drive and
load and initialize Excel. Also, if you do this inside a
loop, with all that Excel starting up and shutting down,
you can end up slowing down your computer - and if you
happen to forget to close the session you run the risk of
having multiple invisible Excel sessions running in the
background!!! But then, Excel will use some overhead -
memory and processor cycles - so you may want to hold off
on opening it until you really have to.

In short, it is up to you where to actually put the
New.Excel.Application declaration that will start Excel -
just activate it sometime before you need to use it and
make sure you are not inadvertently doing it inside a loop!

Once you have that Excel application you can either create
a new workbook (by the Set XLBook = XLApp.Workbooks.Add
statement) or you can open an existing file (with Set
XLBook = XLApp.Workbooks.Open "C:\Book1.xls". This is no
different than what you would do if you were manually
controlling Excel, you are just doing it through code.
Since it sounds like you will be using the same workbook
structure over and over, the Open option seems best to me
(otherwise you need to create your workbook with all its
cell values and formats and formulas through code every
time you run your module). Also, rather than repeatedly
opening and closing Excel for every new 'dummy' value, it
probably makes more sense to create your Excel session att
he beginning of the module, then just set it visible or
invisible as needed while the rest of your code runs.

Once you have your Excel session and workbook open you can
do pretty much anything you can do with Excel in VBA, with
all the same Objects and Methods, by referring to XLApp or
XLBook. So this is where you can set the value of the
cell in the workbook to be equal to your 'dummy' and do
any further processing you want to do. At this stage you
probably also want to make the session visible to the
user, so set XLApp.Visible to True and there will be the
Excel session you created.

Finally, when everything is done you need to clean things
up. This means:
1) Close your workbook and save it if necessary
2) Exit the Excel session and clear Excel out of your
computer's memory
3) Any object variables you declared (e.g. XLApp, XLBook)
have memory and resources assigned to them; clean this up
by setting them equal to "Nothing."
If you don't do this housekeeping it may not be
noticeable, but it is at the least poor practice and
wasteful of computer resources - at worst you can crash
your system.

So, finally, my suggestions:
- use the Dim XLApp as New Excel.Application at the
beginning of your code
- also at the beginning of your code, after all variables
are defined, open the PRE-BUILT workbook "C:\Book1.xls"
(you will need to set this up and have it saved before
running your code). This would be the line Set XLBook =
XLApp.WOrkbooks.Open "C:\Book1.xls"
- Set XLApp visible at the point you want the user to
become aware of it
- Now all you need to do is to grab your 'dummy' variable
at the appropriate point (as you suggest, just before the
Select Case might be good) and put it in the desired cell.
- As soon as possible, i.e. as soon as your need for Excel
is gone, close everything and set the variables to Nothing
as described above.

I hope that does it...
K Dales
 
K

K Dales

Thanks Chip - new info for me.
-----Original Message-----


Technically, this is not true. Excel isn't started when the Dim
statement is processed. The Excel application isn't actually
started until the first reference in code (not a Dim statement)
to the object is encountered. Using the New keyword creates what
is called an "auto-instancing" variable. When the VBA code is
compiled, VBA actually creates code that, if it were written in
VBA, would look like

If XLApp Is Nothing Then
Set XLApp = New Excel.Application
End If

It creates this code just before *every* reference to XLApp. The
generally prefered method is to avoid auto-instancing variables
and use Set/New to create the instance of the object a the
appropriate point in your code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




Rather than give you a straightforward answer ("MY"
answer), I will try to help you decide how to handle it
yourself (but will also give my recommendations).
Basically, here's what happens:

At the point you create an Excel.Application object, it is
the same thing as starting up a new Excel session, i.e. it
is as if you had gone to your Start menu and chosen to
start Excel. The only difference is it happens behind the
scenes and, also, is invisible for now. You would see an
EXCEL process in the Windows Task Manager but will not see
anything on screen until you set the Visible property to
True.

You can start Excel when you first start running your
module by using the line:
Dim XLApp as New Excel.Application
This not only defines XLApp as an Excel.Application object
but creates a new instance of it; i.e. starts it.

Alternatively, you can hold off on starting Excel until
you actually need to use it by defining the object
variable and then, later in code, actually activating it:
Dim XLApp as Excel.Application
....
....
Set XLApp = New Excel.Application

Why would it make a difference? Well, first, at the point
where you create you actually call on Excel to start it
will take some time: it has to read your hard drive and
load and initialize Excel. Also, if you do this inside a
loop, with all that Excel starting up and shutting down,
you can end up slowing down your computer - and if you
happen to forget to close the session you run the risk of
having multiple invisible Excel sessions running in the
background!!! But then, Excel will use some overhead -
memory and processor cycles - so you may want to hold off
on opening it until you really have to.

In short, it is up to you where to actually put the
New.Excel.Application declaration that will start Excel -
just activate it sometime before you need to use it and
make sure you are not inadvertently doing it inside a loop!

Once you have that Excel application you can either create
a new workbook (by the Set XLBook = XLApp.Workbooks.Add
statement) or you can open an existing file (with Set
XLBook = XLApp.Workbooks.Open "C:\Book1.xls". This is no
different than what you would do if you were manually
controlling Excel, you are just doing it through code.
Since it sounds like you will be using the same workbook
structure over and over, the Open option seems best to me
(otherwise you need to create your workbook with all its
cell values and formats and formulas through code every
time you run your module). Also, rather than repeatedly
opening and closing Excel for every new 'dummy' value, it
probably makes more sense to create your Excel session att
he beginning of the module, then just set it visible or
invisible as needed while the rest of your code runs.

Once you have your Excel session and workbook open you can
do pretty much anything you can do with Excel in VBA, with
all the same Objects and Methods, by referring to XLApp or
XLBook. So this is where you can set the value of the
cell in the workbook to be equal to your 'dummy' and do
any further processing you want to do. At this stage you
probably also want to make the session visible to the
user, so set XLApp.Visible to True and there will be the
Excel session you created.

Finally, when everything is done you need to clean things
up. This means:
1) Close your workbook and save it if necessary
2) Exit the Excel session and clear Excel out of your
computer's memory
3) Any object variables you declared (e.g. XLApp, XLBook)
have memory and resources assigned to them; clean this up
by setting them equal to "Nothing."
If you don't do this housekeeping it may not be
noticeable, but it is at the least poor practice and
wasteful of computer resources - at worst you can crash
your system.

So, finally, my suggestions:
- use the Dim XLApp as New Excel.Application at the
beginning of your code
- also at the beginning of your code, after all variables
are defined, open the PRE-BUILT workbook "C:\Book1.xls"
(you will need to set this up and have it saved before
running your code). This would be the line Set XLBook =
XLApp.WOrkbooks.Open "C:\Book1.xls"
- Set XLApp visible at the point you want the user to
become aware of it
- Now all you need to do is to grab your 'dummy' variable
at the appropriate point (as you suggest, just before the
Select Case might be good) and put it in the desired cell.
- As soon as possible, i.e. as soon as your need for Excel
is gone, close everything and set the variables to Nothing
as described above.

I hope that does it...
K Dales



.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top