MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

To capture File name

  Asked By: David    Date: Oct 30    Category: MS Office    Views: 1707

I am looking for a macro, which can save a file to a
different location with the same name automatically. So far I am able to figure
out that the file name is generally passed as a command line argument. something
like \"excel myname.xls\" so what I want is that when someone write
something like \"excel myname.xls\" on command line, the macro should save the
file to a different location like \"D:\\temp\\myname.xls\"



3 Answers Found

Answer #1    Answered By: Viren Rajput     Answered On: Oct 30

It is very difficult to understand what you are asking.

Command line  arguments will almost always include the full path of the Excel
workbook - otherwise Excel will not be able to find it to open it. So your
example command  line isn't very accurate.

A couple of days ago, you asked a very similar question on this group, and I
replied to it. You haven't indicated whether this information was
appropriate or mis-directed. In fact, you didn't even acknowledge the reply
at all. Why not?

Please describe in much more detail EXACTLY what you want to happen.

Answer #2    Answered By: Cheryl Murphy     Answered On: Oct 30

What you are looking for must be something like this

Dim strFile As String
Dim d
Set d = CreateObject("Scripting.FileSystemObject")
strFile = "C:\File.xls"

MsgBox d.getfilename(strFile)

This is just code written for displaying the file  name when a user input based
path and filename are provided. i trust you can fill in the details for doing
what you needed to do.

Answer #3    Answered By: Adalricus Fischer     Answered On: Oct 30

I think your terminology is inconsistent.
Or at least confusing.
You've posted a question to an Excel VBA group.
Which would imply that you wish a solution using VBA in Excel.
Yet you mentioned using a "command line", which generally refers to
the command  issued to LAUNCH an application (like MSExcel), not
something WITHIN Excel.

I guess if we knew your PURPOSE, we can help.
My boss learned some time ago that if he asks me for something, I'll
give him exactly what he asks for. Even though he doesn't know what
he's talking about and ends up getting something does not serve his
purpose. He's learned that he needs to tell me what he wants to
accomplish, and I'll provide a solution.

Putting all of this together, Are you trying to provide a file  that,
when opened, saves itself (with the same name) in a local folder (or
user's personal folder), then remains open?

If so, you can use:
Option Explicit
Sub auto_open()
Dim TempPath
TempPath = "C:\temp\"
ActiveWorkbook.SaveAs (TempPath & ActiveWorkbook.Name)
End Sub
If you're wanting to save  a COPY, but stay in the original file,
look at SaveCopyAs.
If you want to make sure the existing file (in the temp folder) is
overwritten, you'll have to set up a filesystemobject, use fileexists
and delete to remove it first.

If you'll provide a bit more by way of explanation, I'm sure you can
get the help you need.

Didn't find what you were looking for? Find more on To capture File name Or get search suggestion and latest updates.