Macro question
strfilename = format(now(), "mmm") & "MyFile.xls"
format(now(),"mmm") puts the 3 letter abbreviation for the month in front
of the filename.
strfilename = "MyFile.xls"
Would not do that, but then, wouldn't all your file names be the same? (it
they are, the you would want to overwrite the existing file).
--
Regards,
Tom Ogilvy
"SJC" wrote in message
...
I hope I am not being a pest, but I had one last small question--your
command
lines worked perfectly, but the letters "Mar" appear in front of the file
name--is there any way to get rid of that? Thanks once again.
"Jim Thomlinson" wrote:
Ok I am just a little bit lost in exactly what you want... The file you
are
sending is going to create a new workbook and you want to save that
workbook
somewhere that you will be able to get to it. Paul had a great
suggestion on
the path. This one will work and should not cause any problems... How
about
something like this
dim strPath as String
dim strFileName as string
strpath = Application.DefaultFilePath & "\"
strfilename = format(now(), "mmm") & "MyFile.xls"
activeworkbook.saveas strpath & strfilename
That is untested but it should be failry close...
HTH
"SJC" wrote:
I think the 'thisworkbook.path' would work--can I still designate a
name for
the file within the programming using this command? If so, how would
that
look? I can successfully place the 'thisworkbook.path' where it
belongs, but
I can't figure out a way in which I could name the new file. When the
macro
is run, it transfers a portion of the data into the new file. The new
file
is what I would like to rename to a universal path. I apologize--some
of
this is new to me.
"Jim Thomlinson" wrote:
You could use either thisworkbook.path or application.path...
Probably
thisworkbook, which is the directory where they have saved your
workbook. If
it is not saved then you could run into a problem... In that case
application.path which returns where the excel program is saved...
End users
may not like this...
HTH
"SJC" wrote:
Thank you for your advice. Do you have any other suggestions on a
universal
place to put this file? Unfortuneately, there will be about 200
users from
dozens of organizations, so I will not be able to confirm the
administration
of the c: drive for all of them. Is there a way to get to the "My
Documents"
folder or "Desktop" that is universal to all operating systems?
"Jim Thomlinson" wrote:
You can use the dir function to determine if a file (or
directory) exists.
One thing to be aware of is that it is possible that some users
will not be
able to create files in their root of C. Often administrators
lock the root
down to keep users from accidentally mucking up their systems.
That is
something to confirm before you begin.
HTH
"SJC" wrote:
I have created a workbook that contains several
spreadsheets--one for each
month of the year. I have about 200 novice users that will be
recording some
monthly data and sending the report to me. Within each
spreadsheet, I have
included a macro which will export what data I need and save
it into a folder
so that they can easily find the file and email it to me. The
users have
operating systems anywhere from Windows 95 to XP, so it was
difficult to find
a folder to have these files saved to within the macro that
fit all of the
operating systems. So, in the macro, I opened a new folder
and saved the
file under the new folder to the C:\ drive, which would be
universal to all
operating systems. So the way I have it set up is to have the
May file be
the macro that opens up the new folder and stores the first
file. Then
future month macros saves the new files to that same folder.
The problem is
that if I have a user that doesn't use the program until
August, that macro
obviously won't create the folder and cannot find the right
path. Is there a
way to program the macro to create the new folder only if it
hasn't been
previously created? Any other options to solve this problem?
I apologize if
I am sounding confusing--I am fairly new to this macro
business. Please
offer any recommendations. Thanks.
|