Folder referance ?

[fuzy]

the problem:

[VBA]
' Time Macro
' Macro enregistr?e le 27/06/2008 par
'

'
Application.Goto Reference:="R2C1"
Range("A2:C2").Select
Selection.Copy
Workbooks.Open Filename:=ThisWorkbook.Path & Application.PathSeparator & "test\ times.xls"
Range("G3").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Application.Goto Reference:="R2C1"
Selection.Copy
Range("M2").Select
Selection.Insert Shift:=xlDown
Application.Goto Reference:="R1C1"
Application.CutCopyMode = False
End Sub
[/VBA]
I have Datas/logs/test directories lets say I have a file in logs directory and so when I copy a cell to a file time.xls which locates in test directory it works. I can also do it if the file is in the same directory. But can I somehow reach the previous directory 'datas' from 'logs'? I have this file system on all of different computers but not always on the same location, so when I copy it should work without the full path name. I already tried:
filetoopen = Application.GetOpenFilename("Review Files (*.xls), *.xls")
but in this case I need to select the location and the file name, which brings up more possible error chances if others do not select the right file.
Please help me on this , if it's not possible just give me a note, that I should forget about the whole thing.
I might won't be able to see the answer for a week, but hopefully it is clear for you guys.
Thnx G.

[akanchu]

Hi,
I hope I understood the question right, you want to read a file from other directory. Are you able to store the path of data/logs in some variable, so that you could change paths in the code itself.

Have you tried using CHDIR(path) to change the path.

Hope this helps.

[fuzy]

tried to insert the CHDIR to the original code. not working. am I missing some slash or what?
The proboblem, to make it clearer, is I like to create an excel file (time.xls)and a macro in it. When you open the file and press the button that runs the macro it will copy the name and the actual time to an other excel file (log.xls). So if I place the file to c:\Datas\logs\test\time.xls and the other would be placed to c:\Datas\logs\log.xls . If I copy the files (and folders) to d: drive or to a network lets say d:\work\latest\Datas\logs\test\time.xls and also place the log.xls to logs directory it should see the file and run the macro without the need to select the actual location manually.
I do not know how to insert correctly the check dir command, maybe it could do it.
thnx G.

[akanchu]

So if the file is d:\work\latest\Datas\logs\test\time.xls then log should be in d:\work\latest\Datas\logs\log.xls

another eg.
file is d:\work\D080408\Datas\logs\test\time.xls
then log is in
d:\work\D080408\Datas\logs\log.xls

am I right?

[fuzy]

Yes you are totally right. Both of your examples correct. can you solve it? I will be glad.

Actually I have searching a lot for the correct word, maybe you can tell me, (asked some of my colleques also but the do not know either)
The sub folder is the folder which is inside of another, so how do you name the folder wich is outside (one level up) is it a root, or???

thanks G.

[akanchu]

I tried this simple form. Hope this is what you are looking for.

[VBA]

Dim spath As String
Dim dpath As String
Dim lfnd As Integer
spath = ActiveWorkbook.Path
'MsgBox spath
lfnd = InStr(1, spath, "logs", vbTextCompare)

' very basic - this 4 assumes that word is "logs" length 4
' other methods are possible too.
dpath = Mid(spath, 1, lfnd + 4)

'MsgBox dpath
'MsgBox dpath & "log.xls"

Workbooks.Open dpath & "log.xls"

[/VBA]

Let me know the outcome.

[fuzy]

I inserted the code, and it worked!!!! This is not the final version of mine, but I think if it works in this it will.
Really suprised...
thank you for your help.
G.