1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel VBA Exporting Modules

Discussion in 'Business Applications' started by scotty718, Sep 9, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. scotty718

    scotty718 Thread Starter

    Joined:
    Nov 19, 2010
    Messages:
    185
    Hi All,

    Having some trouble exporting modules from code. Not sure why. Any help would be appreciated. I am getting Run-time error '50012' Method 'Export' of object '_VBComponent' failed. I am using an .xls workbook in Excel 2007.

    My code is below, but the export breaks even when I do this:

    Code:
    strPath = "C:\ReplaceTaxCode" 
    ActiveWorkbook.VBProject.VBComponents("ToolsA").Export "\" & strPath
    Full Code Here:
    Code:
    Public Sub ReplaceCode()
    
    Dim wkbCode, wkbReplace As Workbook
    Dim strPath
    Dim VBAEditor As VBIDE.VBE
    Dim objProject As VBIDE.VBProject
    Dim objComponent As VBIDE.VBComponent
    
    Set wkbCode = ThisWorkbook
    Set VBAEditor = Application.VBE
    Set objProject = VBAEditor.ActiveVBProject
    
    strPath = "C:\ReplaceTaxCode"
    
    For Each objComponent In objProject.VBComponents
        objProject.VBComponents(objComponent.Name).Export "\" & strPath
    Next
    
    End Sub
     
  2. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,955
    That's quite clear

    objProject.VBComponents(objComponent.Name).Export & "\" & strPath

    The syntax is incorrect, it must be something like

    objComponent.Export strPath & "\" & objComponent.Name & ".bas"

    Google is great!
     
  3. scotty718

    scotty718 Thread Starter

    Joined:
    Nov 19, 2010
    Messages:
    185
    Thank you Hans.

    I actually have pulled the following code I found (using google :)) - http://www.cpearson.com/excel/vbe.aspx. It's the code called CopyModule. Its great, except it is hanging at a certain point. I hope I can eliminate the hang.
     
  4. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,955
    You will have to be a little more specific, have you tried debugging? Just go thorugh the module step by step and see where it hngs or goes wrong?
    You could put an aposttrophe in from of all the On Error Resume Next lines so that these will not be triggered, that way you will gen an error message, then click on degub an you will be taken to that line of code and you can see what goes wrong.
     
  5. scotty718

    scotty718 Thread Starter

    Joined:
    Nov 19, 2010
    Messages:
    185
    Sorry. I wasn't more specific. I know where it was hanging, but didn't understand why, but wasn't really asking for help :) I actually just ran it again, and voila!, works like a charm! Some great, great code, IMHO.
     
  6. Keebellah

    Keebellah Hans Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,955
    (y) Good for you. Congrats :)
     
  7. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1016783