Closing Excel connection 1

[solo7]

I am using a VB front end to get the address of a spreadsheet, and the worksheets in the spreadsheet. I'm then putting the values from a selected worksheet into a Access table for report producing.

I have a persistant error which I think may be related to my opening and closing the spreadsheet. As when i open the spreadsheet after I have run my VB front end I get a 'Locked for editing - open read only' message.

Can anyone spot the deliberate mistake in my code please ;>)

'display new message in the label box
'
Label1.Caption = " Determining the names of your Spreadsheet 'Sheets'"

'---------- get the names of the sheets in the workbook
'
Dim xlobject As Object
Dim lngNumberOfSheets As Long
Dim i As Long
Dim strNameOfSheet As String

Set xlobject = CreateObject("Excel.Application")
Set xlobject = xlobject.Workbooks.Open(StrTemp)

lngNumberOfSheets = xlobject.Worksheets.Count 'number of sheets in a spreadsheet

'---------- populate the list box with the names of the spreadsheets 'sheets'
'
For i = 1 To lngNumberOfSheets
   strNameOfSheet = xlobject.Worksheets(i).Name 'name of sheet
   List1.AddItem strNameOfSheet
Next i

'---------- display sheet count
'
Label2.Caption = lngNumberOfSheets & " Sheets"

'---------- Populate the hidden label with the actual location of the spreadsheet
'
Label3.Caption = StrTemp

'---------- move focus
'
List1.SetFocus

'---------- lock text box so nobody can tamper with the address
'
Text1.Locked = True

'display new message in the label box
'
Label1.Caption = " Now choose from the list on the left, the spreadsheet you want to produce the ROA'S from"

Set xlobject = Nothing
End Sub

Solo7

 

[BiggerBrother]

Although you have set xlobject to nothing, you never close the connection. Try inserting the line xlobject.close before the set nothing line.

BB

 

[dilettante]

Where are you doing [tt]xlobject.Quit[/tt] in your program? Are you sure you are not leaving Excel running in "no visible UI" mode with your workbook open?

Sometimes Task Manager can be revealing. ;-)

 

[solo7]

BiggerBrother
I think that has got it - It lookes like I NEVER closed the connection. Thanks !!

solo7

 

[BiggerBrother]

I've had the same issue with access.

BB