Close Excel Save Changes Box

[Spichael]

I have access transfer data from a table to a spreadhseet in excel. Then it opens the spreadsheet and performs some updates. Then I want to save it and close it. This process is executed from a Windows task scheduler so noone is physically is in the database and can check any buttons. Excel is currently asking me if I want to save my changes through a prompt box. I want to eliminate this prompt all together. Please see the code below and I apprecaite any advice you have. I have tried many things and have included the code below I thought would work which does not.

Private Sub Command1_GotFocus()
Dim i As Integer
Dim objExcel
Dim objWorkBook
Set objExcel = CreateObject("Excel.Application")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Daily Report", "C:\Documents and Settings\Desktop\DailyKPI_" & Format(Date, "MMDDYYYY") & ".xls", True
objExcel.workbooks.Open "C:\Documents and Settings\Desktop\DailyKPI_" & Format(Date, "MMDDYYYY") & ".xls"
i = 2
Do While objExcel.cells(i, 1) <> ""
If objExcel.cells(i, 22) = "DICL" Then
objExcel.cells(i, 18) = "LCL"
End If
If objExcel.cells(i, 6) = "Vendor" Then
objExcel.cells(i, 7) = "N/A"
End If
If objExcel.cells(i, 9) = "Atlanta, Ga" Then
objExcel.cells(i, 9) = objExcel.cells(i, 12)
End If
i = i + 1
Loop
******************************************************
Here is the code I thought I could use to accomplish the task but no luck
DoCmd.Close acSpreadsheetTypeExcel9, acSaveYes
******************************************************
objExcel.Quit
Set objExcel = Nothing
End Sub

Thank you for your help

 

[boblarson]

Set

objExcel.DisplayAlerts = False

and at the end

objExcel.DisplayAlerts = True

 

[Spichael]

Thanks for the message. It worked. I put it in the following location. I did not set it to true and the end because this database will not be monitored, only the results.

Private Sub Command1_GotFocus()
Dim i As Integer
Dim objExcel
Dim objWorkBook
Set objExcel = CreateObject("Excel.Application")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Daily Report", "C:\Documents and Settings\atl-michaelb\Desktop\DailyKPI_" & Format(Date, "MMDDYYYY") & ".xls", True
objExcel.workbooks.Open "C:\Documents and Settings\atl-michaelb\Desktop\DailyKPI_" & Format(Date, "MMDDYYYY") & ".xls"

*************************************************************
objExcel.DisplayAlerts = False ' Setting this to false so I can close database and spreadsheet without message boxes
*************************************************************
i = 2
Do While objExcel.cells(i, 1) <> ""
If objExcel.cells(i, 22) = "DICL" Then
objExcel.cells(i, 18) = "LCL"
End If
If objExcel.cells(i, 6) = "Vendor" Then
objExcel.cells(i, 7) = "N/A"
End If
If objExcel.cells(i, 9) = "Atlanta, Ga" Then
objExcel.cells(i, 9) = objExcel.cells(i, 12)
End If
i = i + 1
Loop
'DoCmd.Close acSpreadsheetTypeExcel9, acSaveYes Commenting out in case I need later
objExcel.Quit
Set objExcel = Nothing
End Sub