[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 975
  • Last Modified:

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
0
sagetechit
Asked:
sagetechit
  • 3
1 Solution
 
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:
I discovered that the solution is nesting my code in this:

Application.AskToUpdateLinks = False
    MyCode
Application.AskToUpdateLinks = True
0
 
sagetechitAuthor Commented:
Correct answer.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now