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