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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mamusciaAuthor Commented:
Works like a charm.....thank you so much.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.