Macro: cmd to open/save files in local dir?

S

scoobz

....forgive the stupid question, but I'm still quite new at this:

What cmd in a macro can I use to open/save files in the same loca
directory as the exisiting open file.

Whenever I try an open or save cmd, it requires the full sourc
path.

The idea behind it, is that I want to make some macros and share the
with other colleagues, but as they are logged on as different users
the source path is never the same, so I just want them to recognize th
immediate folder.

Thanks
 
D

Dave Peterson

You can get the path of the workbook like:

dim myPath as string
myPath = activeworkbook.path 'the workbook which is active
myPath = Thisworkbook.path 'the workbook running the code.

So it kind of depends on which one you want:

Here's a couple of basic examples:

Option Explicit
Sub testme1()

Dim myPath As String
Dim myFileName As String

myPath = ThisWorkbook.Path

myFileName = myPath & "\" & "myotherfilename.xls"

ActiveWorkbook.SaveAs Filename:=myFileName, FileFormat:=xlWorkbookNormal

'just for testing
MsgBox ActiveWorkbook.Path

End Sub

Sub testme2()

Dim myPath As String
Dim myFileName As String
Dim wkbk As Workbook

myPath = ThisWorkbook.Path

myFileName = myPath & "\" & "myotherfilename.xls"

If Dir(myFileName) = "" Then
MsgBox "Not found"
Else
Set wkbk = Workbooks.Open(Filename:=myFileName)
End If

End Sub
 
S

scoobz

....back again ;)

After using all the various information gathered so far, I am usin
something like:

ActiveWorkbook.SaveAs Filename:= _
ThisWorkbook.Path & "\" & "test.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

....to save files.

However, this only lets me save in the current directory or furthe
down. Is there a cmd I can slip in there somewhere which tells it to g
"back" or "up" a directory?

Example in lamens terms:

ThisWorkbook.Path & "-back- " & "test.xls"
 
D

Dave Peterson

One way to Up the directory structure:

ActiveWorkbook.SaveAs Filename:= _
ThisWorkbook.Path & "\..\" & "test.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

If you remember your old DOS commands, this is the equivalent to:

CD ..
to go back up one level.

And if you know the name of the folder you want to drop down to:

ActiveWorkbook.SaveAs Filename:= _
ThisWorkbook.Path & "\downonemorefolder\" & "test.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

But it has to exist.

If you're not sure that it does, you can try to create it:

on error resume next
mkdir thisworkbook.path & "\downonemorefolder"
on error goto 0

The on error portion just says that if it already exists, don't bother telling
me about the failure to create.
 
D

Don Guillett

In addition this backup macro I use may be of help. In the current folder,
it will create a backup directory (if needed) and save a copy there and save
the file where it is.

Sub Backup() 'kept in personal.xls & assigned to toolbar button
On Error GoTo BackupFile
MkDir CurDir & "\Backup"
BackupFile:
With ActiveWorkbook
MyWB = .Path & "\BACKUP\" & .Name
.SaveCopyAs MyWB
.Save
End With
End Sub
 
S

scoobz

Thanks again Guys!

(...and out of all the modern operating systems we have, DOS wa
probably the most stable...lol Quite ironic really)
 
Top