Returning the value from .path

[dawsona]

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.

 

[Jim North]

When I run this code I get an error on:

With Workbooks(mySource)_

change this to:

With Workbooks(mySource)

... other than that, if MACRO34 exists in runs ok.

 

[dawsona]

The _ was a typo in my message. I am okay with what the code is doing and it does work.

What I was asking is how can I kill the text file created by the macro? The .path is not curdir and I can't find a way of returning it in order to ChDrive /ChDir

Given that this is going out to different users the .path may vary for all of them.

 

[Andrew Poulsom]

This should work:

With Workbooks(mySource)
   ChDir .Path
End With

or

ChDir Workbooks(mySource).Path

or

ChDir ThisWorkbook.Path

 

[Juan Pablo González]

Use Kill

Kill .Path & "code.txt"

 

[dawsona]

Thanks both of you. "Kill .path ..." didn't work (Excel 97) but I cobbled together a bit of both, came up with

ChDrive ThisWorkbook.Path
ChDir ThisWorkbook.Path
Kill "code.txt"

and it did the trick.

 

[Juan Pablo González]

the .Path was taken from your code...

You could just use

Kill modname

 

[dawsona]

Juan Pablo

Even better. No point in having code that isn't adding anything of value.

Thanks.