I have "ripped off" the code below from the message board to export and import a module
Dim modname As String
Dim mySource As String
Dim myDestination As String
mySource = ThisWorkbook.Name
myDestination = ActiveWorkbook.Name
With Workbooks(mySource)_
modname = .Path & "code.txt"
.VBProject.VBComponents ("Macros34").Export modname
End With
Workbooks(myDestination).VBProject.VBComponents.Import modname
This is great and does what I want. However, I want to kill the "code.txt" file but can't seem to change the drive / directory to be the same as the ".path" above i.e. I can get a message box to display the .path but cannot find a way of returning this as a string to use ChDir, ChDrive commands.
The macro will be sent out to global users and I don't want them to be able to open the text file and find out the sheet protection password contained within the module.
Dim modname As String
Dim mySource As String
Dim myDestination As String
mySource = ThisWorkbook.Name
myDestination = ActiveWorkbook.Name
With Workbooks(mySource)_
modname = .Path & "code.txt"
.VBProject.VBComponents ("Macros34").Export modname
End With
Workbooks(myDestination).VBProject.VBComponents.Import modname
This is great and does what I want. However, I want to kill the "code.txt" file but can't seem to change the drive / directory to be the same as the ".path" above i.e. I can get a message box to display the .path but cannot find a way of returning this as a string to use ChDir, ChDrive commands.
The macro will be sent out to global users and I don't want them to be able to open the text file and find out the sheet protection password contained within the module.