Full Version: Excel locks up
UtterAccess Forums > Microsoft® Access > Access Forms
george830
I can create a spreadsheet in VBA on a form which is viewed by the user. Once the user is finished with the spreadsheet, they either close or exit out of Excel and the spreadsheet is gone.
When the form is run again, a command is given in vba to "kill" any previous copies and a new spreadsheet is created. However, it appears that after the first run, which is successful, the spreadsheet is locked up and even though the Excel is no longer present the second spreadsheet cannot be deleted. The only way that I can make it go away is to restart the computer and then delete the file manually.
Has anyone had this experience before and how was it successfully deleted through VBA code?
tia.
freakazeud
Hi,
are you sure excel is not present anymore when the user closes the application? Go through the process and use the Windows Task Manager to ensure that no further Excel instances are running and tying up the file.
You might also want to give a little more information on what code you are using.
HTH
Good luck
george830
I checked the Task Manager and Excel is not there.
tried to do a delete on the spreadsheet and it came back with a error message stating that file was locked up by Excel and it would have to be closed in order to delete the file.
The code that I have been using is as follows: (I hope that this is not too much)
'====================================================================
Private Sub Calendar1_Click()
Dim v1 As Date, v2 As String
v1 = Calendar1
On Error Resume Next
v2 = CurrentProject.Path
Kill v2 & "\tmptbldates.xls"
DoCmd.DeleteObject acTable, "temptbldates"
Me.Text36 = v1
Me.Calendar5.Visible = True
Me.Text38.Visible = True
Me.Calendar5.SetFocus
Me.Calendar1.Visible = False
End Sub
'====================================================================
Private Sub Calendar5_Click()
Dim v1, v2, v3, v4 As String, v5 As Long
'On Error Resume Next
v1 = CurrentProject.Path
v2 = Me.Text36
v3 = Calendar5
Me.Text38 = v3
If DateDiff("d", Text36, v3) <= 0 Then
Me.Text36 = ""
Me.Text38 = ""
Me.Calendar1.Visible = True
Me.Calendar1.SetFocus
Me.Calendar5.Visible = False
Me.Text38.Visible = False
MsgBox "Please Reenter Previous Date", vbOKCancel, "Sorry! Wrong Date."
Exit Sub
Else
Me.Text38.SetFocus
Me.Calendar5.Visible = False
v4 = "SELECT ND.VESSEL, " _
& "ND.[STEAMSHIP COMPANY], " _
& "ND.ETA, " _
& "ND.INVITATION, " _
& "COMMOD.COMMODITY, " _
& "ND.CONTRACT, " _
& "ND.ND, " _
& "Shipper.[SHIPPER NAME], " _
& "ND.UNITS, " _
& "ND.[METRIC TONS], " _
& "ND.NLT, " _
& "Sum(CAR.UNITS) AS [UNITS SHIPPED], " _
& "ND.[PORT OF EXPORT], " _
& "ND.[VOLUNTEER AGENCY], " _
& "ND.[BOOKING AGENT], " _
& "ND.COUNTRY, " _
& "ND.[DISCHARGE PORT] " _
& "into tmptbldates " _
v4 = v4 & "FROM Shipper " _
& "INNER JOIN (COMMOD RIGHT JOIN " _
& "(ND INNER JOIN CAR " _
& "ON (ND.ND = CAR.ND) " _
& "AND (ND.INVITATION = CAR.INVITATION)) " _
& "ON COMMOD.[COMMODITY CODE] = ND.[COMMODITY CODE]) " _
& "ON Shipper.[SHIPPER CODE] = ND.[SHIPPER CODE] " _
v4 = v4 & "GROUP BY " _
& "ND.VESSEL, " _
& "ND.[STEAMSHIP COMPANY], " _
& "ND.ETA, " _
& "ND.INVITATION, " _
& "COMMOD.COMMODITY, " _
& "ND.CONTRACT, " _
& "ND.ND, " _
& "Shipper.[SHIPPER NAME], " _
& "ND.UNITS, " _
& "ND.[METRIC TONS], " _
& "ND.NLT, " _
& "ND.[PORT OF EXPORT], " _
& "ND.[VOLUNTEER AGENCY], " _
& "ND.[BOOKING AGENT], " _
& "ND.COUNTRY, " _
& "ND.[DISCHARGE PORT] " _
& "HAVING (((ND.ETA) Between #" & v2 & "# And #" & v3 & "#)) " _
& "ORDER BY ND.ETA, ND.ND;"
DoCmd.RunSQL v4
End If
'v1 = CurrentProject.Path
DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, _
"tmptbldates", _
v1 & "\tmptbldates.xls", False
DoCmd.DeleteObject acTable, "tmptbldates"
'----FORMAT SPREADSHEET
'----DECLARE VARIABLES FOR SPREADSHEET
Dim xlApp As Excel.Application
Dim xlWb As Excel.Workbook
Dim xlsht As Excel.Worksheet
'----DESCRIBE SPREADSHEET
Set xlApp = New Excel.Application
Set xlWb = xlApp.Workbooks.Open(v1 & "\tmptbldates")
Set xlsht = xlWb.Worksheets(1)
xlsht.Cells.Range("A2").Select
'----DETERMINING THE BOTTOM ROW OF SPREADSHEET
v5 = xlsht.Range("A" & Rows.Count).End(xlup).Row
v5 = v5 + 2
'----INSERTING FORMULAS AT BOTTOM OF SPREADSHEET
Range("I" & v5).Select
ActiveCell.Formula = "=SUM(R[-" & v5 - 2 & "]C:R[-2]C)"
Range("J" & v5).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & v5 - 2 & "]C:R[-2]C)"
Range("L" & v5).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-" & v5 - 2 & "]C:R[-2]C)"
Range("H" & v5).Select
ActiveCell = "TOTALS:"
'----FORMAT SPREADSHEET
With xlsht
'----FREEZE PANES ON SPREADSHEET
xlsht.Select
.Rows("2:2").Select
xlApp.ActiveWindow.FreezePanes = True
'----Make entire worksheet Arial 7.5 pt font
.Range("A:Q").Font.Name = "Arial"
.Range("A:Q").Font.Size = 7.5
'----EXPAND TOP ROW
.Rows("1:1").RowHeight = 30
.Rows("1:1").WrapText = True
.Rows(1).HorizontalAlignment = -4108 'CENTER HEADER ROW
'----FORMAT COLUMNS
.Columns("A:A").ColumnWidth = 17 'VESSEL
.Columns("B:B").ColumnWidth = 9 'STEAMSHIP COMPANY
.Columns("C:C").ColumnWidth = 8 'VESSEL ETA
.Columns("D:D").ColumnWidth = 4 'INVITATION
.Columns("E:E").ColumnWidth = 13 'COMMODITY
.Columns("F:F").ColumnWidth = 8 'CONTRACT
.Columns("G:G").ColumnWidth = 10 'ND
.Columns("H:H").ColumnWidth = 25 'COMMODITY SUPPLIER
.Columns("I:I").ColumnWidth = 7 'ND UNITS
.Columns("I:I").NumberFormat = "#,000"
.Columns("J:J").ColumnWidth = 6 'METRIC TONS
.Columns("J:J").NumberFormat = "#,000"
.Columns("K:K").ColumnWidth = 6 'NLT
.Columns("L:L").ColumnWidth = 7 'UNITS SHIPPED
.Columns("L:L").NumberFormat = "#,000"
.Columns("M:M").ColumnWidth = 5 'LOAD PORT
.Columns("N:N").ColumnWidth = 7 'COOPERATING SPONSOR
.Columns("O:O").ColumnWidth = 22 'FREIGHT FORWARDER
.Columns("P:P").ColumnWidth = 13 'DESTINATION COUNTRY
.Columns("Q:Q").ColumnWidth = 12 'DISCHARGE PORT
'----SAVE AND CLOSE SPREADSHEET
xlApp.ActiveWorkbook.Save
'xlApp.ActiveWorkbook.Close
'----SHOW SPREADSHEET
xlApp.Visible = True
End With
End Sub
'===END OF CODE======================================================
freakazeud
So you are leaving a reference open at the end of the code.
You should clear those e.g.:
lWb.Close SaveChanges:=True
xlApp.Quit
Set xlApp = Nothing
Set xlWb = Nothing
Set xlsht = Nothing
Try to run that at the end of your code and then try to manually delete the file to see if that makes any changes.
HTH
Good luck
george830
I forgot to tell you that I am using split monitors and I was able to look at the task manager and Excel was still running there in the processes screen. When I pressed the Remove button, I was able to delete the file. However, I am going to run your suggestions above and will let you know. Thanks.
freakazeud
Yes...so there was still an instance referenced in the Task Manager for it which will prevent the deleting.
HTH
Good luck
george830
The mystery deepens.
When the user clicks on a button on the form, it creates and displays the spreadsheet for the user to view.
The viewer has three options with the spreadsheet:
1. To close the spreadsheet
2. To print the spreadsheet
or
3. To save the spreadsheet
When the user is all done with it, he can either close or exit from it and return back to the Access program.
Otried your suggestions but this would close up the spreadsheet and the user could not view it.
I was able to find that Excel is still open by looking at the tab Processes. When I click on "End Process" and run it again from Access as I had done before, I get a message stating that Excel had the spreadsheet open. By removing Excel from the Processes list, the spreadsheet could be deleted (Kill) but on the second attempt, I get an error message stating the remote server was not found.
I go back into the "Task Manager" do the above routine and it works perfectly. In order for it to work, I must go through a 3 cycle manual process to get the spreadshee to respond correctly.
freakazeud
You still need to clear the references even if you leave the application open. The code was just a sample to check if when running it from within Access the Excel instances actually disappear.
HTH
Good luck
george830
Just thought that I would let you know that you were a lot of help and I was able to finally get the spreadsheet to appear and format in the manner that I requested.
Thanks for your help
freakazeud
Glad you got it sorted out.
Good luck on future projects!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.