Excel Automation

[Guest]

I have the following module...

Function XLUpdate()
Dim XL As Object
Dim SourceFile As String
Set XL = CreateObject("Excel.Application")
'Refresh EXCEL Pivot Report
SourceFile =
"c:\Databases\Databases\Pending_SRO_Types\Report\Pending_SRO_Types.xls" '
this is a sample excel filename
XL.Workbooks.Open SourceFile
XL.Run "Refresh" ' this is an example of an excel macro name
XL.Workbooks.Close
Set XL = Nothing
End Function

The goal is to open an excel file, refresh the pivot tables, save and close.
The problem is that the module stops because excel prompts if you want to
save the excel file.
What I need is to save and replace the existing excel file without prompting.
Can this be done?

 

[Nick via AccessMonster.com]

Function XLUpdate()
Dim XL As Object
Dim SourceFile As String
Set XL = CreateObject("Excel.Application")
'Refresh EXCEL Pivot Report
SourceFile =
"c:\Databases\Databases\Pending_SRO_Types\Report\Pending_SRO_Types.xls" '
this is a sample excel filename
XL.Workbooks.Open SourceFile
XL.Run "Refresh" ' this is an example of an excel macro name
XL.Workbooks.Close
Set XL = Nothing
End Function

Michael,

Before your 'XL.Workbooks.Close' statement, insert

XL.Workbooks.Save

That should take care of it; I use that with early binding and it works fine,
I think it will still work the same with late binding. If there are other
warnings showing up that you don't want to display, I think you can add in

XL.DisplayAlerts False 'Something to this effect

-Nick