Returning the value from .path

dawsona

Board Regular
Joined
Jul 28, 2002
Messages
66
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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

Board Regular
Joined
Jul 28, 2002
Messages
66
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This should work:

Code:
With Workbooks(mySource)
   ChDir .Path
End With

or

Code:
ChDir Workbooks(mySource).Path

or

Code:
ChDir ThisWorkbook.Path
 

dawsona

Board Regular
Joined
Jul 28, 2002
Messages
66
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.
 

dawsona

Board Regular
Joined
Jul 28, 2002
Messages
66
Juan Pablo

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

Thanks.
 

Forum statistics

Threads
1,144,274
Messages
5,723,442
Members
422,497
Latest member
dougy99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top