Results 1 to 11 of 11
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Close Excel sheet pops up dialog box (Access@003 SP2)

    When executing the following command:
    mySheet.Application.activeworkbook.Close
    a dialog box jumps up asking if you want to write the file to disc.

    Is there a way to overcome this, i dont want this dialog box to popup?

    The code is as follows:
    Dim theRecSource As Long
    Dim xlApp As Object
    Dim mySheet As Object
    Dim theFilePath As String
    Dim theTemplateFile As String

    theFilePath = DLookup("ExcelReportsPath", "tbl Control Local") '"M:Machine PartsExcel DPL Files"
    theTemplateFile = theFilePath & "ReportsR1.xls" 'QuoteForm.xls"

    Set xlApp = CreateObject("Excel.Application")
    Set mySheet = xlApp.workbooks.Open(theTemplateFile).sheets(1)

    Dim rsU As DAO.Recordset

    Set dbs = CurrentDb
    Dim iPosnCode As Integer
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''


    Set rs = dbs.OpenRecordset("tbl R1 ParMonths")
    Do While Not rs.EOF
    ' Update 1st Year/Month column
    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''
    ' Update the singles for PosnXCode
    sSql = "SELECT *"
    sSql = sSql & " FROM [qry rpt R1 Excel]"
    Set rsU = dbs.OpenRecordset(sSql)
    If Not rsU.EOF Then
    Dim iColNo As Integer
    For iColNo = 1 To iNoofYearMonths + 1
    ' sSql = "UPDATE [" & sSheetName & "]"
    ' sSql = sSql & " SET F" & iColNo + 2 & " = " & rs("Col" & iColNo)
    ' sSql = sSql & " WHERE F15 = " & rs!RowNo
    ' dbs.Execute sSql
    mySheet.cells(rsU!excelRowNo, iColNo + 2).Value = rsU("Col" & iColNo)
    Next iColNo
    End If

    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''
    ' Call UpdateSheet(rs!ServiceYear, rs!ServiceMonth)
    rs.MoveNext
    Loop

    '''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''

    ' mySheet.Application.activeworkbook.Save
    mySheet.Application.activeworkbook.Close
    xlApp.Quit

    Set mySheet = Nothing
    Set xlApp = Nothing
    Attached Images Attached Images

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Close Excel sheet pops up dialog box (Access@003 SP2)

    If you want to save the workbook:

    mySheet.Parent.Close SaveChanges:=True

    If you want to close the workbook without saving changes:

    mySheet.Parent.Close SaveChanges:=False

    mySheet.Parent is the workbook to which mySheet belongs.

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Close Excel sheet pops up dialog box (Access@003 SP2)

    Thanks for that, but the Save As dialog box leaps up to ask the question whether you want it saved or not.

    Can this Save As dialog box be suppressed?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Close Excel sheet pops up dialog box (Access@003 SP2)

    If you use one of the lines I proposed, you shouldn't get a prompt, unless you use SaveChanges:=True and the file is read-only.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Close Excel sheet pops up dialog box (Access@003 SP2)

    You might try inserting

    xlApp.DisplayAlerts = False

    above the line that closes the workbook, but it would be better to find out why the prompt appears.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Close Excel sheet pops up dialog box (Access@003 SP2)

    I believe the file may be read only, as i have used SaveChanges:=True, how do i check the file for read only, as it's not read only in the properties of the file in windows explorer?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Close Excel sheet pops up dialog box (Access@003 SP2)

    If you open the workbook manually in Excel, do you get a warning that it's read-only? Or does the title bar contain [Read-only] after the name of the workbook?

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Close Excel sheet pops up dialog box (Access@003 SP2)

    Thanks Hans, it is working.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Close Excel sheet pops up dialog box (Access@003 SP2)

    So what was the solution?

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Close Excel sheet pops up dialog box (Access@003 SP2)

    What you suggested worked, i found out that the sheet was read only.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Close Excel sheet pops up dialog box (Access@003 SP2)

    OK, thanks.

Posting Permissions

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