opening an excel sheet from Access

[Kretzer]

Hi,

i'm trying to open an access workbook and want to jump to an special sheet inside that workbook.
rs is a DAO.recordset and delivers the relative path of the xls file.
lst_Auswahl is a list box where an id is selecte. This ID is similar to the name of the sheet which shall be opened.

I try this using following code:
[...]

For intI = 1 To Me.lst_Auswahl.ListCount - 1
     If Me.lst_Auswahl.Selected(intI) = True Then
         Set rs = CurrentDb.OpenRecordset("select [Pfad] from [tbl_Bilder] where ID like '" &     Me.lst_Auswahl.Column(0, intI) & "'", dbOpenSnapshot)                 
         Set MyXL = New excel.Application
         Debug.Print "C:\" & rs(0)
         Set MyWorkbook = excel.Workbooks.Open("C:\" & rs(0))
         Set MySheet = MyXL.Sheets(Me.lst_Auswahl.Column(2, intI))
         MyXL.Visible = True
      End If
   Next

[...]

The problem is that it's not working very properly. It does work from time to time - but one problem seems to be that the file is permanently locked - once it was opened with this method.
Does anyone can explain the mistake to me?

Thank you
Martin

 

[PHV]

You may try to replace this:
Set MySheet = MyXL.Sheets
By this:
Set MySheet = MyWorkbook.Sheets

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886