Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Close without saving (Excel 97/SR2)

    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. <font color=blue> Is there something I could add to my code to automatically answer the dialog box with 'NO'?</font color=blue>

    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.

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


  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Close without saving (Excel 97/SR2)

    You can set the status to saved just befor you close the work. Try the following (line in red) in your workbook ,<pre>........
    XLApp.Sheets("Update").PrintOut
    <font color=red>XLApp.ActiveWorkbook.Saved = True</font color=red>
    XLApp.ActiveWorkbook.Close
    ..........</pre>


    Andrew

  3. #3
    2 Star Lounger
    Join Date
    May 2001
    Location
    Omaha, NE USA, Nebraska, USA
    Posts
    119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Close without saving (Excel 97/SR2)

    Thanks - Andrew

    That worked great. I appreciate the quick response.

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Close without saving (Excel 97/SR2)

    Andrew, how would you open a new workbook and give it a name without saving the file (re <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=51678>this thread</A>)?
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Close without saving (Excel 97/SR2)

    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

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Close without saving (Excel 97/SR2)

    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,
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Close without saving (Excel 97/SR2)

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

    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •