Excel VBA: Closing Message Box Automatically in

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
sagetechitAsked:
Who is Participating?
 
sagetechitAuthor Commented:
I discovered that the solution is nesting my code in this:

Application.AskToUpdateLinks = False
    MyCode
Application.AskToUpdateLinks = True
0
 
Helen FeddemaCommented:
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
 
sagetechitAuthor Commented:
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
 
sagetechitAuthor Commented:
Correct answer.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.