Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import a CodeModule then call its method in Workbook_Open()
I have a Module called "CodeFile.bas" which i Share amongst several
workbooks - It has a method called KickStart() I want to do something like the following: Private Sub Workbook_Open() Me.VBProject.VBComponents.import "c:\CodeFile.bas" Call KickStart() End Sub i.e. Import the code file and then subsequently call one of its methods. Any ideas how I could achieve this effect. Dickster |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import a CodeModule then call its method in Workbook_Open()
The problem is that you get a compile error, so you need to get around that.
Luckily, if the call to KickStart is in another module, it overcomes the compile error. So, what you need to do is add a module that will always be in the workbook (that is, not imported on open) with a procedure that calls KickStart (that is all it will do). Assuming this procedure is called CallImportedMacro, then your open code would be Private Sub Workbook_Open() Me.VBProject.VBComponents.import "c:\CodeFile.bas" Call CallImportedMacro End Sub in the code module you would have Public Sub CallImportedMacro() Call kickStart End Sub An alternative way is to Application.Run it Private Sub Workbook_Open() Me.VBProject.VBComponents.Import "c:\CodeFile.bas" Application.Run "KickStart" End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I have a Module called "CodeFile.bas" which i Share amongst several workbooks - It has a method called KickStart() I want to do something like the following: Private Sub Workbook_Open() Me.VBProject.VBComponents.import "c:\CodeFile.bas" Call KickStart() End Sub i.e. Import the code file and then subsequently call one of its methods. Any ideas how I could achieve this effect. Dickster |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import a CodeModule then call its method in Workbook_Open()
Works a treat Bob - Thanks
Dickster |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import a CodeModule then call its method in Workbook_Open()
Which option did you go with?
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... Works a treat Bob - Thanks Dickster |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import a CodeModule then call its method in Workbook_Open()
Just curious...
Why not just keep that code in another workbook (maybe an addin) and run the code directly from there? wrote: I have a Module called "CodeFile.bas" which i Share amongst several workbooks - It has a method called KickStart() I want to do something like the following: Private Sub Workbook_Open() Me.VBProject.VBComponents.import "c:\CodeFile.bas" Call KickStart() End Sub i.e. Import the code file and then subsequently call one of its methods. Any ideas how I could achieve this effect. Dickster -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import a CodeModule then call its method in Workbook_Open()
First of all thanks Bob for you answer which did address my original
problem. Thanks Dave for guidance towards an "Add-In". This makes life way easier Im able to call the InitialiseConfig() method living in my Add-In from the WorkBook_Open() Im also able to reference the methods other in the Add-In in my VBA. By setting a reference (Tools - References command ) The true benefit is that an update to the Add-In becomes available to each Spreadsheet that references it the next time it is opened i.e. change code in one place but used in many places (updates automatically available) just what i was looking for :-) Thanks Guys A wee side question I place a list of classes in my Add-In eg. AxaSpecific.cls CMSpecific.cls FPSpecific.cls GESpecific.cls Depending on which Workbook you open i.e. AXAimport.xls CMimport.xls FPimport.xls GEimport.xls I want to instantiate the appropriate object currently i use something like Select Case UCase(Left(ThisWorkBook.Name, 2)) Set providerSpecific = New AxaSpecific Case "CM" Set providerSpecific = New CMSpecific Case "FP" Set providerSpecific = New FPSpecific Case "GE" Set providerSpecific = New GESpecific .. .. .. Case Else End Select Is there a slicker way as my case statement has many more options |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import a CodeModule then call its method in Workbook_Open()
That seems a slick enough way, but another way is to have a wrapper class
that is exposed to the workbook which handles all of the other classes. This class would know about the other classes, the workbook code wouldn't, and would route all the request to the class through the wrapper. Probably not any less more (maybe even more), but it would clean up the workbook code, and abstract the class code. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message ups.com... First of all thanks Bob for you answer which did address my original problem. Thanks Dave for guidance towards an "Add-In". This makes life way easier Im able to call the InitialiseConfig() method living in my Add-In from the WorkBook_Open() Im also able to reference the methods other in the Add-In in my VBA. By setting a reference (Tools - References command ) The true benefit is that an update to the Add-In becomes available to each Spreadsheet that references it the next time it is opened i.e. change code in one place but used in many places (updates automatically available) just what i was looking for :-) Thanks Guys A wee side question I place a list of classes in my Add-In eg. AxaSpecific.cls CMSpecific.cls FPSpecific.cls GESpecific.cls Depending on which Workbook you open i.e. AXAimport.xls CMimport.xls FPimport.xls GEimport.xls I want to instantiate the appropriate object currently i use something like Select Case UCase(Left(ThisWorkBook.Name, 2)) Set providerSpecific = New AxaSpecific Case "CM" Set providerSpecific = New CMSpecific Case "FP" Set providerSpecific = New FPSpecific Case "GE" Set providerSpecific = New GESpecific . . . Case Else End Select Is there a slicker way as my case statement has many more options |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I call up the Text Import Wizard via a macro. | Excel Worksheet Functions | |||
call multiple macros in workbook_open? | Excel Programming | |||
GetObject method not work after Call Shell Method | Excel Programming | |||
How do I call a method in VB from Excel with JUST code? | Excel Programming | |||
How do I call a method in VB from Excel with JUST code? | Excel Programming |