Import/Export Sheet Modules

[ylDevo]

I have created an update for some existing workbooks which requires the addition of VBA in some of the sheets. I can import/export forms and modules without any trouble, but I haven't yet gotten this to work for the individual sheets or ThisWorkbook.

I would like to know if it is possible to export modules from Sheets and ThisWorkbook and then import them into another workbook's corresponding Sheets?

Thanks.

[Jacob Hilderbrand]

If you copy a workbook (save as a new workbook for example) ThisWorkbook code will be copied (along with everything else).

If you copy a sheet to a new workbook the code for that sheet is copied as well.

Or you can write the code directly to VBA from VBA.

[ylDevo]

Jacob, Thanks for reply. Here's what I am trying to accomplish. I have 25 individiual budget workbooks already written and in use. I have been asked to add a mild layer of security to these workbooks, just to make it harder for someone to access the salary information which is stored on one of the sheets - which I have done on a test workbook. I have now created a new, separate workbook that, when I press a command button, opens the existing budget workbooks, installs the user forms and modules and the corresponding VBA code. I am essentially trying to get around opening each workbook and manually copying the code for ThisWorkbook and Sheet modules. I have it all coded except for this piece. Copying the worksheets - at least as I understand your response - wouldn't be optimal in this case since I would have to re-enter the information from the existing workbooks (salary information is different, employees, etc), which is basically what I am trying to avoid. Any other advice? Thanks.

[Richie(UK)]

Hi,

Have a look at Chip's guide here:
http://www.cpearson.com/excel/vbe.htm

This should give you a good start. Let us know if you have any problems implementing this.

HTH

[ylDevo]

Hi Richie. I have read through this and implemented Chip's code. I can export the modules for ThisWorkbook and Sheet*, but cannot figure out the correct code to accomplish the import into the workbooks in the correct place. Even when I attempt to manually import the code from the saved file that is created, it brings it in as a Class Module instead.

As an aside, in Chip's instructions under Copying Modules Between Projects he mentions copying all modules "except the ThisWorkbook and Sheet modules". I have seen questions similar to mine posted on some other forums, but without any answers and it may be that this cannot be done like I am hoping.

I started with something like this:

Workbooks("Book1").ThisWorkbook.VBProject.VBComponents.Import

But I cannot get it working...I appreciate all the help.

Chris

[mvidas]

Hi Chris,

You can still do this without importing the module. Here is an example that shows how you can add it from a string; may prove useful for you.

Sub ylDevo()
 Dim WB As Workbook, ThisWorkbookCode As String, SheetCode As String, i As Integer
Set WB = Workbooks("Book7.xls")
ThisWorkbookCode = "Sub ExampleCode1()" & vbCr & " Msgbox ""Hello""" & vbCr & "End Sub" & vbCr
 SheetCode = "Sub ExampleCode2()" & vbCr & " Msgbox ""Hello again""" & vbCr & _
  "'Extra text" & vbCr & "End Sub" & vbCr
WB.VBProject.VBComponents.Item("ThisWorkbook").CodeModule.AddFromString (ThisWorkbookCode)
 For i = 1 To WB.VBProject.VBComponents.Count
  If WB.VBProject.VBComponents.Item(i).Name = Sheets("Sheet Name").CodeName Then
   WB.VBProject.VBComponents.Item(i).CodeModule.AddFromString (SheetCode)
   Exit For
  End If
 Next
End Sub

If you're getting an error with this, it could be because VBA Extensibility isn't referenced. If so, add the following under the Dim statement:

 On Error Resume Next
 ThisWorkbook.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
 On Error GoTo 0

Should you have any questions please don't hesitate to ask!
Matt

[ylDevo]

Thanks Matt.

Let me work with this a while (it will take me a bit to digest it all being relatively inexperienced with VBA) and I'll let you know what happens.

Chris

[mvidas]

Understandable, it isn't the easiest thing in the world to get/use at first. I would be glad to help if need be.

FYI: When you first open a workbook, if you rename the first sheet to be called "Summary", it still has an object name/codename of "Sheet1" in vba. If you know what the object name of the sheet(s) to add code to, it would be a bit easier (in the same sense that we already know "ThisWorkbook"). The For loop in my above example is something i got from mdmackillop to determine the correct object to add the code into.

As I said, let me know if you'd like any assistance/guidance
Matt

[zilpher]

As an alternative (I hope I understood your question here) I would suggest using an addin that captures the sheet activate event and runs code to protect the sheet.

