VB6 to Excel

[Rick]

Can someone help me write better code to use Excel
programming, from VB6? See the code below. I'm able to
open up an Excel workbook, and perform the task
repeatedly. However, if I leave that particular Excel
workbook open, I get multiple instances of Excel. In
other words, it keep opening up more read-only Excel
workbooks of the same file name and path.

I have been reading up a lot about it, and some of it is
starting to slowly make sense. Apparently we don't have
to CreateObject if it is early binding - where we pick the
Excel application from the reference library. Also with
early binding, then in Excel 2000 or later, we can write
either:

Set m_XLApp = New Excel.Application or
Set m_XLApp = GetObject(,"Excel.Application")

And they do the same thing. The advantage of GetObject is
that you have two parameters, in front of and behind the
comma.

That helps me understand CreateObject and GetObject
somewhat. Am I correct with this? We read about this
from programmers, and we need English/VBprogrammer
interpreters for those of us that only speak English.
<grin>. It's so confusing when the language or rules
change with each new version of Excel or VB.

With the code below, I hope I got everything written to
run a very simple program for testing purposes only. The
program multiples one number on one sheet times 2 and
makes it equal to another location on another sheet. Then
I have the Excel workbook file name being written on
another cell. Finally, I have a treeview control on a
form, named trvNo1. This treeview has one thing added to
it, based on what it reads in Excel. That's it - just a
couple of simple procedures to test this.

If you could copy and paste the code in VB6 (for the cmdOK
button on the VB6 form), I'd sure appreciate it. Also on
this form you'd have a treeview control (trvNo1). Then
you could use an Excel sample with made-up numbers and
text (from A drive or C drive or whatever).

If you could get this to run without multiple instances of
the same Excel workbook being pulled up, you would make
one part-time programmer extremely happy. I've been
trying so many different things with no result. Thanks.

Also anyone other comments to make this work better, it
would be greatly appreciated. I'm still learning a lot
about this, from a VB6 perspective. Thanks again.

If Bob Phillips is out there and reads this, I want to
thank you for helping me get this far. I appreciate the
tips that you've given me in the past. Bob Kilmer has
been helpful too.

Option Explicit
Dim m_XLApp As Application
Dim m_XLWorkbook As Excel.Workbook
'---------------------------------------------------
Private Sub cmdOK_Click()

Dim nodX As Node
Dim FileNamePath As String, FileName As String
Dim S1 As Worksheet, S2 As Worksheet

On Error Resume Next

FileName = "SampleProgram"
FileNamePath = "A:\SampleProgram.xls"


Set m_XLApp = GetObject(, "Excel.Application")
m_XLApp.Visible = True
Set m_XLWorkbook = m_XLApp.Workbooks(FileName)

If m_XLWorkbook Is Nothing Then
Set m_XLApp = Excel.Application
m_XLApp.Visible = True

Set m_XLWorkbook = m_XLApp.Workbooks.Open(FileName:= _
FileNamePath)

m_XLWorkbook.RunAutoMacros Which:=xlAutoOpen
Else
Set m_XLWorkbook = m_XLApp.Workbooks(FileName)
End If

'************************
'* Run Program *
'************************

Set S1 = m_XLWorkbook.Sheets(1)
Set S2 = m_XLWorkbook.Sheets(2)

S1.Select
Range("A1").Select

S1.Cells(5).Value = S2.Cells(1).Value * 2
S1.Cells(6).Value = FileName
Set nodX = trvNo1.Nodes.Add(, , "R1", S1.Cells(5,
4).Value)
nodX.EnsureVisible

Set m_XLApp = Nothing

End Sub

 

[Rob Bovey]

Hi Rick,

Let's start with the early vs. late binding question. Either
CreateObject or GetObject can be used with either late-binding or
early-binding. The thing that determines what type of binding you are using
is how your object variables are declared. If your Excel Application object
variable is declared:

Dim xlApp As Object

then you are using late-binding, no matter how you get the reference to your
instance of Excel. If your Excel Application object variable is declared:

Dim xlApp As Excel.Application

then you are using early binding, no matter how you get the reference to
your instance of Excel.

In general, it is almost always preferable to use early-binding.
Early-bound code executes much faster than late-bound code and with
early-binding you get Intellisense for the Excel objects you're using while
programming in the IDE. Early-binding requires that you set a reference to
the object library you are binding to under the Project/References menu,
late-binding does not.

