Saving Excel from VBA without the Prompt

[BillDickenson]

I am opening an Excel sheet, writing some data, then closing the sheet. However, when I close the sheet, I am getting a "do you want to save...." prompt.

Any way to avoid that ? I just want it to save quietly and go away. This is the current version


xlApp.Save
xlWS.Close
Set xlWB = Nothing
xlApp.Quit
Set xlApp = Nothing

 

[earthandfire]

Ihaven't got Excel runnning at the moment, but from memory there are a number of optional parameters with the save command, one of which should do what you need.

 

[BillDickenson]

you would think so, but none that I can find. Lots of ones for SAVEAS but for saving it as the original, I just can't find it. If you can point me at the right place, I'd be very grateful. (this is driving me slightly crazed)

Thanks

 

[earthandfire]

I've just tested the following and it works without any prompts:

Private Sub Command4_Click()
Dim xl As Excel.Application
Dim wkb As Excel.Workbook
Dim sht As Excel.Worksheet

  Set xl = New Excel.Application
  Set wkb = xl.Workbooks.Open([b]PathAndNameOfWorkbook[/b])
  wkb.Activate
  Set sht = wkb.ActiveSheet
  sht.Range("A1") = "Test"
  wkb.Save
  Set sht = Nothing
  Set wkb = Nothing
  xl.Quit
  Set xl = Nothing
End Sub

An On Error Goto to force closing the objects would obviously make the above more robust.

Hope this helps

 

[earthandfire]

By the way, with an error trap you would also be able to make sure that if something went wrong, the instance of Excel would be closed.

 

[BillDickenson]

Ok, I found it (and feeling a bit stupid). It wasn't in Access at all, it was in Excel. There is an Excel macro that prompts "do you want to" no matter when/how you have saved.

So what I really need to do is start Excel without the macros, or find some way to disable the prompt.

2 days of research and it got past me. Thanks

 

[kodr]

For future reference

Workbooks("filename.xls").Close savechanges:=True

 

[BillDickenson]

darn, even better than what I was doing. Thanks