asked on

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

Helen Feddema

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

sagetechit

ASKER

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.

ASKER CERTIFIED SOLUTION

sagetechit

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sagetechit

ASKER

Correct answer.