Very Basic Excel Object Questions

An old Cobol guy playing around with VB 3.0 and Excel (Office 2K) needs help
on his first OO program, which is simply reading-only the contents of an
..xls file. I have two simple issues:

1) Wrap-up - I see code examples for closing workbooks, quitting
objects and setting objects to nothing. What is the proper way to wrap up
everything when the I'm done with my objects/files? In using the following
code, I cannot get the Close workbook to execute without either a syntax
error or Method not appl for this object. An instance of Excel is left
running when my program is done (I see it doing ctrl-alt-del and it's got a
hold of the cfe.xls file, too).

Dim xExcelApp As object
Dim xWorkbook As object
Dim xSheet As object
Set xExcelApp = GetObject("", "Excel.Application")
Set xWorkbook = xExcelApp.Workbooks.Open("c:\cfe.xls")
Set xSheet = xWorkbook.Worksheets(form1.Text1) (Text1 has worksheet name)
DO MY THING. . .
WrapUp:
' Release resources
'xExcelApp.Workbooks(1).Close(), tried it w/o the (1) and/or w/o the
(), tried as xWorkbook.Close(), w/o the () and even with the () filled in
with SaveChanges:=False -- hurumph!! -- nothing works.
Set xExcelApp = Nothing
Set xWorkbook = Nothing
Set xSheet = Nothing -- what does Quit do for me? Will
doing something at the xExcelApp level take care of
everything
underneath?

2) During the DO MY THING above, the 'If xSheet.Cells(i, j) = "" Then'
statement errors with err=437 whenever the cell is empty. Since I have on
error resume next, when the error occurs it falls to the next statement -
which is what I want it to do in this example anyway, but coding based on
receiving this error for empty cells seems ridiculous to me, especially if
what I really want to code is 'If xSheet.Cells(i, j) = "something besides
nulls". Any ideas?

Otherwise, I'm having fun again and I've used the worksheet values to
create an Outlook email item and send it! Any help for these elementary
issues with Excel and in general, wrapping up, is greatly appreciated.

BJB,

I cannot quickly find what error 437 is.
It is not in the list of trappable errors.
However, you could experiment with...

If xSheet.Cells(i, j).Value = ""
or
If Len(xSheet.Cells(i, j).Value) =0

Of course the above assumes that i and j are valid variables.

'-------------------------------------------------------------
Here are some general guidelines to use when automating Excel...

1. Set a reference to the primary Excel objects used in your program.
Dim xlApp As Excel.Application
Dim WB As Excel.Workbook
Dim WS As Excel.Worksheet

Set xlApp = New Excel.Application
Set WB = xlApp.Workbooks.Add
Set WS = WB.Sheets(1)

Use the appropriate reference Every Time you make reference to a spreadsheet.
Do not use Range(xx) - use WS.Range(xx)
Cells should be WS.Cells(10, 20) or _
WS.Range(WS.Cells(10, 20), WS.Cells(20, 40))

2. Avoid the use of ActiveSheet, ActiveWorkbook, Selection etc.
Use your object references.

3. Avoid the use of the "With" construct.

4. Set all objects to Nothing in the proper order - child then parent.
Set WS = Nothing
WB.Close SaveChanges:=True 'your choice
Set WB = Nothing
xlApp.Quit
Set xlApp = Nothing

Violating any of these guidelines can leave "orphans" that still refer
to Excel and prevent the application from closing.

'------------------------------------------------------------

Jim Cone
San Francisco, USA



"BiilyJoeBob" <[email protected]> wrote in message
news:[email protected]...
An old Cobol guy playing around with VB 3.0 and Excel (Office 2K) needs help
on his first OO program, which is simply reading-only the contents of an
..xls file. I have two simple issues:

1) Wrap-up - I see code examples for closing workbooks, quitting
objects and setting objects to nothing. What is the proper way to wrap up
everything when the I'm done with my objects/files? In using the following
code, I cannot get the Close workbook to execute without either a syntax
error or Method not appl for this object. An instance of Excel is left
running when my program is done (I see it doing ctrl-alt-del and it's got a
hold of the cfe.xls file, too).