I've attached an addin that is protected with a password of: a

Save the addin to your drive, and add it by clicking Tools/Addins/Browse

Once the addin is open, open the VBE (Press Alt F11)

Find the addin in the project explorer, it's called AppWrapper.xla, open it using the letter a as the password.

When this addin is loaded, all events in the application will be passed through this addin too allowing you to add code for every single sheet activate (for example) regardless of the code in the workbook. This means you can run the same code every time an event runs, without having to repeat it many many times.

In the addin is a class called xlApp. , the following code is at the bottom:

 Private Sub xlApp_SheetActivate(ByVal Sh As Object)
 'if Sh.Parent.Name = WorkbookName then
     If Sh.Name = "Sheet1" Then 'change to your sheet name
         'put your security code here
         'for the sake of an example:
         MsgBox "You selected the sheet to be protected"
     End If
 'End If
 End Sub

The first If statement (which is commented out) checks the workbook name, change it to suit, you might want to do a like compare or a left() for example, to see if the workbook is the right type of workbook to evaluate.

The next if statement is to check the sheet name, I used Sheet1. Again, you might want to use a Left() to compare the name, or maybe it's called Salary in which case If Sh.Name = "Salary" would suit.

You can then put your security code in the if statement, save the addin, reopen excel, test it, fix it and when it's ok, send the addin out rather than update all the workbooks with the same code.

This makes maintenance easier too. If you need to change your security code later on, you only need to change the addin, not all the workbooks with the code.

You'll need to be careful to ensure you don't make the sheet name comparison to general or you'll be protecting sheets that don't need protecting.

HTH

[zilpher]

ok, the addin *is* attached now, doh!

[Richie(UK)]

Hi Chris,

Well, the ThisWorkbook and Sheet modules are effectively Class Modules so I can see why you might get the result that you described when trying to import them.

I must apologise - I should have been more specific with my reference to Chip's guide. If you look about half way down you will see a section entitled 'Creating An Event Procedure' - that was the part that I meant for you to read.

You will note that it uses the '.InsertLines ' approach with the code in the form of a string, much like the example code that Matt has provided for you. As Matt said, if you need any further help just let us know.

[ylDevo]

So many options to try!

Thanks for the suggestions. I am going to work with each one to gain some experience. I am just glad to see that it can be done.

Chris

[Richie(UK)]

Hi Chris,

Only briefly tested (and only with ThisWorkbook) but this appears to work too:

'NB Add reference to Extensibility library

Sub Main()
    Dim wbkSrc As Workbook
Application.EnableEvents = False
Set wbkSrc = Workbooks.Open _
        (Filename:="C:\Documents and Settings\Richie\My Documents\" & _
        "Excel\VBA Spreadsheets\Book1.xls")
    Call ExportImport(wbkSrc, ThisWorkbook, "ThisWorkbook")
    wbkSrc.Close
Application.EnableEvents = True
End Sub

Sub ExportImport(wbkSource As Workbook, wbkTarget As Workbook, strMod As String)
    Dim vbProjSrc As VBProject, vbProjTgt As VBProject
    Dim VBCodeMod As CodeModule
Set vbProjSrc = wbkSource.VBProject
    Set vbProjTgt = wbkTarget.VBProject
vbProjSrc.VBComponents(strMod).Export Filename:="c:\CopyCode.bas"
Set VBCodeMod = vbProjTgt.VBComponents(strMod).CodeModule
    Call DeleteAllCodeInModule(VBCodeMod)
    With VBCodeMod
        .AddFromFile Filename:=("c:\CopyCode.bas")
        .DeleteLines 1, 4
    End With
End Sub

Sub DeleteAllCodeInModule(VBC As CodeModule)
    Dim StartLine As Long
    Dim HowManyLines As Long
With VBC
        StartLine = 1
        HowManyLines = .CountOfLines
       .DeleteLines StartLine, HowManyLines
    End With
End Sub

Something else for you to try.

[j2associates]

I know this is a very old thread but Ritchie's code above was very helpful. However, this line will fail because it is trying to write directly to the root of c. vbProjSrc.VBComponents(strMod).Export Filename:="c:\CopyCode.bas". If you change it to vbProjSrc.VBComponents(strMod).Export Filename:="c:\temp\CopyCode.bas", the code will work.

[SamT]

You can write to the Root of C. Or the Root of any drive. Not recommended, but possible.