Solved

Excel VBA: Closing Message Box Automatically in

Posted on 2011-02-22
4
936 Views
Last Modified: 2012-06-27
I'm writing a VBA Module in Excel to open other Excel workbooks.  When opening the workbooks I get a couple of message boxes that pop up.  I need to know how to automatically close these message boxes to allow my script to continue mining the data.

If I'm missing some simpler way to mine data from a bunch of workbooks. please let me know.




Here is a snapshot of my code as of now:
Sub MineTimesheets()


SearchPath = "C:\Documents and Settings\USER\Desktop\Time Sheets"


Dim NumWorkbooks As Integer
Dim i As Integer
Dim Data() As String

With Application.FileSearch
    .NewSearch
    .FileType = msoFileTypeExcelWorkbooks
    .LookIn = SearchPath
    .SearchSubFolders = False
    .Execute
    NumWorkbooks = .FoundFiles.Count
End With


For i = 1 To NumWorkbooks
    ReDim Data(1 To i)
    Data(i) = Application.FileSearch.FoundFiles(i)
    Worksheets("Sheet2").Cells(i, 1).Value = Data(i)
Next i


Workbooks.Open ("C:\Documents and Settings\USER\Desktop\Time Sheets\2011-02-16 to 28 Hotchkiss, Caleb.xls")
Application.DisplayAlerts = False
Workbooks(2).Worksheets("Time Sheet").Cells(5, 5).Value = 7
ActiveWorkbook.Close Savechanges:=True
Application.DisplayAlerts = True

End Sub
0
Comment
Question by:sagetechit
  • 3
4 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34953115
One possibility is to replace the messge boxes with messages written to the status bar -- no need for any user action.  Here is some code:
Application.DisplayStatusBar = True
Application.StatusBar = "Clearing worksheet rows - Please Wait!"

Open in new window

0
 

Author Comment

by:sagetechit
ID: 34953227
The code that Helen Feddema provided still allowed the popups to appear, so it didn't solve my problem.

I did notice that
"Application.DisplayAlerts = False"
prevents one of the popups, but it does not prevent the "This workbook contains links to other data sources. . ." popup.
0
 

Accepted Solution

by:
sagetechit earned 0 total points
ID: 34953596
I discovered that the solution is nesting my code in this:

Application.AskToUpdateLinks = False
    MyCode
Application.AskToUpdateLinks = True
0
 

Author Closing Comment

by:sagetechit
ID: 35196678
Correct answer.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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…

808 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