Dim xExcelApp As object
Dim xWorkbook As object
Dim xSheet As object
Set xExcelApp = GetObject("", "Excel.Application")
Set xWorkbook = xExcelApp.Workbooks.Open("c:\cfe.xls")
Set xSheet = xWorkbook.Worksheets(form1.Text1) (Text1 has worksheet name)
DO MY THING. . .
WrapUp:
' Release resources
'xExcelApp.Workbooks(1).Close(), tried it w/o the (1) and/or w/o the
(), tried as xWorkbook.Close(), w/o the () and even with the () filled in
with SaveChanges:=False -- hurumph!! -- nothing works.
Set xExcelApp = Nothing
Set xWorkbook = Nothing
Set xSheet = Nothing -- what does Quit do for me? Will
doing something at the xExcelApp level take care of everything underneath?

2) During the DO MY THING above, the 'If xSheet.Cells(i, j) = "" Then'
statement errors with err=437 whenever the cell is empty. Since I have on
error resume next, when the error occurs it falls to the next statement -
which is what I want it to do in this example anyway, but coding based on
receiving this error for empty cells seems ridiculous to me, especially if
what I really want to code is 'If xSheet.Cells(i, j) = "something besides
nulls". Any ideas?

Otherwise, I'm having fun again and I've used the worksheet values to
create an Outlook email item and send it! Any help for these elementary
issues with Excel and in general, wrapping up, is greatly appreciated.

> 1) Wrap-up - I see code examples for closing workbooks, quitting
> objects and setting objects to nothing. What is the proper way to wrap up
> everything when the I'm done with my objects/files? In using the
following
> code, I cannot get the Close workbook to execute without either a syntax
> error or Method not appl for this object. An instance of Excel is left
> running when my program is done (I see it doing ctrl-alt-del and it's got
a
> hold of the cfe.xls file, too).

See http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/
if you have not already

but it appears that you're using Late Binding, so the gloabl reference issue
may not apply here

> Dim xExcelApp As object
> Dim xWorkbook As object
> Dim xSheet As object
> Set xExcelApp = GetObject("", "Excel.Application")

personally, I would just get my own instance...

> Set xWorkbook = xExcelApp.Workbooks.Open("c:\cfe.xls")
> Set xSheet = xWorkbook.Worksheets(form1.Text1) (Text1 has worksheet
name)
> DO MY THING. . .
> WrapUp:
> ' Release resources
> 'xExcelApp.Workbooks(1).Close(), tried it w/o the (1) and/or w/o the
> (), tried as xWorkbook.Close(), w/o the () and even with the () filled in
> with SaveChanges:=False -- hurumph!! -- nothing works.
> Set xExcelApp = Nothing
> Set xWorkbook = Nothing
> Set xSheet = Nothing -- what does Quit do for me? Will
> doing something at the xExcelApp level take care of
> everything
> underneath?

Did you try xExcelApp.Quit?

Actually, it should be
..Close
..Quit
then = Nothing

I have a "hammer" function I sometimes use - but only if my function with
the excel automation does not finish gracefully:

Public Function CleanUp(procName As String)
On Error Resume Next
Dim objProcList As Object
Dim objWMI As Object
Dim objProc As Object
'create WMI object instance
Set objWMI = GetObject("winmgmts:")
If Not IsNull(objWMI) Then
'create object collection of Win32 processes
Set objProcList = objWMI.InstancesOf("win32_process")
For Each objProc In objProcList 'iterate through enumerated
collection
If UCase(objProc.Name) = UCase(procName) Then
objProc.Terminate (0)
End If
Next
End If
Set objProcList = Nothing
Set objWMI = Nothing
End Function

> 2) During the DO MY THING above, the 'If xSheet.Cells(i, j) = "" Then'
> statement errors with err=437 whenever the cell is empty. Since I have on
> error resume next, when the error occurs it falls to the next statement -
> which is what I want it to do in this example anyway, but coding based on
> receiving this error for empty cells seems ridiculous to me, especially if
> what I really want to code is 'If xSheet.Cells(i, j) = "something besides
> nulls". Any ideas?

I'm sure there's a better way to check the cell - perhaps If
Len(cellContents) <> 0 or something? (<-- pseudo code)

I just finished a big project where I had to reference alot of cells/ranges.
I'm thinking the next time I need to do this stuff I will ALWAYS use a
string to reference ranges/cells - easier to troubleshoot.
For example:

strVarp = "=VARP($" & GetXlClmLtr(gvc) & fr + 1 & ", " & _
GetXlClmLtr(fdc) & fr + 1 & ") > ($" & GetXlClmLtr(gvc) & _
"$" & (lr + 2) & "*$" & GetXlClmLtr(gvc + 1) & fr + 1 & ")"
'Debug.Print strVarp
xlapp.Workbooks(strXlsFile).Worksheets(sn) _
.Range(strRange).FormatConditions.Add _
Type:=xlExpression, Formula1:=strVarp

below is GetXlClmLtr:

Public Function GetXlClmLtr(ByVal cn As Integer) As String
On Error GoTo HandleErr
Dim intFirst As Integer
Dim intSecond As Integer
If cn < 27 Then 'cn is column number
GetXlClmLtr = Chr(cn + 64)
Else
intFirst = cn \ 26
intSecond = cn Mod 26
If intSecond = 0 Then
intSecond = 26
intFirst = intFirst - 1
End If
GetXlClmLtr = Chr(intFirst + 64) & Chr(intSecond + 64)
End If
Exit_Here:
Exit Function
HandleErr:
GetXlClmLtr = vbNullString
Resume Exit_Here
End Function

> Otherwise, I'm having fun again and I've used the worksheet values to
> create an Outlook email item and send it! Any help for these elementary
> issues with Excel and in general, wrapping up, is greatly appreciated.
>

Correction:

> Actually, it should be

..Save
..Close
..Quit
then = Nothing

I'm impressed that anyone would help me, much less this quickly. Thanks Jim
and deko.

err 437 in vb3.0 is OLE Automation method did not return a value
Error 437
A Visual Basic statement refers to a method of an object variable as if it
returned a value. However, the method does not return a value when it is
called.

I'm using xSheet.Cells(x,y) all over the place, successfully, so I assumed
that .Value, as Jim suggests, was a default. I'll give the explicit .Value a
try.

deko, thanks for your hammer code and cell string stuff (the latter I'll
need to study and file away). I'll try len(str) along with .Value.

Just to be clear, when I say the Close statements I've tried didn't work,
that doesn't mean they executed and didn't work; I sometimes get "Expecting
end-of-statement"
right after the word Close and the "Method not app for this obj" (depending
on which version of the close I use) BEFORE I can even Debug/Run my code!
i.e., something is wrong with the way I'm typing the workbook close statement.

I just read of using App.Workbooks.Item(1).Close but haven't tried this Item
twist yet.

I was tired of searching for answers on the net, so when I posted here
(first time), I had not done a search here. I did search after I posted and,
in fact, saw lots of Close problems and even Jim's general guidelines. Man,
this takes up a lot of daggone time, but I appreciate your help!


"Jim Cone" wrote:

