Misbehaving VBA macro

[Ludwig]

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!

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.

 

[rondeondo]

Hi
Can you try changing this line:

Workbooks(ThisWorkbook.Name).Activate

to

Workbooks(myMainWb).Activate

the problem with using activeshhet and thisworkbook is that when you open another file it becomes the new active book/sheet. Makes debugging hard as when you step through the code it keeps coming back to the sheet with the code in it.
when you start operating on multiple files it's good to have workbook variables to know you're referring to what you intend to be working on. It takes a little extra setting up but it's worth it.

Ron

 

[Norie]

Does this work?

Application.Goto ThisWorkbook.ActiveSheet.Range("A1"), True

 

[Ludwig]

Thanks for the replies rondeondo & Norie. Highly appreciated.

I retested "Workbooks(myMainWb).Activate", and tried "Application.Goto" .. something I never used before. Verified for each that before exiting the macro the activesheet is indeed the main workbook, by inserting a columns(3).select at the end, and seeing that that had been actioned on the main workbook and not the last opened CSV.

BUT.. absolutely no change to the problem. The last opened CSV file remains the workbook on top. I even tried inserting 'DoEvents' once the mainwb is activated at the end .. no change.
Even if I resize the activewindow so it's not exactly the same height and width as it was, that occurs on the main window but doesn't eliminate the problem.


Further experimenting:
The *only* way I have been able to prevent it is to not set ScreenUpdating = false !
Should have tried that earlier, but habits meant that code went in on automatic (along with EnableEvents being turned off & reset at end).
Even tested turning screenupdating on immediately before the open workbook line & an "Off" immediately after (with & without 'DoEvents' after that)... still no change to the problem. Just meant, as I suspected, that the screen flashes.
Result: not stopping screen updates means things flash on the screen which is what I was trying to avoid. But the problem of having the right workbook active when the macro exits goes away.


Thanks for your input. If you do come up with a solution, don't hesitate to post.

 

[Norie]

Why aren't you closing the workbooks you are opening?

 

[Ludwig]

Because I need to leave them open, at least for now. I code iteratively, checking each logical step in the process works correctly.
I need to validate their content then paste it into the mainwb before I can close them. Having the mainwb not left as the top window just annoyed the heck out of me. And it shouldn't be behaving that way (mutter mutter ... excel ..).

I've got the 'paste the data' code for that "next step" done & tested (which includes the 'close'). At least for now I want to run them as separate steps, I'll likely merge them together soon. I now need to work on the validation side and then I can fully merge the steps together, knowing each bit has been extensively tested separately.

 

[rondeondo]

How about making a temp sheet in your main workbook to dump your csv into, then do your iterations through that. Then delete the temp sheet when done. Or just clear it out.

 

[Norie]

If you are doing things iteratively I would have thought the first iteration would be for opening and closing the CSVs.

Once that's thoroughly tested and working you would move on to the bit in the middle where you actually do something with the files you are opening.

By the way, I would recommend that when you are open a file you create a reference to it.

That reference can then be used throughout the code whenever you need to refer to the file.

 

[Ludwig]

If you are doing things iteratively I would have thought the first iteration would be for opening and closing the CSVs.

Once that's thoroughly tested and working you would move on to the bit in the middle where you actually do something with the files you are opening.

By the way, I would recommend that when you are open a file you create a reference to it.

That reference can then be used throughout the code whenever you need to refer to the file.

I could have, but wanted to leave them open so I can work on code needed to process them. Ultimately I'll call that code 'Sub' for each CSV file opened and then close it before moving onto the next one, hence have no need to create a reference to it.

 

[Mick_Webb]

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 :

Dim lngWinState As Long

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

'Return windows state.
    lngWinState = Application.WindowState

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

'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!