Solved

Suppress prompts for saving tab-delimited file from VB Macro

Posted on 2012-03-26
4
327 Views
Last Modified: 2012-06-22
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?
0
Comment
Question by:mamuscia
  • 2
  • 2
4 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37769578
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
 

Author Comment

by:mamuscia
ID: 37771104
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
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37772328
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
 

Author Closing Comment

by:mamuscia
ID: 37773110
Works like a charm.....thank you so much.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question