> BJB,
>
> I cannot quickly find what error 437 is.
> It is not in the list of trappable errors.
> However, you could experiment with...
>
> If xSheet.Cells(i, j).Value = ""
> or
> If Len(xSheet.Cells(i, j).Value) =0
>
> Of course the above assumes that i and j are valid variables.
>
> '-------------------------------------------------------------
> Here are some general guidelines to use when automating Excel...
>
> 1. Set a reference to the primary Excel objects used in your program.
> Dim xlApp As Excel.Application
> Dim WB As Excel.Workbook
> Dim WS As Excel.Worksheet
>
> Set xlApp = New Excel.Application
> Set WB = xlApp.Workbooks.Add
> Set WS = WB.Sheets(1)
>
> Use the appropriate reference Every Time you make reference to a spreadsheet.
> Do not use Range(xx) - use WS.Range(xx)
> Cells should be WS.Cells(10, 20) or _
> WS.Range(WS.Cells(10, 20), WS.Cells(20, 40))
>
> 2. Avoid the use of ActiveSheet, ActiveWorkbook, Selection etc.
> Use your object references.
>
> 3. Avoid the use of the "With" construct.
>
> 4. Set all objects to Nothing in the proper order - child then parent.
> Set WS = Nothing
> WB.Close SaveChanges:=True 'your choice
> Set WB = Nothing
> xlApp.Quit
> Set xlApp = Nothing
>
> Violating any of these guidelines can leave "orphans" that still refer
> to Excel and prevent the application from closing.
>
> '------------------------------------------------------------
>
> Jim Cone
> San Francisco, USA
>
>
>
> "BiilyJoeBob" <[email protected]> wrote in message
> news:[email protected]...
> An old Cobol guy playing around with VB 3.0 and Excel (Office 2K) needs help
> on his first OO program, which is simply reading-only the contents of an
> ..xls file. I have two simple issues:
>
> 1) Wrap-up - I see code examples for closing workbooks, quitting
> objects and setting objects to nothing. What is the proper way to wrap up
> everything when the I'm done with my objects/files? In using the following
> code, I cannot get the Close workbook to execute without either a syntax
> error or Method not appl for this object. An instance of Excel is left
> running when my program is done (I see it doing ctrl-alt-del and it's got a
> hold of the cfe.xls file, too).
>
> Dim xExcelApp As object
> Dim xWorkbook As object
> Dim xSheet As object
> Set xExcelApp = GetObject("", "Excel.Application")
> Set xWorkbook = xExcelApp.Workbooks.Open("c:\cfe.xls")
> Set xSheet = xWorkbook.Worksheets(form1.Text1) (Text1 has worksheet name)
> DO MY THING. . .
> WrapUp:
> ' Release resources
> 'xExcelApp.Workbooks(1).Close(), tried it w/o the (1) and/or w/o the
> (), tried as xWorkbook.Close(), w/o the () and even with the () filled in
> with SaveChanges:=False -- hurumph!! -- nothing works.
> Set xExcelApp = Nothing
> Set xWorkbook = Nothing
> Set xSheet = Nothing -- what does Quit do for me? Will
> doing something at the xExcelApp level take care of everything underneath?
>
> 2) During the DO MY THING above, the 'If xSheet.Cells(i, j) = "" Then'
> statement errors with err=437 whenever the cell is empty. Since I have on
> error resume next, when the error occurs it falls to the next statement -
> which is what I want it to do in this example anyway, but coding based on
> receiving this error for empty cells seems ridiculous to me, especially if
> what I really want to code is 'If xSheet.Cells(i, j) = "something besides
> nulls". Any ideas?
>
> Otherwise, I'm having fun again and I've used the worksheet values to
> create an Outlook email item and send it! Any help for these elementary
> issues with Excel and in general, wrapping up, is greatly appreciated.
>

tip:

goto VBeditor with AltF11

press f1 for help
search "object model"
select "excel object model"

make a print and stick it on the wall near your PC.

that'll give you a good overview of where to find
what and how objects are related.

Next:
in VBE:

always use option explicit
always dim your variables as proper type,
makes intellisense more usefull

dim wks as Worksheet
dim rng as Range

make sure the Locals window is visible

for debugging AND to learn object properties:
use breakpoints.
explore the locals window.

for details on methods/Properties:
put the cursor on a word (select either nothing or entire word)
press F1.


hth, cheerz!

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


BillieJoeBob wrote :

