Prev: SIMPL project looking for a VB contribution
Next: Changing colour of vb6 tab control
From: BioColor on 2 Sep 2009 15:43
Hi,

When I call this Sub the first time (with no instance of Excel running
before running the VB), it runs fine (Handler is not executed). The
second time I call it it fails with:

Error 91 Object variable or With block variable not set at the
xlSheet.Cells(2,200) line.

Seems like I'm not closing things properly. What am I doing wrong?

TIA
DuncanC

---
Sub PvtTable1(CSVName As String)

' CSVName is the full path name of the CSV file to be opened in Excel

Dim xlBook As Excel.Workbook
Dim xlApp As Excel.Application

Dim xlSheet As Excel.Worksheet, xlSheet1 As Excel.Worksheet

Dim ExcelWasRunning As Boolean, ErrMsg As String
Dim WorkOpen As Boolean, XLSName As String

' strLRRow, Col are in the CSV sheet
Dim strLRRow As String, strLRCol As String

' See if Excel is running Presumes with spreadsheet open

On Error GoTo Handler

If xlApp Is Nothing Then
Err.Clear
ExcelWasRunning = False
Else
ExcelWasRunning = True
End If

ErrMsg = "Creating Excel app object"

Set xlApp = CreateObject("Excel.application") 'Creates an object

' Delete the XLS file if it exists

XLSName = Left(CSVName, Len(CSVName) - 3) & "xls"

If File_Exist(XLSName) Then
Kill (XLSName)
End If

ErrMsg = "Opening CSV"

' Load the CSV file.
xlApp.Workbooks.Open _
FileName:=CSVName, _
Format:=xlCSV, _
Delimiter:=",", _
ReadOnly:=False

ErrMsg = "Setting xlBook"

Set xlBook = xlApp.ActiveWorkbook
xlBook.Activate

ErrMsg = "Setting xlSheet"
Set xlSheet = xlApp.ActiveSheet
xlSheet.Activate

ErrMsg = "Finding last CSV Column"

xlSheet.Cells(2, 200).Activate
ActiveCell.End(xlToLeft).Select

strLRCol = ActiveCell.Column

GoTo Cleanup

Handler:

ErrMsg = "Error " & Err.Number & ": " & Err.Description & vbCrLf _
& "in section: " & ErrMsg
MsgBox (ErrMsg)


Cleanup:

ErrMsg = "in cleanup."

xlApp.ActiveWorkbook.Close False

Set xlBook = Nothing
Set xlSheet = Nothing
Set xlSheet1 = Nothing

If Not ExcelWasRunning Then
xlApp.Quit
Set xlApp = Nothing
End If

Err.Clear

End Sub
From: BioColor on 2 Sep 2009 17:22
On Wed, 02 Sep 2009 15:43:20 -0400, BioColor(a)aol.com wrote:

Oops!
>
>Error 91 Object variable or With block variable not set at the
>xlSheet.Cells(2,200) line.

It fails on the ActiveCell.End line.

Cheers,
DuncanC

From: BioColor on 3 Sep 2009 10:23
On Wed, 02 Sep 2009 15:43:20 -0400, BioColor(a)aol.com wrote:

>Hi,
>
>When I call this Sub the first time (with no instance of Excel running
>before running the VB), it runs fine (Handler is not executed). The
>second time I call it it fails with:
>
>Error 91 Object variable or With block variable not set at the
>xlSheet.Cells(2,200) line.
>
>Seems like I'm not closing things properly. What am I doing wrong?
>
>TIA
>DuncanC
>
>---
>Sub PvtTable1(CSVName As String)
>
>' CSVName is the full path name of the CSV file to be opened in Excel
>
>Dim xlBook As Excel.Workbook
>Dim xlApp As Excel.Application
>
>Dim xlSheet As Excel.Worksheet, xlSheet1 As Excel.Worksheet
>
>Dim ExcelWasRunning As Boolean, ErrMsg As String
>Dim WorkOpen As Boolean, XLSName As String
>
>' strLRRow, Col are in the CSV sheet
>Dim strLRRow As String, strLRCol As String
>
>' See if Excel is running Presumes with spreadsheet open
>
>On Error GoTo Handler
>
>If xlApp Is Nothing Then
> Err.Clear
> ExcelWasRunning = False
>Else
> ExcelWasRunning = True
>End If
>
>ErrMsg = "Creating Excel app object"
>
>Set xlApp = CreateObject("Excel.application") 'Creates an object
>......
>
>End Sub

I fixed this. I guess I was using the wrong method of determining if
Excel was already running. It also turns out that Excel stays running
even after xlApp.quit until the VB program terminates (according to
Task Manager). So I use this method instead:


On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")

If Err.Number Then
Err.Clear
ExcelWasRunning = False
Set xlApp = CreateObject("Excel.application")
Else
ExcelWasRunning = True
End If

Thanks to:
http://en.allexperts.com/q/Visual-Basic-1048/Open-excel-VB.htm

Cheers,
DuncanC
 | 
Pages: 1
Prev: SIMPL project looking for a VB contribution
Next: Changing colour of vb6 tab control