Misbehaving VBA macro

Ludwig

Board Regular
Joined
Apr 7, 2003
Messages
50
I have a macro that opens CSV files associated with the main workbook. Eventually I will also add code to process them, but at the present stage, the macro has annoying bug I cannot resolve.

What the macro does is find all relevant CSV files in a specified subdirectory from where the main workbook is located & opened. It compares the date/time held within the main workbook with their last updated datetime and if more recent then it opens it. If the CSV file is already open, it does nothing with it (no need to 're-open' it).

At the end, it switches back to the main workbook.

My problem is ... this "switch back to the main workbook (where the macro is stored)" does not happen. A msg entered in application.statusbar shows the activeworkbook name is correct, but the workbook with the cursor in it, and the top window of course, is the last CSV file opened. See uploaded image.

One easy way I have found to avoid the problem is to simply add a call to MsgBox("msg") as the last statement and voila! the main workbook becomes the activeworkbook window, just as it should.
Using a workbook object or referencing the main workbook by ThisWorkbook.Name method to activate it makes no difference to the problem.

What is going wrong? I included the macro code below, with a testing boolean switch in it that proves it fails to truely make the main workbook the activeworkwork unless I issue that Msgbox dialog!

VBA Code:
Global glTest As Boolean

Sub OpenCSVFiles()
    Dim FileName As String, lCount As Long, oCount As Long, FolderName As String, TestDate As Date, FileDate As Date, myMainWb As String
    myMainWb = ThisWorkbook.Name
    TestDate = ThisWorkbook.Sheets("How to import data").Cells(1, 1).Value
    FolderName = ThisWorkbook.Path
    If LCase(Left(FolderName, 8)) = "https://" Then
        For i = 1 To 4      ' Remove the first four backslashes
            FolderName = Mid(FolderName, InStr(FolderName, "/") + 1)
        Next
        FolderName = Environ("onedrive") & "\" & FolderName & "\HomeBank\Export"
    End If
    FolderName = Replace(FolderName, "/", "\")

    Dim myScrnUpd As Boolean, myEvents As Boolean, myFormula As String, myCalc As Integer
    myScrnUpd = Application.ScreenUpdating
    Application.ScreenUpdating = False
    myEvents = Application.EnableEvents
    Application.EnableEvents = False
    
    lCount = 0
    oCount = 0
    Debug.Print "searching in folder '" & FolderName & "' for CSV files after " & _
                Format(TestDate, "dd-mmm-yy hh:mm:ss") & ".."
    FileName = Dir(FolderName & "\hb-repstat_*.csv")
    While Len(FileName) > 1
        lCount = lCount + 1
        FileDate = FileDateTime(FolderName & "\" & FileName)    ' needs full path qualified to work
        On Error Resume Next
        If WorkBookIsOpen(FileName) Then
            Debug.Print "File" & Format(lCount, "000") & ": " & FileName & _
                                " already open  - last modified " & FileDate
        ElseIf TestDate < FileDate Then
            Workbooks.Open FileName:=FolderName & "\" & FileName
            oCount = oCount + 1
            Debug.Print "File" & Format(lCount, "000") & ": " & FileName & _
                                " opened  - last modified " & FileDate
        Else
            Debug.Print "File" & Format(lCount, "000") & ": " & FileName & _
                                " IGNORED - last modified " & FileDate
        End If
        FileName = Dir()
    Wend
    Debug.Print lCount & " CSV files checked, " & oCount & " opened"
    
' restore settings and the workbook & hence sheet that was showing at the start
    Application.EnableEvents = myEvents
    Application.ScreenUpdating = myScrnUpd
    Workbooks(ThisWorkbook.Name).Activate
    Application.StatusBar = lCount & " CSV files checked, " & oCount & " opened" _
                            & " .. activeworkbook = " & ActiveWorkbook.Name

    If glTest Then
        Call MsgBox("Why don't we show the main workbook without this message?!?" & _
                vbNewLine & vbNewLine & lCount & " CSV files checked, " & oCount & " opened", _
                vbOKOnly, "What's going wrong here ?? !!")
    End If
    glTest = Not glTest     ' so flip between modes to test
    Debug.Print "Last line of code  ===> active workbook = " & ActiveWorkbook.Name

End Sub


Function WorkBookIsOpen(Name As String) As Boolean

    Dim xWb As Workbook
    On Error Resume Next
    Set xWb = Application.Workbooks.Item(Name)
    IsWorkBookOpen = (Not xWb Is Nothing)

End Function

I've even cut the code down to just what you see above in a test workbook with one worksheet named "How to import data", but and the problem remains.

Any help much appreciated.
 

Attachments

  • Screenshot 2020-09-23 164054.jpg
    Screenshot 2020-09-23 164054.jpg
    95 KB · Views: 6

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Ludwig

Board Regular
Joined
Apr 7, 2003
Messages
50
I assume that you are using Excel where the Office version is 15.0 (2013) or above – Excel 2013 changed to the principle of SDI (Single Document Interface) with each workbook having it's own window as opposed to the previous principle of MDI (Multiple Document Interface) in which a single parent window is used to contain multiple nested child windows – i.e. the open workbooks. This has had many consequences within VBA, one being Workbook.Activate makes the defined workbook object the active workbook but, dependant upon the WindowState, does not always put it to the top of the Z order to make it the top front workbook.

Try this –

Declare a Long variable :

VBA Code:
Dim lngWinState As Long

Just before you active your main workbook, return the current WindowState :

VBA Code:
'Return windows state.
    lngWinState = Application.WindowState

Straight after you activate your main workbook, apply the WindowState:

VBA Code:
'Ensure windows state,
    Application.Windows(myMainWb).WindowState = lngWinState

As an aside, this change by dear old Micros**t also messes with Application.SreenUpdating and there is quite a discussion on line at VBA ScreenUpdating-Property does not work in Excel 2016!

Tried the "WindowState" as suggested, unfortunately did not make any difference.

My test code is now, in case I misread your post:
VBA Code:
Global glTest As Boolean

Sub OpenCSVFiles()
    
    Application.StatusBar = ""  ' clear it ou
    Dim FileName As String, lCount As Long, oCount As Long, FolderName As String, _
        TestDate As Date, FileDate As Date, myMainWb As String
    myMainWb = ThisWorkbook.Name
  
    TestDate = ThisWorkbook.Sheets("How to import data").Cells(1, 1).Value
    FolderName = ThisWorkbook.Path
    If LCase(Left(FolderName, 8)) = "https://" Then
        For i = 1 To 4      ' Remove the first four backslashes
            FolderName = Mid(FolderName, InStr(FolderName, "/") + 1)
        Next
        FolderName = Environ("onedrive") & "\" & FolderName & "\HomeBank\Export"
    End If
    FolderName = Replace(FolderName, "/", "\")
    Application.StatusBar = Time & " Locating CSV files to process in folder '" & FolderName & "' ...."

    Dim myScrnUpd As Boolean, myEvents As Boolean, myFormula As String, myCalc As Integer
    myScrnUpd = Application.ScreenUpdating

    ' =============
    Dim lngWinState As Long
    lngWinState = Application.WindowState   ' Return windows state so can restore it at the end
        '  above 2 lines hopefully fixes which window shows at the end of all this -
        '     refer https://www.mrexcel.com/board/threads/misbehaving-vba-macro.1146458/#post-5562550
        '     Mick_Webb's entry 28Sep20 @ 10:08AM for details.
    '  NOT MAKING A DIFFERENCE !!
    ' =============

    Application.ScreenUpdating = False
    myEvents = Application.EnableEvents
    Application.EnableEvents = False
    
    lCount = 0
    oCount = 0
    Debug.Print "searching in folder '" & FolderName & "' for CSV files after " & _
                Format(TestDate, "dd-mmm-yy hh:mm:ss") & ".."
    FileName = Dir(FolderName & "\hb-repstat_*.csv")
    While Len(FileName) > 1
        lCount = lCount + 1
        FileDate = FileDateTime(FolderName & "\" & FileName)    ' needs full path qualified to work
        On Error Resume Next
        If WorkBookIsOpen(FileName) Then
            Debug.Print "File" & Format(lCount, "000") & ": " & FileName & _
                                " already open  - last modified " & FileDate
        ElseIf TestDate < FileDate Then
            Application.ScreenUpdating = False
            Workbooks.Open FileName:=FolderName & "\" & FileName
'            Workbooks(myMainWb).Activate
            Application.ScreenUpdating = myScrnUpd
            oCount = oCount + 1
            Debug.Print "File" & Format(lCount, "000") & ": " & FileName & _
                                " opened  - last modified " & FileDate
        Else
            Debug.Print "File" & Format(lCount, "000") & ": " & FileName & _
                                " IGNORED - last modified " & FileDate
        End If
        FileName = Dir()
    Wend
    Debug.Print lCount & " CSV files checked, " & oCount & " opened"
    
' restore settings and the workbook & hence sheet that was showing at the start
    Application.EnableEvents = myEvents
    Application.ScreenUpdating = myScrnUpd
    DoEvents
    
    Workbooks(myMainWb).Activate
    ' =============
    Application.Windows(myMainWb).WindowState = lngWinState     ' reinstate initial windows state
        '     refer https://www.mrexcel.com/board/threads/misbehaving-vba-macro.1146458/#post-5562550
        '     Mick_Webb's entry 28Sep20 @ 10:08AM for details.
    '  NOT MAKING A DIFFERENCE !!
    ' =============
  
'    Application.Goto ThisWorkbook.ActiveSheet.Range("A1"), True
'    Application.Goto Workbooks(myMainWb).ActiveSheet.Range("A1"), True
'    Columns(3).Select
    Application.StatusBar = lCount & " CSV files checked, " & oCount & " opened" _
                            & " .. activeworkbook = " & ActiveWorkbook.Name
'    Call Repaint4Excel_Ribbon_Bug
    If glTest Then
        Call MsgBox("Why don't we show the main workbook without this message?!?" & _
                vbNewLine & vbNewLine & lCount & " CSV files checked, " & oCount & " opened", _
                vbOKOnly, "What's going wrong here ?? !!")
    End If
    glTest = Not glTest     ' so flip between modes to test
    Debug.Print "Last line of code  ===> active workbook = " & ActiveWorkbook.Name & _
                ", ScreenUpdating = " & Application.ScreenUpdating
    Debug.Print "debug.print activeworkbook.name"   ' so can then run it in Immed Window
'    Columns(1).Select

End Sub


Function WorkBookIsOpen(Name As String) As Boolean

    Dim xWb As Workbook
    On Error Resume Next
    Set xWb = Application.Workbooks.Item(Name)
    IsWorkBookOpen = (Not xWb Is Nothing)

End Function
 

Mick_Webb

New Member
Joined
Apr 4, 2016
Messages
17
Office Version
  1. 2016
  2. 2013
  3. 2010
  4. 2007
Platform
  1. Windows
mmm interesting - sorry it was no help, it solved a similar problem I had a few years back. I wrapped this into a function that I call whenever I want to activate a workbook but the core principle is the same.

I have not worked with 365 but, reading through your messages, it could be that Micros**t have worked on the ScreenUpdating property – the main gripe here was that the property was of the current top window, if you set it to False and then changed windows, i.e. by activating or opening another workbook Application.ScreenUpdating was that of the new window – True by default.

Try –

VBA Code:
With Application
     .ScreenUpdating = True
     .Windows(myMainWb).WindowState = lngWinState
     .ScreenUpdating = False
End With
 

Ludwig

Board Regular
Joined
Apr 7, 2003
Messages
50

ADVERTISEMENT

mmm interesting - sorry it was no help, it solved a similar problem I had a few years back. I wrapped this into a function that I call whenever I want to activate a workbook but the core principle is the same.

I have not worked with 365 but, reading through your messages, it could be that Micros**t have worked on the ScreenUpdating property – the main gripe here was that the property was of the current top window, if you set it to False and then changed windows, i.e. by activating or opening another workbook Application.ScreenUpdating was that of the new window – True by default.

Try –

VBA Code:
With Application
     .ScreenUpdating = True
     .Windows(myMainWb).WindowState = lngWinState
     .ScreenUpdating = False
End With

No difference. Thanks for the suggestions though.
The fix you've had for some time is like my "screen jiggle" I had to do on Excel 2013 with ribbon not repainting on occasions after a macro ran (it would go all white including the top window line along with the X to close excel and no amount of clicking in the area would do anything!') - change the window size to be so small Excel did a ribbon display recalc/adjust. Then change settings back as they were. 9 lines of VBA code - all was fixed then. They run so quick you didn't actually notice the "flick".

Wish M$ would just fix these sort of seemingly small but incredibly frustrating things that they've been told about! Leaving them as-is is becoming more than just annoying.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
Maybe this is a solution:
VBA Code:
Application.Windows(myMainWb).Activate


In addition, your WorkBookIsOpen function in this appearance will always return a FALSE ...
Rich (BB code):
Function WorkBookIsOpen(Name As String) As Boolean

    Dim xWb As Workbook
    On Error Resume Next
    Set xWb = Application.Workbooks.Item(Name)
    IsWorkBookOpen = (Not xWb Is Nothing)

End Function
 

Ludwig

Board Regular
Joined
Apr 7, 2003
Messages
50
Oooh!! Big oopsie there, spelling function name wrong! Thanks for picking that up. Hadn't got to testing that part yet.

That other idea didn't work either. Never mind, as of now I'm giving up on trying to get it working I'd like. I need to get other bits working.

Many thanks for the help.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,090
Office Version
  1. 2013
Platform
  1. Windows
Glad to help and thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,035
Messages
5,545,640
Members
410,696
Latest member
JTrehan
Top