Suppress prompts for saving tab-delimited file from VB Macro

I have a VB macro that splits a large Excel file into chunks and then saves each chunk as a tab-delimited file.  I get those annoying popup's that ask if I am sure I want to save the file.
I tried using Application.DisplayAlerts = False before the SAVEAS, and then turn it back on after the SAVEAS using Application.DisplayAlerts = True.

Is there any way to get rid of the popup prompts?
mamusciaAsked:
Who is Participating?
 
dlmilleCommented:
You're getting the error on the close method.  As you've already saved the file, then change that line to:

wbNew.Close SaveChanges:=False

Open in new window


Or you can move the .DisplayAlerts=True below that line, and Excel should take the default settings on the .Close.  However, I think changing the .Close method to what you want to be the appropriate approach.

Cheers,

Dave
0
 
dlmilleCommented:
Ok - exactly which prompts are you getting that are not being suppressed by the Application.DisplayAlerts = False statement?

Would you share your code snippet that shows the steps doing the save?

In my testing, I'm not getting any prompts when I have Application.DisplayAlerts=False

Dave
0
 
mamusciaAuthor Commented:
Below is the VB code I am using.  It works to split the Excel file into chunks of Tab-delimited files, but it prompts for each file created with the message I attached as a file.

Sub SplitRows()
    Dim rLastCell As Range
    Dim rCells As Range
    Dim strName As String
    Dim lLoop As Long, lCopy As Long
    Dim twodiglCopy As String
    Dim wbNew As Workbook
    Dim thisFile As String
    thisFile = ThisWorkbook.FullName
    thisFile = Replace(thisFile, ".xls", "")
    ' MsgBox (thisFile)
     
    With ThisWorkbook.Sheets(1)
        Set rLastCell = .Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
        For lLoop = 1 To rLastCell.Row Step 2000
            lCopy = lCopy + 1
            Set wbNew = Workbooks.Add
            Sheets("Sheet1").Cells(1, 1).EntireRow.Copy
            wbNew.Sheets(1).Cells(1, 1).PasteSpecial
            .Range(.Cells(lLoop, 1), .Cells(lLoop + 2000, .Columns.Count)).EntireRow.Copy _
            Destination:=wbNew.Sheets(1).Range("A2")
            If lCopy < 10 Then
                twodiglCopy = "0" & lCopy
            Else
                twodiglCopy = lCopy
            End If
            ' wbNew.Close SaveChanges:=True, Filename:=thisFile & "_" & twodiglCopy & ".xls"
            Application.DisplayAlerts = False
            wbNew.Saved = True
            wbNew.SaveAs Filename:=thisFile & "_" & twodiglCopy & ".txt", FileFormat:=xlText, CreateBackup:=False
            Application.DisplayAlerts = True
            wbNew.Close
        Next lLoop
    End With
End Sub
Excel-Prompt.png
0
 
mamusciaAuthor Commented:
Works like a charm.....thank you so much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.