How do you disable save file dialog?

Using some sample VB Script code, I've come up with a script that will pull
some data and print out the resulting workbook. As odd as this as this may
or may not sound, I don't want or need the workbook to be saved for archival
purposes. The long term goal is to put this script on a schedule as we need
a report to print every 30 minutes or so. Problem: everytime the script
closes the workbook and excel, Excel prompts the user to save the file.
This is not acceptable if we want this to run in an automated. How can I
get this prompt to go away? I'm pretty sure Excel is generating the prompt.
Seems to me there is a setting in there somewhere to disable this prompt but
I can't find it. Any thoughts would be appreciated. I doubt anyone would
need to see the script but here it's pasted below. I just had a thought: I
wonder if I set the excel application to nothing without closing the
workbook and Excel if that would do what I want? Hmmm... This script is
run by using cscript from the command prompt. As an aside since I'm asking
questions: how is it possible to save the data in a Comma Seperated File
(CSV)? Thanks in advance for any suggestions!

Dim oExcel
Dim strFileName
strFileName = "test.xls"
Dim strNewName

'--Find the current date and time so this info can be appended to the file
name when
'--the excel sheet is saved
strNewName = Year(Now()) & "-" & Month(Now()) & "-" & Day(Now()) & " "
strNewName = strNewName & Hour(Now()) & "." & Minute(Now()) & " " &
strFileName

'--Start Excel and run it invisibly
Set oExcel = CreateObject("Excel.Application")
oExcel.Visible = False

'--Open a workbook preconfigured with ActiveFactory Workbook functions.
'--using "oExcel.Workbooks.Add [Path to file]"
'--Note: The act of opening the workbook will cause the functions to update
oExcel.Workbooks.Add "D:\script\test.xls"

'--Print the file
oExcel.Workbooks(1).Printout

'--Save the workbook in htm format (44), commented out for now
'----oExcel.Workbooks(1).SaveAs "D:\script\save\" & strNewName, 44

'--Close the workbook and Excel
oExcel.Workbooks.Close
oExcel.Quit
Set oExcel = Nothing


Chris Smith

Chris,

To prevent the save dialog:
Workbooks(1).Saved = True

To save the file as csv, use something like:
NameSave = "C:\MyFolder\MyFileName.xls"
Application.DisplayAlerts = False ' prevent messages
Workbooks(1).SaveAs _
Filename:= NameSave, FileFormat:=xlCSV, _
CreateBackup:=False
Application.DisplayAlerts = True ' reset messages
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"someone" <[email protected]> wrote in message
news:[email protected]...
> Using some sample VB Script code, I've come up with a script that will
> pull some data and print out the resulting workbook. As odd as this as
> this may or may not sound, I don't want or need the workbook to be saved
> for archival purposes. The long term goal is to put this script on a
> schedule as we need a report to print every 30 minutes or so. Problem:
> everytime the script closes the workbook and excel, Excel prompts the user
> to save the file. This is not acceptable if we want this to run in an
> automated. How can I get this prompt to go away? I'm pretty sure Excel
> is generating the prompt. Seems to me there is a setting in there
> somewhere to disable this prompt but I can't find it. Any thoughts would
> be appreciated. I doubt anyone would need to see the script but here it's
> pasted below. I just had a thought: I wonder if I set the excel
> application to nothing without closing the workbook and Excel if that
> would do what I want? Hmmm... This script is run by using cscript from
> the command prompt. As an aside since I'm asking questions: how is it
> possible to save the data in a Comma Seperated File (CSV)? Thanks in
> advance for any suggestions!
>
> Dim oExcel
> Dim strFileName
> strFileName = "test.xls"
> Dim strNewName
>
> '--Find the current date and time so this info can be appended to the file
> name when
> '--the excel sheet is saved
> strNewName = Year(Now()) & "-" & Month(Now()) & "-" & Day(Now()) & " "
> strNewName = strNewName & Hour(Now()) & "." & Minute(Now()) & " " &
> strFileName
>
> '--Start Excel and run it invisibly
> Set oExcel = CreateObject("Excel.Application")
> oExcel.Visible = False
>
> '--Open a workbook preconfigured with ActiveFactory Workbook functions.
> '--using "oExcel.Workbooks.Add [Path to file]"
> '--Note: The act of opening the workbook will cause the functions to
> update
> oExcel.Workbooks.Add "D:\script\test.xls"
>
> '--Print the file
> oExcel.Workbooks(1).Printout
>
> '--Save the workbook in htm format (44), commented out for now
> '----oExcel.Workbooks(1).SaveAs "D:\script\save\" & strNewName, 44
>
> '--Close the workbook and Excel
> oExcel.Workbooks.Close
> oExcel.Quit
> Set oExcel = Nothing
>
>
> Chris Smith
>

