none
VBA Code Stops When Opening File With No Error RRS feed

  • Question

  • I have a macro that works about 80% of the time but sometimes will stop running with no error.  This occurs when opening a file.  I took out the offending code and put it in its own workbook and stand-alone short macro, and am still having the same problem.  It also seems that older macros that used to work are also now experiencing this issue.

    I upgraded to Excel 2010 this year, but I don’t think that is the problem but am not sure.  Maybe there is some type of memory or other issue in Windows?  Security or a certain setting that was changed by my IT department?   I read online about the shift key bug and tried some code related to that, but it didn't help.

    Offending code is below, it is simple code that has worked before consistently so I doubt the code is the problem:

    Sub OpenFile()

    'Selects and opens the current employee profile file.

    Dim strFileName As String

        strFileName = Application.GetOpenFilename(FileFilter:="Microsoft Excel Workbooks, *.xls; *.xlsx", _

            Title:="Please select the Employee Profile Data file.")

        If strFileName = "" Then

            MsgBox ("No file selected - Error.")

            Exit Sub

        End If 

        Workbooks.Open (strFileName)

        'MsgBox ("Successful run")

    End Sub

    Monday, September 22, 2014 7:21 PM

Answers

  • For the benefit of any future person with this problem, it appears I fixed it by adding DoEvents right before I open the workbook.  I think this allows the Operating System to take care of some things that may have been causing the problem?  At any rate, alls well that ends well.
    Monday, September 22, 2014 7:52 PM