> I'm impressed that anyone would help me, much less this quickly.
> Thanks Jim and deko.
>
> err 437 in vb3.0 is OLE Automation method did not return a value
> Error 437
> A Visual Basic statement refers to a method of an object variable as
> if it returned a value. However, the method does not return a value
> when it is called.
>
> I'm using xSheet.Cells(x,y) all over the place, successfully, so I
> assumed that .Value, as Jim suggests, was a default. I'll give the
> explicit .Value a try.
>
> deko, thanks for your hammer code and cell string stuff (the latter
> I'll need to study and file away). I'll try len(str) along with
> .Value.
>
> Just to be clear, when I say the Close statements I've tried didn't
> work, that doesn't mean they executed and didn't work; I sometimes
> get "Expecting end-of-statement"
> right after the word Close and the "Method not app for this obj"
> (depending on which version of the close I use) BEFORE I can even
> Debug/Run my code! i.e., something is wrong with the way I'm typing
> the workbook close statement.
>
> I just read of using App.Workbooks.Item(1).Close but haven't tried
> this Item twist yet.
>
> I was tired of searching for answers on the net, so when I posted
> here (first time), I had not done a search here. I did search after
> I posted and, in fact, saw lots of Close problems and even Jim's
> general guidelines. Man, this takes up a lot of daggone time, but I
> appreciate your help!
>
>
> "Jim Cone" wrote:
>
> > BJB,
> >
> > I cannot quickly find what error 437 is.
> > It is not in the list of trappable errors.
> > However, you could experiment with...
> >
> > If xSheet.Cells(i, j).Value = ""
> > or
> > If Len(xSheet.Cells(i, j).Value) =0
> >
> > Of course the above assumes that i and j are valid variables.
> >
> > '-------------------------------------------------------------
> > Here are some general guidelines to use when automating Excel...
> >
> > 1. Set a reference to the primary Excel objects used in your
> > program. Dim xlApp As Excel.Application
> > Dim WB As Excel.Workbook
> > Dim WS As Excel.Worksheet
> >
> > Set xlApp = New Excel.Application
> > Set WB = xlApp.Workbooks.Add
> > Set WS = WB.Sheets(1)
> >
> > Use the appropriate reference Every Time you make reference to a
> > spreadsheet. Do not use Range(xx) - use WS.Range(xx)
> > Cells should be WS.Cells(10, 20) or _
> > WS.Range(WS.Cells(10, 20), WS.Cells(20, 40))
> >
> > 2. Avoid the use of ActiveSheet, ActiveWorkbook, Selection etc.
> > Use your object references.
> >
> > 3. Avoid the use of the "With" construct.
> >
> > 4. Set all objects to Nothing in the proper order - child then
> > parent. Set WS = Nothing
> > WB.Close SaveChanges:=True 'your choice
> > Set WB = Nothing
> > xlApp.Quit
> > Set xlApp = Nothing
> >
> > Violating any of these guidelines can leave "orphans" that still
> > refer to Excel and prevent the application from closing.
> >
> > '------------------------------------------------------------
> >
> > Jim Cone
> > San Francisco, USA
> >
> >
> >
> > "BiilyJoeBob" <[email protected]> wrote in message
> > news:[email protected]...
> > An old Cobol guy playing around with VB 3.0 and Excel (Office 2K)
> > needs help on his first OO program, which is simply reading-only
> > the contents of an ..xls file. I have two simple issues:
> >
> > 1) Wrap-up - I see code examples for closing workbooks,
> > quitting objects and setting objects to nothing. What is the
> > proper way to wrap up everything when the I'm done with my
> > objects/files? In using the following code, I cannot get the Close
> > workbook to execute without either a syntax error or Method not
> > appl for this object. An instance of Excel is left running when my
> > program is done (I see it doing ctrl-alt-del and it's got a hold of
> > the cfe.xls file, too).
> >
> > Dim xExcelApp As object
> > Dim xWorkbook As object
> > Dim xSheet As object
> > Set xExcelApp = GetObject("", "Excel.Application")
> > Set xWorkbook = xExcelApp.Workbooks.Open("c:\cfe.xls")
> > Set xSheet = xWorkbook.Worksheets(form1.Text1) (Text1 has
> > worksheet name) DO MY THING. . .
> > WrapUp:
> > ' Release resources
> > 'xExcelApp.Workbooks(1).Close(), tried it w/o the (1) and/or
> > w/o the (), tried as xWorkbook.Close(), w/o the () and even with
> > the () filled in with SaveChanges:=False -- hurumph!! --
> > nothing works. Set xExcelApp = Nothing
> > Set xWorkbook = Nothing
> > Set xSheet = Nothing -- what does Quit do for
> > me? Will doing something at the xExcelApp level take care of
> > everything underneath?
> >
> > 2) During the DO MY THING above, the 'If xSheet.Cells(i, j) = ""
> > Then' statement errors with err=437 whenever the cell is empty.
> > Since I have on error resume next, when the error occurs it falls
> > to the next statement - which is what I want it to do in this
> > example anyway, but coding based on receiving this error for empty
> > cells seems ridiculous to me, especially if what I really want to
> > code is 'If xSheet.Cells(i, j) = "something besides nulls". Any
> > ideas?
> >
> > Otherwise, I'm having fun again and I've used the worksheet values
> > to create an Outlook email item and send it! Any help for these
> > elementary issues with Excel and in general, wrapping up, is
> > greatly appreciated.
> >