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_He
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.