Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update or insert new VBA module (Excel 2000/SR2)

    I want to write a Visual Basic application that will open an existing Excel workbook, and insert a new module(bas) opr class(cls) file into it. This application should also be able to delete an existing module or cls if it exists. I have been looking at examples of using VBE but they present it from doing something like what I describe from within the Excel workbook whereas I need to do perform these functions from a Visual Basic 6 applcation. Can anyone guide me to some examples?

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update or insert new VBA module (Excel 2000/SR2)

    Something like this should get you started :

    Sub ImportModule()
    Dim oXL as Excel.Application
    Dim VBP as VBIDE.VBProject
    Dim sFilename as string
    Set oXL=New Excel.Application
    With oXL
    .Visible=True
    .Workbooks.Open "CataTest.xls"
    Set VBP=.ActiveWorkbook.VBProject
    sFilename="c:dataModule1.bas"
    VBP.VBComponents.Import sFilename
    .Activeworkbook.Save
    .Quit
    End With
    Set oXL = nothing
    End Sub

    You need references to the Excel and the "Microsoft Visual Basic for Applications Extensibility" library.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update or insert new VBA module (Excel 2000/SR2)

    Jan Karel probably gave you what you needed, but it looked like a fun project to create, so here is my version. This one shows how to delete a module and insert lines of code on the fly. Like Jan Karel's example, you need to add references to Excel and VBE. Notice that in both of our cases, we prefixed the objects from these libraries with Excel or VBIDE. Doing this will save you a lot of grief: these libraries often have objects with the same name and if you do not specify a prefix, VB will use the VB object. Note also that this is a complete VB project: just create a new project, add a module, delete the form, and paste this code into the module. There is more info on programming the VBE on Chip Pearson site, but you will have to translate it to VB. I thought that I had an example of creating a userform on the fly, but I cannot find it. To create a class module, use vbext_ct_ClassModule instead of vbext_ct_StdModule. Post back if you need more help, Bryan is our real expert and he hasn't posted yet. HTH --Sam
    <pre>Option Explicit

    Public Sub main()
    Dim xlApp As New Excel.Application
    Dim xlBook As Excel.Workbook
    Dim vbeProject As VBIDE.VBProject
    Dim vbeModule As VBIDE.CodeModule
    Dim strBook As String
    Dim i As Integer
    strBook = xlApp.GetOpenFilename
    Set xlBook = xlApp.Workbooks.Open(strBook)
    xlApp.Visible = True
    Set vbeProject = xlBook.VBProject
    With vbeProject
    On Error Resume Next
    .VBComponents.Remove .VBComponents("myMod")
    On Error GoTo 0
    With .VBComponents.Add(vbext_ct_StdModule)
    .Name = "myMod"
    Set vbeModule = .CodeModule
    End With
    With vbeModule
    i = .CountOfLines + 1
    .InsertLines i, _
    "Sub Hi()" & Chr(13) & _
    " Msgbox ""Hi"" " & Chr(13) & _
    "End Sub"
    End With
    End With
    End Sub</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Update or insert new VBA module (Excel 2000/SR2)

    Found the example which creates a userform with code. It is on John Walkenbach's site, http://www.j-walk.com/ss/excel/tips/tip76.htm. Again, you will need to translate it to VB code. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update or insert new VBA module (Excel 2000/SR2)

    Thanks for the example. I was headed in this same direction but kept running into problems due to a brain freeze. Yours and Sammyb's replies have melted the ice around my brain!

  6. #6
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update or insert new VBA module (Excel 2000/SR2)

    Thanks for the example. I was headed in this same direction but kept running into problems due to a brain freeze. Yours and pieterse's replies have melted the ice around my brain!

  7. #7
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update or insert new VBA module (Excel 2000/SR2)

    I have run into a new problem with trying to add a new VBA module to an excel spreadsheet which has to do if the VBA sectionis protected. I have tried the following code to remove the password but it doesn't work, I don't get an error message until I actually try to import the new module. See code below and comments explaining where error occurs and message generated.

    Dim xlsApp As New Excel.Application
    Dim xlBook As Excel.Workbook

    Dim VBP As VBIDE.VBProject
    Dim VBModule As VBIDE.CodeModule

    Dim path As String
    Dim fname As String
    Dim mname As String

    Dim pro As Boolean

    path = "C:Excel Examples"
    fname = "Lease Amortization gov.XLT" ' The VBA code is password protected
    mname = "Example.bas"

    fname = path & fname

    'Open the workbook
    xlsApp.Workbooks.Open fname

    If xlsApp Is Nothing Then
    MsgBox "Excel file open did not work"
    Else
    MsgBox "Excel file open worked"
    End If

    mname = path + mname

    ' Unprotect the VBA code
    xlsApp.ActiveWorkbook.Unprotect ("ppcexcelws")

    Set VBP = xlsApp.ActiveWorkbook.VBProject

    pro = VBP.Protection

    ' Insert the new module
    VBP.VBComponents.Import mname ' It fails here. I get a message bos that states "Can't perform operation since the project is protected"

    ' Save the modified workbook
    xlsApp.ActiveWorkbook.Save

    xlsApp.Quit

    Set xlsApp = Nothing
    Set VBP = Nothing

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update or insert new VBA module (Excel 2000/SR2)

    Unprotecting the workbook does NOT unprotect it's VBA project.
    VBA projects cannot be unprotected by normal VBA coding, except using (VERY UNRELIABLE) Sendkeys.

    So use this line:
    pro = VBP.Protection
    and if pro evaluates to True, show a msgbox telling the user to first unprotect the VBAProject.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Update or insert new VBA module (Excel 2000/SR2)

    Jim,
    There is, as far as I am aware, no way of unprotecting a VBA project via code. (I guess it was an (unsuccessful) attempt to limit hacking options!) I think you'll need to find an alternative method of achieving your goal.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update or insert new VBA module (Excel 2000/SR2)

    Thanks. Can you direct me to inform,ation about "Send Keys"? My situation is that myself and some others are developing and will maintain several hundred templates where we reuse certain modules and at the same time by requirements we cannot use addins( stupid). So as an adi to maintaining we wish to use importing modules to all of the templates but the templates are also VBA password protected so that is why I am asking about being able to unprotect. If we have to unprotect via manual we might as well import while we are there. We also were looking at using the import to be able to update templates that have been sold to clients but again the VBA code is password protected.

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update or insert new VBA module (Excel 2000/SR2)

    Here is a thread about this subject.

    But I would strongly advise you to have a serious talk with the IT managment about this issue. I would urge them to enable you to distribute an add-in with all of the necessary code.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •