VB6 to Excel

[Rick]

Hi,

Over the past few months, I've been experimenting with
writing VBA code in VB6 and using the data in Excel to run
the programs. It seems to work OK, but I had a few
questions. I'm able to use a form in VB6, take the data
from an Excel sheet and write another report on another
sheet in Excel. If I already have that workbook open, it
knows it. However, I'm pushing into other directions, and
complicating things - with a multitude of other controls
such as treeview and listview. This is what I have so far:

My String Variables are FileName and FileNamePath.
My Objects are m_XLApp and m_XLWorkbook

Set m_XLApp = GetObject("", "Excel.Application")
m_XLApp.Visible = True
Set m_XLWorkbook = m_XLApp.Workbooks(FileName)
'*******************************************
'* Do I need the next three lines of code? *
'*******************************************
If Err.Number <> 0 Then
'if not found then create it
Set m_XLWorkbook = CreateObject("Excel.Application")
End If

If m_XLWorkbook Is Nothing Then 'Excel File Not There
Set m_XLApp = Excel.Application
m_XLApp.Visible = True
Set m_XLWorkbook = m_XLApp.Workbooks.Open(FileName:=
_
FileNamePath)
m_XLWorkbook.RunAutoMacros Which:=xlAutoOpen
Else 'Excel File Is There
Set m_XLWorkbook = m_XLApp.Workbooks(FileName)
End If

I've borrowed, cut, and pasted this code together, and to
be perfectly honest, I really don't completely understand
it - although I can logically make some assumptions. I
got it to work with some experimentation in some cases.

Here's my questions:

1) What's the difference between GetObject and
CreateObject?

2) If I write "If m_XLWorkbook Is Nothing", does that mean
if the workbook is not open? (that's my assumption)

3) Why does "If m_XLWorkbook Is Nothing" seem to work if I
have only one action to perform on a form, instead of two
or three actions in three different subroutines?

4) In a narrative, could someone explain what the above
code means, line by line? I sort of understand it, but
some areas are still a little "gray" for me. Is there a
better way to write this code?

I can elaborate further if anyone would like. Could you
give me some hints, of some similar code that you use
also - that would better perform the work? I would really
appreciate it. I trying to understand this code, line by
line, of exactly what it means in simple terms. I'm
always pushing the limits, trying different things. I
really appreciate any feedback. Do you recommend any
books or websites that would help also? It's a lot of
fun, particularly when you can complile the VB6 program
into an executable - and then to your amazement - it
actually works.

Thanks, Rick

 

[Rick]

Bob,

Thanks so much for replying. I think maybe you've helped
me before, months ago. I'll review what you wrote and try
some more programming. A lot of what I do with this, is
by trial and error. It's too fun to not to want to learn
more.... I appreciate the answers and tips to writing
this better.

Maybe later, I'll get back with my more complicated
treeview and listview situation in VB6. It works really
well, but if I already have the Excel workbook open, it
opens it up again (as a read-only file). But with other
different programs that I wrote with only one subroutine,
it doesn't open another workbook (with the same file
name), if it already is open on the computer. I got it to
work by "hacking" my way through it. This hacking is a
safe way to accomplish an immediate task, but doesn't help
me understand what I'm doing.... So, what I'm doing is
writing a lot of different Excel VBA programs in VB6,
trying a variety of things, until I'm able to have a
stronger grasp of what I'm doing. Your tips are greatly
appreciated.

Thanks, Rick

 

[Bob Phillips]

Rick,

Okay then, we'll see you when you get stuck again, but in the meantime ...
have fun!

Regards

Bob