• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

How to Release link to Excel file

I am using the attached code to link to an Excel Spreadsheet from Access 2007.  

The user selects the Excel file to look at and review its contents.  He can then clear the display and select another.  He keeps selecting and reviewing the contents of Excel files until he finds the one he wants, and then clicks a button to proceed.

The problem occurs when, after reviewing an Excel file, he clears the display, but then goes back and selects the same file again.  The "File_Is_Locked" function cannot access the file a second time because "it is open by another process".  Somehow, even though I have deleted the TableDef, the Excel file is still marked as opened.

Even if I just try to open the Excel file from the desktop, I cannot because "it is open by another process".  

How can I mark the Excel file as closed after I am done with it?

This is the progression:
1. First, I use "File_Is_Locked" to make sure the file is not already open by Excel or some other process.
2. "Delete_Temp_Whatever" deletes the TableDef in case some other process happened to not delete it when it was done using it.
3. "Set tbl = db.CreateTableDef..." successfully establishes the link to the Excel file.
4. "Delete_Temp_Whatever" deletes the TableDef because we are done using it.

Private Sub MyHandler()
    g_strExcelTabName = "Rules$A1:C1"
    g_strLinkedTableName = "Temp_Whatever"
    Set db = CurrentDb()
    Delete_Temp_Whatever 'In case some other process happened to not delete it
    Debug.Print g_strFullPathExcelFileKT
    Set tbl = db.CreateTableDef(g_strLinkedTableName, _
    dbAttachSavePWD, g_strExcelTabName, "Excel 12.0 Xml;HDR=NO;IMEX=1;ACCDB=YES;Database=" & g_strFullPathExcelFileKT & _
    ";Pwd=MyPswd")
    db.TableDefs.Append tbl
...
...
...
    Delete_Temp_Whatever 'Get rid of the temporary table
End Sub

Public Sub Delete_Temp_Whatever()
On Error GoTo Delete_Temp_Whatever_Error_Recovery
    DoCmd.DeleteObject acTable, "Temp_Whatever"
Delete_Temp_Whatever_Error_Recovery:
    Exit Sub
End Sub

Function File_Is_Locked(strFileName As String) As Boolean
    On Error Resume Next
    ' If the file is already opened by another process,
    ' and the specified type of access is not allowed,
    ' the Open operation fails and an error occurs.
    Open strFileName For Binary Access Read Write Lock Read Write As #1
    Close #1
    Debug.Print Err.Number
    Debug.Print Err.Description
    ' If an error occurs, the document is currently open.
    If Err.Number <> 0 Then
        File_Is_Locked = True
        Err.Clear
    Else
        File_Is_Locked = False
   End If
End Function

Open in new window

0
wsturdev
Asked:
wsturdev
  • 6
  • 4
1 Solution
 
Rey Obrero (Capricorn1)Commented:
have you tried just linking to the excel file

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, _
    "myExcelLink", "C:myExcel.xls", True, "Sheet1$"
0
 
wsturdevAuthor Commented:
Yes, but that causes the Nav Pane to show up (there is apparently some kind of fault in the TransferSpreadsheet function.  Microsoft gave me the code I am using.
0
 
Rey Obrero (Capricorn1)Commented:
and it gave birth to another problem ;-)  
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Rey Obrero (Capricorn1)Commented:
can you upload a copy of the excel file.. i'll experiment on this when i get home.
0
 
wsturdevAuthor Commented:
The attached file is supposed to be an XLSM, so please change the extension from .xlsx to .xlsm.
Sample.xlsx
0
 
wsturdevAuthor Commented:
capricorn1 -- any luck?
0
 
wsturdevAuthor Commented:
Calling capricorn1...  ;-)  I know you must be busy, but did you have any luck testing?
0
 
Rey Obrero (Capricorn1)Commented:
wsturdev,

using windows 7 A2007, i can't reproduce the error.
the file .xlsm is not locked by the process.
0
 
wsturdevAuthor Commented:
I have been able to use one of our allotted support incidents with MS and am awaiting a resolution.
I will let you know the outcome.
0
 
wsturdevAuthor Commented:
Simpler than I was expecting!!!

'First, establish the link to the Excel file, create the table
    'and import the data into the table
    Set tbl = db.CreateTableDef(g_strLinkedTableName, _
    dbAttachSavePWD, g_strExcelTabName, "Excel 12.0 Xml;HDR=NO;IMEX=1;ACCDB=YES;Database=" & g_strFullPathExcelFileKT & _
    ";Pwd=MyPswd")
    'Second, append the newly created table to Access TableDefs
    db.TableDefs.Append tbl
    'Break the link to the Excel file
    Set tbl = Nothing
    'The data is now in the local table and can be used as desired,
    'and the link to the Excel file is broken so you can now access
    'the Excel file outside of Access.

Open in new window

0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now