PDA

View Full Version : Solved: Change Path -- ActiveWorkbook.path



MPDK166
03-22-2011, 08:46 AM
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
03-22-2011, 11:34 AM
Maybe

Filepath = Activeworkbook.Path & "1"

MPDK166
03-23-2011, 06:07 AM
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
03-23-2011, 06:52 AM
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
03-23-2011, 08:05 AM
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
03-23-2011, 08:15 AM
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
03-23-2011, 08:35 AM
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
03-23-2011, 10:51 AM
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.
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

MPDK166
03-24-2011, 05:30 AM
Kenneth,

Your solution works for me!

Thanks