How to stop Excel Macro from save prompt pop up

[HarryStamper]

I have an excel macro that my access app calls. All it does is delete column A from an attachment as a daily cleanup process. After it runs, it prompts with "save, no, cancel" prompt. If I am out, nothing else runs because the prompt. How can I handle this? (I go on vacation in 2 wks!!!!!!!!) Thanks


Sub Macro1()
'
'
' Macro1 Macro
' Macro recorded 6/7/2006 by erodman
'
'
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
End Sub

 

[xlbo]

Columns("A:A").Delete Shift:=xlToLeft
activeworkbook.saved = true


You should probably look into ways of using something other than "activeworkbook" but as you have not specified how you are accessing excel from Access, I cannot really suggest anything

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[HarryStamper]

Thanks for the reply. I am running the code via an Access Macro which calls the following function via the runcode argument.

Function ClearTL()
' Macro1 Macro
' Macro recorded 6/7/2006
DoCmd.SetWarnings False

Dim strPath As String
Dim xlApp As Object
Dim xlSheet As Object
strPath = "D:\Documents and Settings\xxx\My Documents\TL.XLS"
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(strPath).Sheets(1)
With xlApp
.Run "Macro1"
End With

xlApp.Quit
Set xlApp = Nothing
Set xlSheet = Nothing

DoCmd.SetWarnings True
End Function

 

[HarryStamper]

Beautiful, it worked perfectly...The macro runs, deletes the column and saves it without operator intervention.
I can now go on vacation!!!!! Thanks

 

[firefytr]

I would not change the property manually, rather complete a save myself and let the file change it's own property. I don't think it's a good idea to manipulate the .Saved property of workbooks and stay away from it as much as possible. Instead, why not just save?

Function ClearTL()
' Macro1 Macro
' Macro recorded 6/7/2006
DoCmd.SetWarnings False

Dim strPath As String
Dim xlApp As Object
Dim xlBook as object, xlSheet As Object
strPath = "D:\Documents and Settings\xxx\My Documents\TL.XLS"
Set xlApp = CreateObject("Excel.Application")
xlApp.displayalerts = False
Set xlBook = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlBook.Sheets(1)
xlSheet.Range("A:A").Delete
xlBook.Close savechanges:=true
xlApp.displayalerts = True

xlApp.Quit
Set xlApp = Nothing
Set xlSheet = Nothing

DoCmd.SetWarnings True
End Function

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci