Excel Macro - Relative Path
 

Hi Skip,

You could use something like the following 'GetFolder' function to browse
for a 'base' folder:

Function GetFolder(Optional Title As String = "Select a Folder", Optional
RootFolder As Variant) As String
On Error Resume Next
GetFolder = CreateObject("Shell.Application").BrowseForFolder( 0, Title, 0,
RootFolder).Items.Item.Path
End Function

This macro shows how the 'GetFolder' function might be implemented:

Sub Test()
Fold = GetFolder(Title:="Find a Folder", RootFolder:=&H11)
MsgBox Fold
End Sub

Cheers


"Skip" wrote in message
...
Hello all,

I am not a programmer but I do record Macros using the Macro Recorder in
Excel and can edit the results. Really grateful for any help with this.

I have a spreadsheet say c:\01\01.xls. Beneath this directory is a tree of
directors containing Excel sheets that are opened, manipulated, closed and
saved via a Macro in c:\01\01.xls.

Works fine, but I want to replicate the structure by copying the entire
structure across to c:\02, c:\03, c:\04, etc.Problem is, the Macros
contain
an absolute path and so still refer to the spreadsheets in the c:\01 tree.
Is there any way to edit the path to relative and therefore make the tree
structure portable? If it's a complex solution, please give me as much
detail as you can and give an example of how and where the code should be
inserted.

Many thanks.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.725 / Virus Database: 480 - Release Date: 19/07/2004

Excel Macro - Relative Path
 

Hello all,

I am not a programmer but I do record Macros using the Macro Recorder in
Excel and can edit the results. Really grateful for any help with this.

I have a spreadsheet say c:\01\01.xls. Beneath this directory is a tree of
directors containing Excel sheets that are opened, manipulated, closed and
saved via a Macro in c:\01\01.xls.

Works fine, but I want to replicate the structure by copying the entire
structure across to c:\02, c:\03, c:\04, etc.Problem is, the Macros contain
an absolute path and so still refer to the spreadsheets in the c:\01 tree.
Is there any way to edit the path to relative and therefore make the tree
structure portable? If it's a complex solution, please give me as much
detail as you can and give an example of how and where the code should be
inserted.

Many thanks.

Excel Macro - Relative Path
 

Many thanks Macropod.

So if I have a recorded Macro containing the following:

ChDir "C:\MI\01"
Workbooks.Open FileName:=
_"C:\MI\01\01.csv
Workbooks.Open FileName:=
_"C:\MI\01\02.csv

etc, etc...

How would I edit the code to find MI as the root directory with the relative
path from there down to various sub directories?

Apologies if this is basic, but I'm unsure how to implement.

Thanks.