file names

  • Thread starter Thread starter mika.
  • Start date Start date
M

mika.

When I use the SaveAs function as follows:

ActiveWorkbook.SaveAs Filename:="H:\A\B\Sept 10 2003
\Sept10-1.xls", AccessMode:=xlShared
....when I open Sept10-1.xls, it says that it is shared,
but when I use:

ActiveWorkbook.SaveAs Filename:="Sept10-1.xls",
AccessMode:=xlShared
....when I open Sept10-1.xls, it doesn't say that it is
shared.

How do I get it so that it saves to the current directory
without having to type in all that information?

Thanks.
 
Mika,

Dim Path as String, Filename as String
Path = "H:\A\B\Sept 10 2003\"
Filename = "Sept10-1.xls"
ActiveWorkbook.SaveAs Filename:= Path & Filename, AccessMode:=xlShared

next file.........

Filename = "Sept10-2.xls"
ActiveWorkbook.SaveAs Filename:= Path & Filename, AccessMode:=xlShared

etc,..........

You could use Filename = Date & "-1.xls" which would give you
"9/10/2003-1.xls" or something similar, depending on your windows date
format.

HTH
Henry
 
Henry,

Thanks for your response. However, I would like the user
to, no matter where they put the file, to have it save to
that directory. I thought this was possible with
ActiveWorkbook.SaveAs Filename:="Sept10-1.xls",
AccessMode:=xlShared

but I don't think it is saving to the right director,
because when I reopen the file it is not shared.

Any further ideas?
Thanks...
 
Mika,

What directory do you mean by "save to *that* directory"
From your original post, I understood that the directory you want to save in
is "H:\A\B\Sept 10 2003\"
The user would have no choice, as the program will save itself.
In my original post, I used Path and Filename as variable names.
These are Excel keywords so are not allowed.
Sorry for the confusion.

Put this in the Workbook Before_Close event

Dim MyPath as String, MyFilename as String
MyPath = "H:\A\B\Sept 10 2003\" or whatever directory you want to save in.
MyFilename = "Sept10-1.xls" or whatever filename you want.
ActiveWorkbook.SaveAs Filename:= MyPath & MyFilename,
AccessMode:=xlShared
etc.

If you want to save the file back to the directory from whence it came
MyPath = ActiveWorkbook.Path

HTH
Henry
 
Back
Top