Close without saving (Excel 97/SR2)

Topic

New Reply

I am looking for process to open multiple files in mulitple directories and print one worksheet from each of the files. I have written the following module to open the excel files update a date field, then print the appropriate worksheet. The module then closes the file and moves on to the next file. Here is where I need help. When I close the file, a dialog box appears asking if I want to save the changes, Yes-No-Cancel. I will always be anwering NO. Is there something I could add to my code to automatically answer the dialog box with ‘NO’?

BTW, don’t know if it makes any difference, but this code is being ran from within Access 97. If this belongs in the Access forum, let me know and I will repost.

Option Compare Database
Option Explicit
Dim mstrShift(1 To 3) As String
Dim mstrDFile(1 To 2) As String

Sub AttendanceUpdates()
    Dim XLApp As New Excel.Application
    Dim strFilePath As String, strShift As String, y As Integer
    Dim x As Integer, strTTLPath As String, strFile As String
    Call ShiftData
    Call DayFiles
    x = 2
    For y = 1 To 2
        strFilePath = "Srvnt01SharedAdminEmployee Files"
        strShift = mstrShift(x)
        strFile = mstrDFile(y)
        strTTLPath = strFilePath & strShift & strFile
        XLApp.Workbooks.Open strTTLPath
        '
        XLApp.Range("E3:H3").Select
        XLApp.ActiveCell.FormulaR1C1 = "7/1/2001"
        XLApp.Range("E4").Select
        '
        XLApp.Workbooks.Application.ActivePrinter = "SRVNT01PRT_SCLR on Ne01:"
        XLApp.Sheets("Update").PrintOut
        XLApp.ActiveWorkbook.Close
    Next y
    XLApp.Quit
    Set XLApp = Nothing
    
End Sub
Public Function PrtUpdates()
    Call AttendanceUpdates
    
End Function
Sub ShiftData()
    mstrShift(1) = "Mids"
    mstrShift(2) = "Days"
    mstrShift(3) = "Swings"
End Sub
Sub DayFiles()
    mstrDFile(1) = "SUP#056.XLS"
    mstrDFile(2) = "SUP#029.XLS"
End Sub

Reply | Quote

Replies

You can set the status to saved just befor you close the work. Try the following (line in red) in your workbook ,

........
XLApp.Sheets("Update").PrintOut 
XLApp.ActiveWorkbook.Saved = True
XLApp.ActiveWorkbook.Close    
..........

Andrew

Reply | Quote

Thanks – Andrew

That worked great. I appreciate the quick response.

Reply | Quote

Andrew, how would you open a new workbook and give it a name without saving the file (re this thread)?

Reply | Quote

John,

I don’t think you can open a new workbook and change the name without actually giving it a name by saving it.

I’m not sure how the question relates to the other post, unless you mean to create a file and attach it to an email message, in which case it must be a saved file.

If I have misunderstood your question please put me right.

Andrew

Reply | Quote

You guessed right; I was trying to create a file and e-mail it without ever saving it. In regard to that thread, perhaps you can discern what problem poster “ptebo” could be having with the SendMail line producing runtime error 1004. I can’t reproduce the problem. Regards,

Reply | Quote

I’ll take a look John, but no guarantees.

Andrew

Reply | Quote