Drives and Directories

There is an easy way to change and manage the drive and directory in macros.


[vb autolinks=”false” classname=”myclass” collapse=”false” firstline=”1″ gutter=”true” highlight=”” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true” title=”Set Drive”]

MyDrive = "E:"
MyFolder = "\MyDocs\ThisFolder\"
ChDrive MyDrive
ChDir MyFolder
[/vb]


When done, the current directory will be E:\MyDocs\ThisFolder\.


Consider the following:


[vb autolinks=”false” classname=”myclass” collapse=”false” firstline=”1″ gutter=”true” highlight=”” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true” title=”Set Drive 2″]
MyPath = "E:\MyDocs\ThisFolder\"
ChDrive MyPath
ChDir MyPath
[/vb]


This code contains one less line (and one less variable), but it does the same thing. VBA, when executing the ChDrive command, only pays attention to the drive letter in a path. This allows you to easily set the single variable to your path, and then use it when both setting drives and directories.


From Allen Wyatt’s Excel Tips website

Add a folder if not there already

The following macro snippet adds three variables to store drive paths for use in a macro. The macro adds the three variables with the “Dim” statements which will store the locations for opening files or saving them to specific locations. This macro looks to a sheet called “Settings” in a “ToolBox.xlsm” file.

[vb autolinks=”false” classname=”myclass” collapse=”false” firstline=”1″ gutter=”true” highlight=”” htmlscript=”false” light=”false” padlinenumbers=”false” smarttabs=”true” tabsize=”4″ toolbar=”true” title=”Add Folder”]

Option Explicit

Private Sub add_folder()

Dim drivePath as String
Dim dlPath as String
Dim svPath as String

‘   Set directory for file selection / grab from worksheet

drivePath = Workbooks("ToolBox.xlsm").Worksheets("Settings").Range("B2").Value
dlPath = Workbooks("ToolBox.xlsm").Worksheets("Settings").Range("B3").Value
svPath = Workbooks("ToolBox.xlsm").Worksheets("Settings").Range("B4").Value

ChDrive drivePath
ChDir dlPath

If Right(drivePath, 1) <> "\" Then
drivePath = drivePath & "\"
End If

If Right(dlPath, 1) <> "\" Then
dlPath = dlPath & "\"
End If

If Right(svPath, 1) <> "\" Then
svPath = svPath & "\"
End If

End Sub
[/vb]