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

  • Thread starter Thread starter scoobz
  • Start date Start date
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
 
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
 
....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"
 
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.
 
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
 
Thanks again Guys!

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