Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    3

    Unhappy Unanswered: Access and excel link

    I am trying to open a form that links to an excel file on the network. No matter what sharing permissions i set up it keeps telling me the microsoft jet engine cannot open the file it is open EXCLUSIVELY by another. I can view the excel file on numerous clients but soon as use the form it throws up the error.
    Can anyone help me with this one please.

  2. #2
    Join Date
    Feb 2004
    Location
    Swindon, UK
    Posts
    86
    not sure if it will work, but i have a large quantity of users who all use the same db, one of the main actions is that it copies information from a table into an excel spreadsheet, then runs a few macros within the excel spreadsheet. this is used by at least 20 people, 8 hours a day and i've not had anyone mentioning about it being open exclusively by another user. i used the code below, but you will have to have a play with it and remove the parts that copy the info over.

    Sub CopyToExcel()
    'Copy records to first 20000 rows
    'in an existing Excel Workbook and worksheet
    '
    Dim objXL As Excel.Application
    Dim objWkb As Excel.Workbook
    Dim objSht As Excel.Worksheet
    Dim db As Database
    Dim rs As Recordset
    Dim intLastCol As Integer
    Dim X
    Const conMAX_ROWS = 20000
    Const conSHT_NAME = "Info"
    Const conWKB_NAME = "N:\EHDatabase\BSCWkly ROCs MI.xls"
    Set db = CurrentDb
    Set objXL = New Excel.Application
    Set rs = db.OpenRecordset("tbl-RefundQuantityDetailsTemp", dbOpenSnapshot)
    With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not err.Number = 0 Then
    Set objSht = objWkb.Worksheets.Add
    objSht.Name = conSHT_NAME
    End If
    err.Clear
    On Error GoTo 0
    intLastCol = objSht.UsedRange.Columns.Count
    With objSht
    .Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
    intLastCol)).ClearContents
    .Range(.Cells(1, 1), _
    .Cells(1, rs.Fields.Count)).Font.Bold = True
    .Range("A2").CopyFromRecordset rs
    End With
    Set rs = db.OpenRecordset("tbl-RefundReasonDetailsTemp", dbOpenSnapshot)
    If Not err.Number = 0 Then
    Set objSht = objWkb.Worksheets.Add
    objSht.Name = conSHT_NAME
    End If
    err.Clear
    On Error GoTo 0
    intLastCol = objSht.UsedRange.Columns.Count
    With objSht
    .Range(.Cells(1, 1), _
    .Cells(1, rs.Fields.Count)).Font.Bold = True
    .Range("A10").CopyFromRecordset rs

    End With
    '.ActiveSheet.PrintOut
    '.ActiveWorkbook.Application.Run "'Refund Letter.xls'!Macro1"
    '.ActiveWorkbook.Close SaveChanges:=False
    '.Quit
    End With
    Set objSht = Nothing
    Set objWkb = Nothing
    Set objXL = Nothing
    Set rs = Nothing
    Set db = Nothing

    End Sub
    "Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done."

  3. #3
    Join Date
    Oct 2003
    Location
    US
    Posts
    343
    Do you have that excel file linked into the Access db? If you have and still having problems with then I would recommend, ask everyone to get out of the app ( you can use tools to tell you if someone is still in a db. take a look at this http://www.mvps.org/access/modules/mdl0055.htm), dropping that link, recreate the link and then try.

  4. #4
    Join Date
    May 2004
    Posts
    3
    Quote Originally Posted by thebeast
    I am trying to open a form that links to an excel file on the network. No matter what sharing permissions i set up it keeps telling me the microsoft jet engine cannot open the file it is open EXCLUSIVELY by another. I can view the excel file on numerous clients but soon as use the form it throws up the error.
    Can anyone help me with this one please.
    It sounds like you have a person who has the Excel file open. If you don't want people opening the Excel file directly, you can prevent this by password-protecting it.

    If you want them to be able to access the Excel file, I'd suggest creating a new Excel file and linking individual cells to cells in the original file. Keep this new file where people can't open it, attach it to your database and this will probably solve the problem.

  5. #5
    Join Date
    Jul 2004
    Posts
    3
    Thanks for that Mowgli212001 i will try it out

  6. #6
    Join Date
    Jul 2004
    Posts
    3
    I Should have mentioned i am testing this so there are no other users on the system.

  7. #7
    Join Date
    Feb 2004
    Location
    Swindon, UK
    Posts
    86
    let me know how it goes mate
    "Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •