This is post https://www.excelfox.com/forum/showt...ll=1#post24165 https://www.excelfox.com/forum/showt...age3#post24165
So I have added text to the existing text of an available _WorkbookOpen coding, and that text is identical to the text of the sort of coding that would do the business of checking to see if I opened test.csv , and tells me about it if I did/do
But important to remember That the text in coding format in last post is not coding that will ever be done. It is just the blue print text , like text on a piece of paper, or on a stamp
We need to make an actual object, A class object module, and put a copy of this coding in it. Better said, we want to stamp that text out where it will become VBA source coding
That is this next bit
Make the my watcher thing
It is convenient for this thing to be made when I open my main file, WatchMyFileOpenings.xls
So, just for that reason, I put the following coding in the ThisWorkbook code module of my main file, WatchMyFileOpenings.xls
ThisWorkbook: code module of WatchMyFileOpenings.xls
Code:
Option Explicit
Dim MeWatcher As FileOpenWatcher ' This must go here, not in the coding below, or else the variable will not exist after the coding below ends
Private Sub Workbook_Open() ' This is done when this file opens
Set MeWatcher = New FileOpenWatcher ' Make the object MeWatcher which is something like a running Excel
Set MeWatcher.ExApp = Excel.Application ' I think this makes the obkect, MeWatcher, the actual running Excel
End Sub
ThisWorkbook code module.JPG
The main thing will be in the variable I choose to call MeWatcher In other words that MeWatcher is the variable pointing to an object thing which actually will be somewhere in existence, monitoring when a file might be opened, and if that happens, it checks to see if it is test.csv , and if it is, it tells me with a pop up message box
Within the coding that kicks in when I open the file, WatchMyFileOpenings.xls , the two lines make that object MeWatcher
First Set MeWatcher = New FileOpenWatcher makes the object of the type we want, but its missing a few things. A Parallel could perhaps be a worksheet object, but with no specific Name, position, etc.,so we are not quite there, and we would not yet see, for example, a worksheet code module appear in the VB Editor left explorer window, or maybe we would, but it would look empty
At the moment the object is a sort of Excel Application type, but not a specific one .
The next line Set MeWatcher.ExApp = Excel.Application , will, I think, do the final job, making our actual running Excel the final object in the variable MeWatcher, or perhaps more accurately said , the application in that variable is the final object in the variable MeWatcher
What effectively then happens could be regarded as a class object code module being made and appearing looking somewhat similar to the worksheets object code modules or ThisWorkbook as seen in the VB Editor left explorer windows, like this , and that has a copy of the Class module coding in it.
So we could reasonably perceive that we have something like this appearing, which if it did, would take a position and "level" in the explorer view or OOP hierarchy, in the VB Editor , similar to that of the worksheets and the ThisWorkbook things
This is what it could be perceived to look like, just a perception suggestion from me
Object module, MeWatcher ( perception suggestion )
Code:
' This is , or can reasonably be perceived to be, the "real" coding that is done
Option Explicit
Public WithEvents ExApp As Excel.Application ' This makes ExApp a variable/ object that some how is like a running Excel, and also the WithEvents means it has access to all those codings that it has that kick off when something happens
' This and the next macro is the sort of coding we need to tell us if we open the file "test.csv"
Private Sub ExApp_WorkbookOpen(ByVal Wb As Workbook) ' This will become a property in a final Instanciated object, amnd I intend to use it
Dim s As String
Let s = Wb.Name
If s = "test.csv" Then Call MyMacro
End Sub
Sub MyMacro() ' This is available also in intellisense to a final instanciated object, although in this example I will not use it, - its calles from the routine above. Splitting the two is not done for any particular reason
MsgBox Prompt:="You just opened test.csv"
End Sub
That is , or can reasonably be perceived to be, the "real" coding that is done. And by virtue of the code line somewhere else of , Set MeWatcher.ExApp = Excel.Application , the variable , or object, MeWatcher , is effectively my running Excel
Unfortunately, that does not happen, or rather it does, or could be perceived to happen, but Microsoft just neglected to make it appear for us, so we must imagine it does.
At this point we have an object, MeWatcher, of the sort, FileOpenWatcher , which we want, which is one that is a bit like a running Excel.
That last code line, Set MeWatcher.ExApp = Excel.Application makes the variable in our new class object code module, (the one we can’t see), ExApp the actual running Excel
That all takes a few careful readings through to get it
The point is with all this class stuff, is that it is just that little bit more complicated such that you must think a few things carefully through to understand. A simple short explanation often does more harm than good, JIMHO, since, for example, telling you as people often do, that the worksheets code module is a class module , is wrong…. At least I think….. for now: So far, - the best experts in this field, won’t comment on this statement…. and I have asked many times… well actually, not quite, I am tangling with Mike about it currently from about here …. https://eileenslounge.com/viewtopic....317564#p317564
See also the next post
Alan
Bookmarks