Exporting module from workbook to workbook - not working (user specific)

[marc81]

Hi all,

I have some code that takes a module from the workbook that I am running a macro, exports it to a temporary bas file then imports it to a newly created workbook. This works absolutely fine for me, just just had another member of my team advise different. We've stepped through it, and it doesn't error, it just doesn't create the bas file. Anybody any idea why? Here's my code, sorry if it's a bit messy I'm no expert... there is a bit in there that changes some of the text in the bas file as when I was importing it to the new workbook with the same module name it kept linking it back to the original workbook (if that makes sense). Like I say though, works perfect for me. The code in red is where I think it may be failing.

    'Copy macro from main file to extract
    Dim strModuleName As String
    Dim strFolder As String
    Dim strTempFile As String
    ThisWorkbook.Activate              '<= changed
    strFolder = ThisWorkbook.Path      '<= changed
    If Len(strFolder) = 0 Then strFolder = CurDir
    strFolder = strFolder & "\"
    strTempFile = strFolder & "~tmpexport.bas"
    On Error Resume Next
    ThisWorkbook.VBProject.VBComponents("Alloc_Date_Button").Export strTempFile '<= changed
  
            'AMEND THE EXPORTED MACRO TO NEW NAME
            Dim objFSO
            Const ForReading = 1
            Const ForWriting = 2
            Dim objTS 'define a TextStream object
            Dim strContents As String
            Dim fileSpec As String
          
            fileSpec = ThisWorkbook.Path & "\~tmpexport.bas"
            Set objFSO = CreateObject("Scripting.FileSystemObject")
            Set objTS = objFSO.OpenTextFile(fileSpec, ForReading)
            strContents = objTS.ReadAll
            strContents = Replace(strContents, "Alloc_Date_Button", "Alloc_Date_Button_Export")
            strContents = Replace(strContents, "ALLOC_DATE_UPDATE", "ALLOC_DATE_UPDATE_EXPORT")
          
            objTS.Close
          
            Set objTS = objFSO.OpenTextFile(fileSpec, ForWriting)
            objTS.Write strContents
            objTS.Close

  
    Output.VBProject.VBComponents.Import strTempFile '<= changed
    Kill strTempFile
    On Error GoTo 0

Thanks,

Marc

 

[RoryA]

You won't see any errors due to your On Error Resume Next statement. Your code will fail if the user's trust centre settings do not have the 'trust access to VBA project option' set.

 

[marc81]

You won't see any errors due to your On Error Resume Next statement. Your code will fail if the user's trust centre settings do not have the 'trust access to VBA project option' set.

Of course I won't see the error sorry completely forgot about that! He's just been allocated a new laptop so that would make sense, thanks so much for your help.