Question Saving Excel file from within access

[pwbrown]

[Solved]Saving Excel file from within access

Hello, I've spent hours trying to solve my problem and tried so many solutions and none have worked.

Basically from within access I'm opening an excel file, editing it, then closing and saving it.
It works until I get to closing and saving it, when I close it, it prompts me to save and if I click yes it goes to a SaveAs screen.

I would like it to save automatically when it gets closed so the user doesn't get prompted with anything. I also want it to override the current file opened and not a new save.

Edit: Should note I'm using access 2007

Here is some code I'm using:

Dim appxl As Object
Set appxl = CreateObject("Excel.Application")
appxl.Visible = False
 
appxl.Workbooks.Open "C:.....xlsx"
appxl.Sheets("Sheet1").Select
**edits to excel worksheet done here**
 
appxl.Workbooks.Close

Any help will be welcomed, thanks.

Kind regards,

Peter

 

[Trevor G]

Welcome to the Forum Peter what you can do is add the save command before the Close statement

appxl.DisplayAlerts=False
appxl.ActiveWorkbook.Save
appxl.Activeworbook.Close
appxl=DisplayAlerts=True
appxl = Nothing

 

[pwbrown]

Hey Trevor, appreciate your help.

appxl.ActiveWorkbook.Save
appxl.Activeworbook.Close

This worked a treat, I should of come here before wasted all my time looking around elsewhere. Was a really simple fix too.

Kind regards,
Peter

 

[Trevor G]

Happy to help Peter and thanks for letting me know it has helped you find a solution. Keep with the Forum many experienced folk that will look to assist and advice.

By the way sometimes if you record a macro in Excel then look at this code you can then add it into your Access VBA modules.

 

[boblarson]

Just an FYI - you can consolidate those two statements to one:

appxl.ActiveWorkbook.Close True

saves it when it closes.