none
how to use "saved = true" to suppress "save change" prompt in excel.applition? RRS feed

  • Question

  • yes, microsoft has make a clear explain for this in http://support.microsoft.com/kb/213428

    but when i do this:

    Dim excel As Object
    Set excel = CreateObject("Excel.application")
    excel.Workbooks.Open "d\1.xls"
    
    'some opeation will modify this sheets
    'omission.....
    'give excel a flag to skip save
    excel.Saved = True 'error 438
    excel.Worksheets.Saved = True 'error 438
    excel.Workbooks.Saved = True 'error 438
    excel.quit Set excel = Nothing

    excel.Saved = True     or     excel.Worksheets.Saved = True    or    excel.Workbooks.Saved = True

    all retrun error with:

    "real-time error '438'
    object does not support this property or method."

    so how to modify it? thanks!




    • Edited by itakeblue Monday, May 14, 2012 5:10 PM
    • Changed type itakeblue Tuesday, May 15, 2012 2:18 AM
    Monday, May 14, 2012 5:08 PM

Answers

  • "real-time error '438'
    object does not support this property or method."...

    ... because the method Saved is a method of Workbook object.

    Sub AAA()
      Dim excel    As Object
      Dim Wkb      As Object
      Set excel = CreateObject("Excel.application")
      Set Wkb = excel.Workbooks.Open("d:\1.xls")
      'some opeation will modify this sheets
      'omission.....
      '---------------
      Wkb.Saved = True
      Wkb.Close
      '---------------
      'Or
      'Wkb.Close False
      '---------------
      excel.Quit
      Set Wkb = Nothing
      Set excel = Nothing
    End Sub

    Artik
    • Marked as answer by itakeblue Tuesday, May 15, 2012 2:19 AM
    Monday, May 14, 2012 6:46 PM
  • Alternatively, use

        Wkb.Close SaveChanges:=False
    

    I wouldn't name the application object excel. If you ever set a reference to the Excel object model, that could lead to confusion. I'd use app or xlApp or objExcel.

    Regards, Hans Vogelaar

    • Marked as answer by itakeblue Tuesday, May 15, 2012 2:21 AM
    Monday, May 14, 2012 7:55 PM

All replies

  • "real-time error '438'
    object does not support this property or method."...

    ... because the method Saved is a method of Workbook object.

    Sub AAA()
      Dim excel    As Object
      Dim Wkb      As Object
      Set excel = CreateObject("Excel.application")
      Set Wkb = excel.Workbooks.Open("d:\1.xls")
      'some opeation will modify this sheets
      'omission.....
      '---------------
      Wkb.Saved = True
      Wkb.Close
      '---------------
      'Or
      'Wkb.Close False
      '---------------
      excel.Quit
      Set Wkb = Nothing
      Set excel = Nothing
    End Sub

    Artik
    • Marked as answer by itakeblue Tuesday, May 15, 2012 2:19 AM
    Monday, May 14, 2012 6:46 PM
  • Alternatively, use

        Wkb.Close SaveChanges:=False
    

    I wouldn't name the application object excel. If you ever set a reference to the Excel object model, that could lead to confusion. I'd use app or xlApp or objExcel.

    Regards, Hans Vogelaar

    • Marked as answer by itakeblue Tuesday, May 15, 2012 2:21 AM
    Monday, May 14, 2012 7:55 PM
  • thanks Artik_, it works.

    also thanks Hans's naming habit suggestion:)

    Tuesday, May 15, 2012 2:21 AM