Hi,

I am trying to refresh an excel spreadsheet from sqlserver.
1. I have setup the excel spreadsheet with external data source to the sqlserver.
2. I have an active x task as follows
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()

dim xlapp
dim xlbooks
dim xlsheet
dim SheetName
set xlapp = createobject("Excel.Application")
set xlbooks = getobject("\\marketing_list$\prathi.xls")
SheetName = "sheet1"
set xlsheet = xlbooks.worksheets(Sheetname)
xlbooks.RefreshAll
xlbooks.Save
xlapp.quit

Main = DTSTaskExecResult_Success
End Function

3. I get this message, "This action will cancel a pending refresh data command continue ?" (It is an excel message), when I press continue, the active x script continues. I don't want the excel message to show . What do I need to do so that the excel message does not show up!!

I would really appreciate any help with this.

Thanks Prathi

What about

Application.DisplayAlerts=False

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"prathi" <anonymous (AT) discussions (DOT) microsoft.com> wrote

active x script continues. I don't want the excel message to show . What
do I need to do so that the excel message does not show up!!

I tried it but now I have a runtime error. Where do I put
Application.DisplayAlerts=False ? Do I have to put it after

set xlapp = createobject("Excel.Application").

Thanks Prathi

----- Allan Mitchell wrote: -----

What about

Application.DisplayAlerts=False

--

----------------------------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org



"prathi" <anonymous (AT) discussions (DOT) microsoft.com> wrote

active x script continues. I don't want the excel message to show . What
do I need to do so that the excel message does not show up!!

Yes

xlapp.DisplayAlerts = False

--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"prathi" <anonymous (AT) discussions (DOT) microsoft.com> wrote

I am able to refresh the pivot tables on sheet1 from sqlserver. When I open the excel file, it is hidden. I have to go to windows, unhide and select workbook. How can I unhide the workbook from the activex script ? This is the activex script :

Function Main()

dim xlapp
dim xlbooks
dim xlsheet
dim SheetName
set xlapp = createobject("Excel.Application")
set xlbooks = getobject("\\ausdsps301\pub_trans$\marketing_liist \cra_detail.xls")
SheetName = "sheet1"
set xlsheet = xlbooks.worksheets(Sheetname)
xlbooks.RefreshAll
xlbooks.Save
xlapp.quit

Main = DTSTaskExecResult_Success
End Function