DoCmd.SetWarnings problem

[tbg130]

Hi,

I am trying to delete a worksheet if it already exists, but the warnings will not shut off. Below is the code that I have used:

For Each Worksheet In objBook.Worksheets
With Worksheet
If .Name = strCountryID Then
DoCmd.SetWarnings False
.Delete
DoCmd.SetWarnings True
End If
End With
Next

I would also like to be able to do the same thing if a file already exists; but can't get that to work either.

Suggestions?

Thanks,

Tyler

 

[RoyVidar]

The setwarnings applies to Access, but when automating Excel, you'll need the Excel method, I think it is DisplayAlerts, issued on the Excel application object:

[tt] For Each Worksheet In objBook.Worksheets
With Worksheet
If .Name = strCountryID Then
'DoCmd.SetWarnings False
objXL.DisplayAlerts=false
.Delete
objXL.DisplayAlerts=true
'DoCmd.SetWarnings True
End If
End With
Next[/tt]

With a file, you'll need to check for the existence of it, which might differ between the different methods, look into the .FileExists property of the FileSystemObject

Roy-Vidar

 

[tbg130]

Perfect! Works great and now I can run through a large data set dozens of times with a loop and not get questionned every time it writes a new file!

Thanks!