Excel Prompts From Access VBA?

[DanBurn]

I am manipulating an excel spreadsheet from my access database.

I am having problems stopping excel prompt when I delete data in the spread sheet.

I have setwarnings to false in access but that doesn't seem to effect Excel and I have also used

[COLOR=black][FONT=Verdana]application.displayalerts = [/FONT][/COLOR][COLOR=blue][FONT=Verdana]False[/FONT][/COLOR]

But it doesn't seem to pass it to excel.
I have got excel object library reference activated in access.

So how do I stop prompts in excel from Access?

(I'm using Access and excel 2002)

Cheers!

 

[boblarson]

How are you deleting data? What code are you trying to use?

 

[chergh]

You need to prefix your 'application' with 'excel' like:

excel.application.displayalerts = false

 

[DanBurn]

Here is the full code

Private Sub Command44_Click()
Dim objExcel As Object
 
DoCmd.SetWarnings False
Set objExcel = CreateObject("Excel.Application")
 
objExcel.Workbooks.Open FileName:="N:\Highways\AIU\General Data\Safety Camera Tables\REPORTS\SPEED MONITORING\2008 Data\C2DataDump.xls" _
, UpdateLinks:=3
objExcel.Visible = True
 
Cells.Find(What:="00:00-24:00", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
 
Rows(ActiveCell.Row).Select
Selection.Cut
Sheets.Add
    Sheets("Sheet1").Select
    ActiveSheet.Paste
    Sheets("pvrreportb").Select
    Cells.Select
    Selection.ClearContents
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
 
ActiveWindow.DisplayGridlines = True
    Sheets("Sheet1").Select
    Rows("1:1").Select
    Selection.Copy
    Sheets("pvrreportb").Select
    Range("A1").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
 
    ActiveWindow.SelectedSheets.Delete
 
    Sheets("pvrreportb").Select
    ActiveWorkbook.Save
DoCmd.SetWarnings True
End Sub

 

[DanBurn]

Cheers chergh

Registered User

That worked a treat