Excel Automation

M

Michael

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?
 
N

Nick via AccessMonster.com

Michael said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top