Solved: Change Path -- ActiveWorkbook.path

[MPDK166]

When I retrieve a Path with ActiveWorkbook.path, i want to change the path, how to do?

e.g.

FileCurrent path:
c:\Test
FilePath = ActiveWorkbook.path & "\"

New Path:
C:\Test1

FilePath = ???????????????

[Bob Phillips]

Maybe

[vba]Filepath = Activeworkbook.Path & "1"
[/vba]

[MPDK166]

Unfortunately not...

Because the folder in the new path can be a different name.
Actually I need to go down a folder and then go up to the new folder.

[Paul_Hossler]

XLD's solution works for your example, so I think we need a better example

Do you want to save the wprkbook to a different folder?

Paul

[MPDK166]

I have got a workbook called Total.xls in the path C:\Documents:\2011\Report\Total.xls

I need data from different files on different paths

e.g.

C:\Documents\2011\03\20110316.xls

or

C:\Documents\2011\04\20110404.xls


So the path I need to access is variable.

[Kenneth Hobs]

You need to go into more details. We can't read your mind. You need to tell us the rules or what logical criteria governs. Are the paths in C:\Documents:\2011\Report\Total.xls or all files from the root of C:\Documents\2011\ less the C:\Documents:\2011\Report\Total.xls or what?

Half the solution is defining the problem.

[MPDK166]

That's True...

The folder of the file i am using is on a network location ......\2011\Reports\Total.xls

Therefore i want to use ActiveWorkbook.Path method because every folder after 2011 is definitive.

I want to access different files on different locations to retrieve data


The files are on the following location:

.....\2011\Registration\Month$\FileName$.xls

[Kenneth Hobs]

All I can see is that you want the path one level lower. How you get the other, is unclear to me unless it is hardcoded.

e.g.
[vba]Sub t()
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
MsgBox "ThisWorkbook.Path = " & ThisWorkbook.Path & vbLf & _
"Path one folder down = " & fso.GetFolder(ThisWorkbook.Path & "\..").Path
Set fso = Nothing
End Sub[/vba]

[MPDK166]

Kenneth,

Your solution works for me!

Thanks