How do you disable save file dialog?

[someone]

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

 

[Earl Kiosterud]

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

 

[someone]

Thanks! I'll give it a shot.

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

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