There are three ways to create an instance of Excel:

Set xlApp = New Excel.Application

Set xlApp = CreateObject("Excel.Application")

Set xlApp = GetObject(<various permutations>)

The first is preferable because it's slightly more efficient than the other
two (CreateObject and GetObject require registry lookups, New doesn't). If
you are trying to use an existing instance of Excel then you must use
GetObject. Now, a few comments on your original code:

''' Always fully qualify your object variable
''' declarations with the object library.
''' This should be "As Excel.Application"
Dim m_XLApp As Application
Dim m_XLWorkbook As Excel.Workbook
'---------------------------------------------------
Private Sub cmdOK_Click()

Dim nodX As Node
Dim FileNamePath As String, FileName As String
''' These should be Excel.Worksheet
Dim S1 As Worksheet, S2 As Worksheet

''' Never use On Error Resume Next unless it is
''' absolutely critical. In this case it is simply
''' masking errors that might have allowed you to
''' figure out what was going on.
On Error Resume Next

FileName = "SampleProgram"
FileNamePath = "A:\SampleProgram.xls"

''' This gets a reference to an existing instance of
''' Excel if one exists, otherwise it throws an error.
''' The error is being masked by On Error Resume Next.
Set m_XLApp = GetObject(, "Excel.Application")
m_XLApp.Visible = True
''' This is invalid. The FileName variable does not
''' contain a valid workbook name. On Error Resume Next
''' is masking this error.
Set m_XLWorkbook = m_XLApp.Workbooks(FileName)

''' As explained in the comment above, the m_XLWorkbook
''' variable will be Nothing. Therefore you are now
''' potentially starting a second instance of Excel.
If m_XLWorkbook Is Nothing Then
Set m_XLApp = Excel.Application
m_XLApp.Visible = True
Set m_XLWorkbook = m_XLApp.Workbooks.Open(FileName:=FileNamePath)
m_XLWorkbook.RunAutoMacros Which:=xlAutoOpen
Else
''' Again, this is not a valid workbook name, so this
''' will not work. However, this branch will never
''' execute, so it doesn't cause an error.
Set m_XLWorkbook = m_XLApp.Workbooks(FileName)
End If

'************************
'* Run Program *
'************************

Set S1 = m_XLWorkbook.Sheets(1)
Set S2 = m_XLWorkbook.Sheets(2)

''' These two lines are not required.
S1.Select
Range("A1").Select

S1.Cells(5).Value = S2.Cells(1).Value * 2
S1.Cells(6).Value = FileName
''' If you run this more than once you'll get an error because you're
''' trying to use the same key name over again.
Set nodX = trvNO1.Nodes.Add(, , "R1", S1.Cells(5, 4).Value)
nodX.EnsureVisible

''' Destroying the reference to Excel does not close it,
''' so you are leaving your instance of Excel orphaned.
Set m_XLApp = Nothing

End Sub

Here is a revised version that should work better:

Option Explicit

Private m_XLApp As Excel.Application
Private m_XLWorkbook As Excel.Workbook

Private Sub cmdOK_Click()

Dim nodX As Node
Dim FileNamePath As String
Dim FileName As String
Dim S1 As Excel.Worksheet
Dim S2 As Excel.Worksheet

FileName = "Book1.xls"
FileNamePath = "E:\Book1.xls"

Set m_XLApp = Excel.Application
m_XLApp.Visible = True
Set m_XLWorkbook = m_XLApp.Workbooks.Open(FileNamePath)
m_XLWorkbook.RunAutoMacros Which:=xlAutoOpen

Set S1 = m_XLWorkbook.Sheets(1)
Set S2 = m_XLWorkbook.Sheets(2)

S1.Range("A5").Value = S2.Range("A1").Value * 2
S1.Range("A6").Value = FileName
Set nodX = trvNO1.Nodes.Add(, , , S1.Range("D5").Value)
nodX.EnsureVisible

m_XLWorkbook.Close False
Set m_XLWorkbook = Nothing

End Sub

Private Sub Form_Terminate()
m_XLApp.Quit
Set m_XLApp = Nothing
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Rob Bovey]

Hi Rick,

I got into a bit of a hurry towards the end of that last message and
gave you a less than optimal revision of your code. Here's a new version
with explanatory comments:

Option Explicit

''' This is now the only object that's required
''' to remain instantiated throughout the life
''' of the form, so it's the only module-level
''' object variable.
Private m_XLApp As Excel.Application

Private Sub Form_Load()
''' Creating an instance of the Excel application is
''' expensive, so only do it once, when the form loads.
Set m_XLApp = New Excel.Application
''' There's no need for Excel to be visible in order
''' to work with it.
'm_XLApp.Visible = True
End Sub

Private Sub cmdOK_Click()

Dim nodX As Node
Dim szPath As String
Dim szFileName As String
''' The workbook is now referenced only within this
''' procedure, so I've moved the declaration here.
Dim xlBook As Excel.Workbook
Dim S1 As Excel.Worksheet
Dim S2 As Excel.Worksheet

''' Eliminated redundancy in previous string variables.
szPath = "E:\"
szFileName = "Book1.xls"

''' This revised procedure opens and closes only the workbook
''' when the OK button is clicked, not the entire Excel app.
Set xlBook = m_XLApp.Workbooks.Open(szPath & szFileName)
xlBook.RunAutoMacros xlAutoOpen

Set S1 = xlBook.Worksheets(1)
Set S2 = xlBook.Worksheets(2)

S1.Range("A5").Value = S2.Range("A1").Value * 2
S1.Range("A6").Value = szFileName

Set nodX = trvNO1.Nodes.Add(, , , S1.Range("D5").Value)
nodX.EnsureVisible

''' Close the workbook without saving.
xlBook.Close False
Set xlBook = Nothing

End Sub

Private Sub Form_Terminate()
''' You must explicitly close the Excel application
''' before destroying its object reference.
m_XLApp.Quit
Set m_XLApp = Nothing
End Sub

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

 

[Rick]

Rob,

Thanks so much for the time that you've spent to help me
understand this. I find it fascinating - the idea of
using VB6 with millions of cells of data storage space
available in Excel sheets. With Excel you can define
blocks of data, making it very useful....

I'll play with it some and then get back. When I
commented out the closing Excel statements (to leave the
Excel workbook there), it still opened up another read-
only workbook - when I pushed the OK button again - which
is what I expected since there was no If-Then statements
to prevent this. I was trying to find a way to not do
that. I was trying to come up with a way, when the user
forgets to close the workbook, that there would be no
harm - that it would still work if someone pushed the OK
button - just as it had before. The program would know
that the workbook was opened and would not open another
workbook with the same name.

That's the reasoning behind my previous code that others
help me to write. One time, I got it to work perfectly -
and I can't find out what I did. I copied the program -
tried to recreate it exactly and it still didn't work
quite right. I did something good, and don't know what I
did. But maybe it's something like a comma, or a dot or a
reference or who-knows-what that may have made it work and
then not work.

What I'm trying to do, with VB6, is use Excel for both a
place of storing data to use later, and also a place to
write the reports (so that is why I'd want the Workbook
open). All the code would be done in VB6, but all the
original database information and reports would be done in
Excel. I have many of my own VBA programs that I'm trying
to convert. I compilied one of these VB6 programs as an
executable, even with my badly written code, and it works
so great - as a professional program that interacts with
Excel.

Thanks also for the time to explain things so clearly,
with your carefully marked comments. You've been a great
help!!! You've helped me before. I also have one of your
books that you wrote with other authors - "Excel 2002
VBA". Thanks.

 

[Rick]

Hi Rob,

Thanks so much. I'll play around with it later, after I
return from work this evening.

Thanks, Rick

 

[Rick]

Hi Rob,

You might not read this, since so much time has passed,
but I wanted to say thank you again. I finally got it to
work yesterday evening. It's perfect.

It didn't work at first, but what I had to do, is first
split my code up into two pieces. It was so difficult for
me to understand. (I've been trying this for a long
while, with only partial success.)

First, I got it to work with the Excel Workbook open
(only). Secondly, I got it to work, when the Workbook had
to be opened. With those two steps fully understood, then
I was able to combine my understanding of them both
and "packaged" the final third version with the If-Then
statement for the two different conditions.

It makes sense now. I didn't want to give up until I
comprehended it completely and got it fully functional. I
think a person just has to work it through...over and over
again. Never give up!

Thanks for your patience and great answers and solutions.

Thanks, Rick