Solved

Suppress prompts for saving tab-delimited file from VB Macro

Posted on 2012-03-26
4
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

705 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