When I run the code without exiting the database I receive an error

[AThom10731]

An error is received when I run the following code if I do not exit MS Access after running it. The code runs a query, exports results to an Excel file and formats the worksheets. If I exit Access each time it runs fine. I have a command button on a form that executes the code.

Any thoughts? Thanks for your assistance.
-----------------------------------------------------------------
Private Sub Command1_Click()

'Created a new Form called frm_CAPSummaryForm1
'Set it's RecoredSource to:
'SELECT [F_Site] FROM SEC_FindingRecords GROUP BY [F_Site];
'Add the field [F_Site] to the Detail section.
'Add a Command Button (Command1) to the form with the following code:
'
'If C:\ALL_CAPSpreadsheet.xls exists then delete it
If Dir("C:\ALL_CAPSpreadsheet.xls") <> "" Then
' the file exists, returns "" (empty string) if the file doesn't exist.
' Delete a file :
On Error GoTo ErrorHandler
Kill "C:\ALL_CAPSpreadsheet.xls"
End If
Me.Recordset.MoveFirst
Do
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "QRY_CAP_SummaryAllDisc", "C:\ALL_CAPSpreadsheet.xls", True, Me.F_Site
Me.Recordset.MoveNext
Loop Until Me.Recordset.EOF
Call format_AllDiscworksheets
Exit Sub

ErrorHandler:
MsgBox "Sorry, another process is using ALL_CAPSpreadsheet.xls. Please log off or kill the excel process from Task Manager."
Exit Sub
End Sub
Function format_AllDiscworksheets()
Dim filePath As String
Dim TabFlag As String

Dim EX1 As Excel.Application
Dim EX1Book As Excel.Workbook
Dim EX1Sheet As Excel.Worksheet
Dim StatusColor As Integer
Dim StatusIndex As Integer

filePath = "C:\ALL_CAPSpreadsheet.xls"
Set EX1 = CreateObject("Excel.Application")
On Error GoTo ErrorHandling
Set EX1Book = EX1.Workbooks.Open(filePath)
MsgBox (" Formatting spreadsheet: " & filePath & "... Please wait on the hour glass to disappear before exiting. ")

EX1.Visible = False

For Each EX1Sheet In ActiveWorkbook.Worksheets
'place code between the For and Next
'for what you would like to do to
'each sheet

'All headers bold and centered with silver fill

EX1Sheet.Range("A1:O1").Font.Bold = True
EX1Sheet.Range("A1:O1").HorizontalAlignment = Excel.xlLeft
EX1Sheet.Range("A1:O1").Interior.Color = RGB(192, 192, 192)
EX1Sheet.Range("A1:O200").Font.Size = 9
'All other data is wrap text and top left
EX1Sheet.Range("A2:O200").HorizontalAlignment = xlLeft
EX1Sheet.Range("A2:O200").VerticalAlignment = xlTop
EX1Sheet.Range("A2:O200").WrapText = True
EX1Sheet.Range("A2:O200").Orientation = 0
EX1Sheet.Range("A2:O200").AddIndent = False
EX1Sheet.Range("A2:O200").ShrinkToFit = False
EX1Sheet.Range("A2:O200").ReadingOrder = xlContext
EX1Sheet.Range("A2:O200").MergeCells = False
EX1Sheet.Columns("E:G").ColumnWidth = 25
EX1Sheet.Columns("A:B").ColumnWidth = 17
EX1Sheet.Columns("D").ColumnWidth = 17
EX1Sheet.Columns("H").ColumnWidth = 17
EX1Sheet.Columns("I:K").ColumnWidth = 11
EX1Sheet.Columns("L:M").ColumnWidth = 14
EX1Sheet.Columns("N").ColumnWidth = 25
EX1Sheet.Columns("B:O").AutoFilter

'center C, I, J, K, O
EX1Sheet.Columns("C").HorizontalAlignment = Excel.xlCenter
EX1Sheet.Columns("I:K").HorizontalAlignment = Excel.xlCenter
EX1Sheet.Columns("O").HorizontalAlignment = Excel.xlCenter


'Conditionally Format Status by filling the cell with the applicable status color


For StatusIndex = 2 To 200
'Status needs to be green if a finish date is present

If IsDate(EX1Sheet.Range("K" & StatusIndex).Value) Then
EX1Sheet.Range("L" & StatusIndex).Interior.Color = RGB(50, 205, 50)
EX1Sheet.Range("L" & StatusIndex).Value = "Green"
Else
Select Case EX1Sheet.Range("L" & StatusIndex).Value
Case "Green"
EX1Sheet.Range("L" & StatusIndex).Interior.Color = RGB(50, 205, 50)
Case "Yellow"
EX1Sheet.Range("L" & StatusIndex).Interior.Color = RGB(255, 255, 0)
Case "Red"
EX1Sheet.Range("L" & StatusIndex).Interior.Color = RGB(255, 0, 0)
End Select

End If

