VBA select no to "Do you want to save changes"

[Lorlai]

I want to create a vba code that will make changes to an excel file, then auto close the excel file. However, when this happens, the user always has to view the message box "Do you want to save the changes you made to 'filename.xlxs'?". Is there a way to write something in vba so that "no" is auto selected? And pressed, so that it is like the user pressed no?

Thank you

 

[Datsmart]

If you want to close the active workbook without SAVE or Prompt...

    ActiveWorkbook.Close SaveChanges:=False

 

[Hermanito]

Datsmart: your solution won't save the file, that is not the OP's intent, I think...

Lorlai, try this instead:

Dim WB as Workbook
Set WB = TheWorkbookToBeSavedAndClosedWithoutDialogs
Application.DisplayAlerts = False
WB.Save 'leave this line out if you don't want the changes to be saved
WB.Close
Application.DisplayAlerts = True

You need to adapt the Set WB line to your situation...

 

[Lorlai]

Thank you for getting back to me so fast Datsmart and Hermanito!

I have tried both of your solutions, and am unsuccessful with both. I read back through my post and realized I forgot to mention that I am doing this through Access (I am importing an excel file, then closing it down when all the data is transfered through). Does Access VBA require a different syntax with this command?

Thank you again!

 

[Hermanito]

You confuse me now...

first you said there were changes to be made to the Excelfile
but merely importing it in Access would not make any changes, so it would not trigger the dialogbox to save changes when closing it again.

Can you show us the code you have in Access right now?

 

[Lorlai]

Option Explicit
Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias _
"GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type
Public Function CreateAccess()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
Dim WrksheetName As String
Dim i As Integer
Dim oApp As Object
 
OpenFile.lStructSize = Len(OpenFile)
'OpenFile.hwndOwner = Form.Hwnd
'OpenFile.hInstance = App.hInstance
'sFilter = "acSpreadsheetTypeExcel (*.xlxs)" & Chr(0) & "*.xlxs" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "E:\"
OpenFile.lpstrTitle = "Choose a File"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
Set oApp = CreateObject("Excel.Application")
oApp.Visible = True
oApp.Workbooks.Open OpenFile.lpstrFile
'Finds the first empty cell in a column
Range("A1").End(xlDown).Offset(1, 0).Select
'The first few rows are unnecessary, this deletes them
Range(ActiveCell.Row & ":" & ActiveCell.Row).Select
Range("1:1", ActiveCell.Row & ":" & ActiveCell.Row).Delete
Range("A1").Select
'Jumps down to the next row Loops through the row until
'there is an empty cell, clearing the color formats and making the
'font black (automatic)
Do
With Selection.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
With Selection.Font
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    End With
 
'Finds and replaces spaces with an underscore
ActiveCell.Value = Replace(ActiveCell.Value, " ", "_")
ActiveCell.Offset(0, 1).Select
Loop Until IsEmpty(ActiveCell)
'Deletes the last row of data
Range("A" & Cells(Rows.Count, "A").End(xlUp).Row).Select
Selection.Delete Shift:=xlUp
With oApp
.Visible = False
DoCmd.TransferSpreadsheet (acImport), acSpreadsheetTypeExcel97, "Temp1", OpenFile.lpstrFile, True
End With
oApp.Workbooks.Close 
Set oApp = Nothing
End Function

Here is my code; I am importing an excel spreadsheet, reformatting it, then closing it, which prompts the savechanges dialog.

 

[Hermanito]

Do you want to save the reformatted excelfile or not?

Try to add the lines in red at the end of your code:

[COLOR="Red"]oApp.DisplayAlerts = False[/COLOR]
oApp.Workbooks.Close 
[COLOR="Red"]oApp.DisplayAlerts = True[/COLOR]
Set oApp = Nothing

with those lines added, you should not be getting the dialogbox anymore.
Caution: the file is NOT saved, you lose all changes made...

EDIT: You should probably add a
oApp.Quit
before the
Set oApp = Nothing

 

[Lorlai]

Hermanito, you are my hero! Thank you so much