Writing code to 'Thisworkbook' from another workbook in vba

[dannyboy1982]

Hi,

I need to write some VBA code so that when I click a command button, it opens up a file (called IBSXL.xlsm) and automatically writes some code in the 'Thisworkbook' module. I replace this file everyday, so I cannot leave the code in the module as it gets overwritten each day. Is there a way to do this in VBA? I have managed to open the file and transfer a 'normal' module but cannot work out how to get the code into the 'Thisworkbook' module.

Public Sub TransferModule()
Const MODULE_NAME    As String = "module16"         ' Name of the module to transfer
Const TEMPFILE       As String = "c:\users\name\desktop\module16.bas" ' temp textfile
Dim WBK As Workbook
   
   On Error Resume Next
   '**Create new workbook
   Set WBK = Workbooks.Open("C:\users\name\desktop\IBSXL.xlsm")

   '** export the module to a textfile
   ThisWorkbook.VBProject.VBComponents(MODULE_NAME).Export TEMPFILE
  
   'import the module to the new workbook
   WBK.VBProject.VBComponents.Import TEMPFILE
  
   'kill the textfile
   Kill TEMPFILE
   
End Sub

I need the code in the 'Thisworkbook' module as I want the code to run as soon as the workbook opens without interaction from the user.

Any help would be greatly appreciated.

 

[Arithos]

Hi,

I was giving a completely different answer when I realized you might actually want this macro to be triggered by a workbook event?

If you want this macro to run when you open your workbook, simply put this as a workbook event. Change the type to fit your liking. To a "before close" or something similar.

Private Sub Workbook_Open()
Const MODULE_NAME    As String = "module16"         ' Name of the module to transfer
Const TEMPFILE       As String = "c:\users\name\desktop\module16.bas" ' temp textfile
Dim WBK As Workbook
   
   On Error Resume Next
   '**Create new workbook
   Set WBK = Workbooks.Open("C:\users\name\desktop\IBSXL.xlsm")

   '** export the module to a textfile
   ThisWorkbook.VBProject.VBComponents(MODULE_NAME).Export TEMPFILE
  
   'import the module to the new workbook
   WBK.VBProject.VBComponents.Import TEMPFILE
  
   'kill the textfile
   Kill TEMPFILE

End Sub

 

[RoryA]

Where is the code in the source file?

 

[dannyboy1982]

I do want a macro to be triggered by opening the workbook and I realise that this code has to go into the 'Thisworkbook' module, however am I able to use code in another workbook to add it for me?

I run a report from another program and export to excel each day and call it IBSXL, each day I overwrite the file which consequently erases all code (as I am overwriting with a new file), what I need is to have the code saved in another workbook (titanium.xls) and to be able to run a macro from this workbook that will open IBSXL and automatically put the required code into the 'Thisworkbook' module.

 

[dannyboy1982]

At the minute the code that I would like put into the opening workbook ('Thisworkbook' module) is in a normal module in the source file.