Next StatusIndex

For StatusIndex = 2 To 200
'SI_DatabaseRating needs to dumped with no changes but background

Select Case EX1Sheet.Range("M" & StatusIndex).Value
Case "Green"
EX1Sheet.Range("M" & StatusIndex).Interior.Color = RGB(50, 205, 50)
Case "Yellow"
EX1Sheet.Range("M" & StatusIndex).Interior.Color = RGB(255, 255, 0)
Case "Red"
EX1Sheet.Range("M" & StatusIndex).Interior.Color = RGB(255, 0, 0)
End Select

Next StatusIndex

TabFlag = "Green"
EX1Sheet.Tab.ColorIndex = 10

For StatusIndex = 2 To 200
'Tab color needs to be red if at least one Red in column L
'If no reds found then if a yellow is found then the value is yellow
'If no reds are found and no yellows are found then the value of the tab stays green

Select Case EX1Sheet.Range("L" & StatusIndex).Value
Case "Red"
TabFlag = "Red"
EX1Sheet.Tab.ColorIndex = 3
Case "Yellow"
If TabFlag <> "Red" Then
TabFlag = "Yellow"
EX1Sheet.Tab.ColorIndex = 6
End If
End Select

Next StatusIndex


Next

EX1.DisplayAlerts = False
'X1Book.Save
'X1Book.SaveAs (filePath & Format(Date, "mmdd") & ".xls")
filePath = "C:\ALL_CAPSpreadsheet" & Format(Now, "yyyymmdd_hhmmss") & ".xls"
MsgBox "Your output file is:: " & filePath & "... Exit the database before opening Excel"


EX1Book.Save
EX1Book.SaveAs filePath
EX1Book.Close True
Set EX1Sheet = Nothing
Set EX1Book = Nothing
'Excel.Application.Quit
EX1.Quit
Set EX1 = Nothing

On Error GoTo ErrorHandling
Kill "C:\ALL_CAPSpreadsheet.xls"

Exit Function

ErrorHandling:

MsgBox "Sorry, another process is using ALL_CAPSpreadsheet.xls. Please log off or kill the excel process from Task Manager."
Exit Function

End Function

 

[AThom10731]

Sorry, if I was not clear, if I run the code a second time without exiting Excel, I recieve an error.

Runtime error 90 permission denied. It bombs when it attempts to kill the excel file.

 

[Andrzejek]

And that's what you have in your code:
you run it a second time, you have the Excel file open, you cannot kill it, so you get the message:
"Sorry, another process is using ALL_CAPSpreadsheet.xls. Please log off or kill the excel process from Task Manager."
So what you should do is exit this Excel file (ALL_CAPSpreadsheet.xls) and run the code again by clicking the Command1 command button.


Have fun.

---- Andy

 

[AThom10731]

Thanks, Andy, but the following generates a permission denied error message.

On Error GoTo ErrorHandler
Kill "C:\ALL_CAPSpreadsheet.xls"

I would like to have the ErrorHandler take control and display the message instead of the end user receiving the Permission Denied message.


I have tried placing it before the
If Dir("C:\ALL_CAPSpreadsheet.xls") <> "" Then

statement and also have runtime errors instead of receiving the error message I would like the user to receive.

 

[Andrzejek]

And that what should happen: error occurs, you have a line "On Error GoTo ErrorHandler" so it should go to ErrorHandler: line and display a message and bail out.

In your VBA editor, check: Tools - Options... General Tab, Error Trapping frame, and see which option you have selected
O Break on All Errors
O Break in Class Module
O Break on Unhandled Errors)

You may have first one selected, select teh second one.

Have fun.

---- Andy

 

[AThom10731]

Thank you, Andy. I am getting my error message now vs the VBA error message by changing the break options.

Ann

 

[Andrzejek]

Glad to help.

The only problem now will be: what option selected your users have in VBA editor...?

Have fun.

---- Andy

 

[AThom10731]

Good point, I will have a user guide/document to roll out to users to include step-by-step procedures to set their options.
Thanks again.

 

[PHV]

Anyway, change this:
For Each EX1Sheet In ActiveWorkbook.Worksheets
with this:
For Each EX1Sheet In EX1Book.Worksheets

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AThom10731]

OK, thank you both. I probably need to post a new thread but...

My user community has begun to upgrade their systems from XP to Win 7. Any ideas on why users with Windows 7 are unable to write to C:\ from my Access VBA code even with local admin rights and full control permissions set?

 

[Andrzejek]

Are you saying that you write straight to the root on C:\ drive?
Or is it to "C:\SomeFolder\MoreInfo\xyz.abc" file?

Have fun.

---- Andy

 

[AThom10731]

With XP my code writes to C:\ without a problem.

When I attempt to write to this folder using Win 7 it fails. I have attempted to capture environment variable info and point to user's temp folder as a test and it does not work either. For example:

C:\Users\Ann\AppData\Local\Temp
for the user Ann.