Import Excel to Access (Excel locked for editing) 1

[DomFino]

Hi All,
I am having a problem with an Excel to Access import (2003). I have a form with two command buttons. The first button when clicked opens the Excel spreadsheet for viewing before it is sent to an Access table. The second command button, deletes all the records in the Access table and imports all the recoded for the spreadsheet.

The issue is that after I run the routine once. The second time I run it the Excel spreadsheet errors with “spreadsheet is locked for editing, open read only.” At this point, the spreadsheet is not open and Excel is not open. My question - is there some way to make sure the Excel spreadsheet is released so the error does not occur? I looked at the KILL option in Access but that would delete the Excel spreadsheet, which I do not want to do.

As always, maybe I am going about this all wrong. Any suggestions will be appreciated.

Thanks
Dom

Private Sub cmdOpenExcel_Click()
On Error GoTo Err_cmdOpenExcel_Click
Dim retVal As Variant

retVal = Shell("Excel " & Chr(34) & "\\bigguy\users\dom_f\Access\TestWebPAS2\TESTwebpas_download1.xls" & Chr(34), vbMaximizedFocus)
Exit_cmdOpenExcel_Click:
    Exit Sub

Err_cmdOpenExcel_Click:
    MsgBox Err.Description
    Resume Exit_cmdOpenExcel_Click

End Sub

Private Sub cmdImportRecords_Click()
On Error GoTo Err_cmdImportRecords_Click

Dim strDelete As String

strDelete = "DELETE WebPAS.*, * FROM WebPAS"

DoCmd.RunSQL strDelete

Call ImportExcelToAccess

Exit_cmdImportRecords_Click:
    Exit Sub
    
Err_cmdImportRecords_Click:
    MsgBox Err.Description
    Resume Exit_cmdImportRecords_Click
    
End Sub

Function ImportExcelToAccess()
On Error GoTo ImportExcelToAccess_Err

DoCmd.TransferSpreadsheet acImport, 8, "WebPAS", "\\bigguy\users\dom_f\Access\TestWebPAS2\TESTwebpas_download1.xls", True, ""


ImportExcelToAccess_Exit:
    Exit Function

ImportExcelToAccess_Err:
    MsgBox Error$
    Resume ImportExcelToAccess_Exit

End Function

 

[CautionMP]

DomFino,
I think the easiest solution is to eliminate the step where you open the workbook with [tt]Shell()[/tt], does the user need to see the workbook before import?

If so you might look at a Global Excel object variable, that way you can open the workbook and then close it from code before the import occurs.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[DomFino]

CautionMP,
Thanks for the reply. Yes, the user needs to see the spreadsheet before export and import to Access. I am not familiar with the Global Excel object variable; however, I am going to research that now.
Thanks again for the information.
Dom

 

[DomFino]

All,
I found the offending code and it had nothing to do with the code I posted earlier. The code causing the problem is on the ON OPEN event of my main form. What I need is the spreadsheet date and time so I can display it on the main form. That way the users know the data is accurate as of a specific date and time.

Here is the code causing the problem. I recently added the two lines:

Set objExcel = Nothing
Set objWorkbook = Nothing

But it did not help. The spreadsheet is acting as if it is still open. Does anyone know how to obtain the spreadseet property CREATION DATE and then release the spreadsheet?

Private Sub Form_Open(Cancel As Integer)
    Dim szCreationDate As String

    Set objExcel = CreateObject("Excel.Application")
        objExcel.Visible = False
    Set objWorkbook = objExcel.Workbooks.Open("\\bigguy\users\dom_f\Access\TestWebPAS2\TESTwebpas_download1.xls")

        szCreationDate = objWorkbook.BuiltinDocumentProperties("Creation Date")

    Set objExcel = Nothing
    Set objWorkbook = Nothing

    Me.txtCreationDate = szCreationDate

    End Sub

 

[PHV]

Private Sub Form_Open(Cancel As Integer)
Dim szCreationDate As String
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Open("\\bigguy\users\dom_f\Access\TestWebPAS2\TESTwebpas_download1.xls")
szCreationDate = objWorkbook.BuiltinDocumentProperties("Creation Date")
[!]objWorkbook.Close False
objExcel.Quit[/!]
Set objExcel = Nothing
Set objWorkbook = Nothing
Me!txtCreationDate = szCreationDate
End Sub

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

 

[DomFino]

PHV,

Hi PHV, you have bailed me out once again. Your suggested code did the trick. Thank you so much for the reply and for being such a valuable resource.
Dom