Thanks! I'll give it a shot.

Chris Smith

"Earl Kiosterud" <[email protected]> wrote in message
news:%[email protected]...
> Chris,
>
> To prevent the save dialog:
> Workbooks(1).Saved = True
>
> To save the file as csv, use something like:
> NameSave = "C:\MyFolder\MyFileName.xls"
> Application.DisplayAlerts = False ' prevent messages
> Workbooks(1).SaveAs _
> Filename:= NameSave, FileFormat:=xlCSV, _
> CreateBackup:=False
> Application.DisplayAlerts = True ' reset messages
> --
> Earl Kiosterud
> mvpearl omitthisword at verizon period net
> -------------------------------------------
>
> "someone" <[email protected]> wrote in message
> news:[email protected]...
>> Using some sample VB Script code, I've come up with a script that will
>> pull some data and print out the resulting workbook. As odd as this as
>> this may or may not sound, I don't want or need the workbook to be saved
>> for archival purposes. The long term goal is to put this script on a
>> schedule as we need a report to print every 30 minutes or so. Problem:
>> everytime the script closes the workbook and excel, Excel prompts the
>> user to save the file. This is not acceptable if we want this to run in
>> an automated. How can I get this prompt to go away? I'm pretty sure
>> Excel is generating the prompt. Seems to me there is a setting in there
>> somewhere to disable this prompt but I can't find it. Any thoughts would
>> be appreciated. I doubt anyone would need to see the script but here
>> it's pasted below. I just had a thought: I wonder if I set the excel
>> application to nothing without closing the workbook and Excel if that
>> would do what I want? Hmmm... This script is run by using cscript from
>> the command prompt. As an aside since I'm asking questions: how is it
>> possible to save the data in a Comma Seperated File (CSV)? Thanks in
>> advance for any suggestions!
>>
>> Dim oExcel
>> Dim strFileName
>> strFileName = "test.xls"
>> Dim strNewName
>>
>> '--Find the current date and time so this info can be appended to the
>> file name when
>> '--the excel sheet is saved
>> strNewName = Year(Now()) & "-" & Month(Now()) & "-" & Day(Now()) & " "
>> strNewName = strNewName & Hour(Now()) & "." & Minute(Now()) & " " &
>> strFileName
>>
>> '--Start Excel and run it invisibly
>> Set oExcel = CreateObject("Excel.Application")
>> oExcel.Visible = False
>>
>> '--Open a workbook preconfigured with ActiveFactory Workbook functions.
>> '--using "oExcel.Workbooks.Add [Path to file]"
>> '--Note: The act of opening the workbook will cause the functions to
>> update
>> oExcel.Workbooks.Add "D:\script\test.xls"
>>
>> '--Print the file
>> oExcel.Workbooks(1).Printout
>>
>> '--Save the workbook in htm format (44), commented out for now
>> '----oExcel.Workbooks(1).SaveAs "D:\script\save\" & strNewName, 44
>>
>> '--Close the workbook and Excel
>> oExcel.Workbooks.Close
>> oExcel.Quit
>> Set oExcel = Nothing
>>
>>
>> Chris Smith
>>
>
>