Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Suppress prompts for saving tab-delimited file from VB Macro

Posted on 2012-03-26
4
Medium Priority
?
344 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 2000 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

Technology Partners: 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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

618 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