return file name

What do I put in a cell to get it to return the file name? Is there a way, 
inversely, to get the file to adopt a default name equal to the content of a 
cell when saving? 


0
chuck
4/16/2010 6:21:13 PM
excel 39879 articles. 2 followers. Follow

8 Replies
851 Views

Similar Articles

[PageSpeed] 49

chuck wrote:
> What do I put in a cell to get it to return the file name? Is there a way,
> inversely, to get the file to adopt a default name equal to the content of a
> cell when saving?

To get the file name...

=MID(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),
FIND("[",CELL("filename",A1))+1,255)

File name and path...

=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1)
)-1),"[","")

Don't know about naming a file for the contents of a cell.

-- 
Jordon
0
Jordon
4/16/2010 6:43:13 PM
ActiveWorkbook.SaveAs Filename:="C:\Gordstuff\" & _
            Sheets("Sheet1").Range("A1").Value


Gord Dibben  MS Excel MVP


On Fri, 16 Apr 2010 14:21:13 -0400, "chuck" <chuckf@teklaoperator.com>
wrote:

>What do I put in a cell to get it to return the file name? Is there a way, 
>inversely, to get the file to adopt a default name equal to the content of a 
>cell when saving? 
>

0
Gord
4/16/2010 10:50:38 PM
"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:lcqhs51p9ueq534u0cpbp52uh7f11nk1kh@4ax.com...
> ActiveWorkbook.SaveAs Filename:="C:\Gordstuff\" & _
>            Sheets("Sheet1").Range("A1").Value
>
>
> Gord Dibben  MS Excel MVP

Thanks Gord but I am quite new to Excel. What do I do with this? Include it 
in a macro? 


0
chuck
4/21/2010 12:38:11 PM
The other responder showed how to get the filename into a cell.

My code is a macro and will save the file using whatever is in Sheet1 A1 as
the saveas name.

Wrap a Sub, End Sub around it like

Sub Saveit()
    saveas code from below
End Sub

Place the macro in a general module in your Personal.xls


Gord

On Wed, 21 Apr 2010 08:38:11 -0400, "chuck" <chuckf@teklaoperator.com>
wrote:

>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
>news:lcqhs51p9ueq534u0cpbp52uh7f11nk1kh@4ax.com...
>> ActiveWorkbook.SaveAs Filename:="C:\Gordstuff\" & _
>>            Sheets("Sheet1").Range("A1").Value
>>
>>
>> Gord Dibben  MS Excel MVP
>
>Thanks Gord but I am quite new to Excel. What do I do with this? Include it 
>in a macro? 
>

0
Gord
4/21/2010 1:48:37 PM
"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:n90us51h14lmuq0lomfesdr520tflrrolc@4ax.com...
> The other responder showed how to get the filename into a cell.
>
> My code is a macro and will save the file using whatever is in Sheet1 A1 
> as
> the saveas name.
>
> Wrap a Sub, End Sub around it like
>
> Sub Saveit()
>    saveas code from below
> End Sub
>
> Place the macro in a general module in your Personal.xls
>
>
> Gord

 Thanks again Gord. I was most of the way there with that. I just wasn't too 
clear on what to do when it said that I could not record a macro on this 
type of workbook. I've got it working now. I created a button to run the 
macro and saved the workbook as a macro enabled workbook.
Is there a way to make the button text be the content of a cell, for 
instance name the button "Save As & (cell D31 content)" ? 


0
chuck
4/21/2010 3:27:29 PM
Will the text in D31 be changed manually on occasion?

You could use sheet event code to change the Button Text.

Assumes the button was created using Forms Toolbar

Private Sub Worksheet_Change(ByVal Target As Range)

Me.Shapes("Button 1").Select       ' edit to your button name

    Selection.Characters.Text = "Save As " & Range("D31").Value
    Me.Range("D31").Select
End Sub

Right-click the sheet tab and "View Code".  Copy/paste the code above into
that sheet module.

Alt + q to return to Excel.

Enter a value in D31 and see Button 1 text change.


Gord

On Wed, 21 Apr 2010 11:27:29 -0400, "chuck" <chuckf@teklaoperator.com>
wrote:

>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
>news:n90us51h14lmuq0lomfesdr520tflrrolc@4ax.com...
>> The other responder showed how to get the filename into a cell.
>>
>> My code is a macro and will save the file using whatever is in Sheet1 A1 
>> as
>> the saveas name.
>>
>> Wrap a Sub, End Sub around it like
>>
>> Sub Saveit()
>>    saveas code from below
>> End Sub
>>
>> Place the macro in a general module in your Personal.xls
>>
>>
>> Gord
>
> Thanks again Gord. I was most of the way there with that. I just wasn't too 
>clear on what to do when it said that I could not record a macro on this 
>type of workbook. I've got it working now. I created a button to run the 
>macro and saved the workbook as a macro enabled workbook.
>Is there a way to make the button text be the content of a cell, for 
>instance name the button "Save As & (cell D31 content)" ? 
>

0
Gord
4/21/2010 4:21:39 PM
"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
news:<us8us5hrkmtjq1gk43rkuj1urlqkc1p8vm@4ax.com>...
> Will the text in D31 be changed manually on occasion?
>
> You could use sheet event code to change the Button Text.
>
> Assumes the button was created using Forms Toolbar
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Me.Shapes("Button 1").Select       ' edit to your button name
>
>     Selection.Characters.Text = "Save As " & Range("D31").Value
>     Me.Range("D31").Select
> End Sub
>
> Right-click the sheet tab and "View Code".  Copy/paste the code above into
> that sheet module.
>
> Alt + q to return to Excel.
>
> Enter a value in D31 and see Button 1 text change.
>
>
> Gord

That works beautifully. I really need to learn more about using VB apps in 
MS Office documents. Thanks very much again Gord. 


0
chuck
4/21/2010 9:09:40 PM
You're welcome.

Yes.  VBA is a great tool.

Gord

On Wed, 21 Apr 2010 17:09:40 -0400, "chuck" <chuckf@teklaoperator.com>
wrote:

>"Gord Dibben" <gorddibbATshawDOTca> wrote in message 
>news:<us8us5hrkmtjq1gk43rkuj1urlqkc1p8vm@4ax.com>...
>> Will the text in D31 be changed manually on occasion?
>>
>> You could use sheet event code to change the Button Text.
>>
>> Assumes the button was created using Forms Toolbar
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>>
>> Me.Shapes("Button 1").Select       ' edit to your button name
>>
>>     Selection.Characters.Text = "Save As " & Range("D31").Value
>>     Me.Range("D31").Select
>> End Sub
>>
>> Right-click the sheet tab and "View Code".  Copy/paste the code above into
>> that sheet module.
>>
>> Alt + q to return to Excel.
>>
>> Enter a value in D31 and see Button 1 text change.
>>
>>
>> Gord
>
>That works beautifully. I really need to learn more about using VB apps in 
>MS Office documents. Thanks very much again Gord. 
>

0
Gord
4/21/2010 10:52:14 PM
Reply:

Similar Artilces:

Customer Returns
I'd like to know if there someone has written a RMS report that shows the customer account number, customer's name (last, first), and other customer statistics that INCLUDE pieces purchased and pieces returned. Or, if someone with more knowledge of SQL would care to share their coding expertise to accomplish the same, it would be appreciated. ...

Return-Path: <> from Postmater
Hi, I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive bad mail the response is made from postmaster@mydomain.com but the return-path field is blank. With some anti-spam filter this Email is Junk. Return-Path: <> Anybody know how to specifeid a return-path to the NDR mail? _____________ Thank you Sebastien On Fri, 4 Nov 2005 12:48:04 -0800, "Sebas" <Sebas@discussions.microsoft.com> wrote: >Hi, > >I got Exchange 2003 SP1 on Windows 2003 in Native Mode and when I receive >bad mail the response is made from postmaster@mydomain.c...

Is there a way to color code Outlook Contact File Folder Labels?
Is there a way to color code Outlook Contact File Folder Labels? No. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Join OneNote Tips mailing list: http://www.onenote-tips.net/ "isailhbca" <isailhbca@discussions.microsoft.com> wrote in message news:4DBD10F7-FAA0-4102-A4C3-E2697908EFE8@microsoft.com... >...

Recovering a publisher file from its .tmp file
I am not familiar with Publisher 2002 and its workings. One of my bosses was creating a company news letter and while inserting, rather large, pictures she crashed and it saved all the work as a .tmp. I tried renaming the file to a .pub with no avail. I am primarily interested in recovering the text. Any hints or suggestions would be greatly apprieciated! Thanks, Jim Try Open, files of type, scroll down to "recover text from any file" and browse to the file. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Jim&quo...

Editing Package Files
I have a VBA modified report that I need to distribute to users. Our report dictionary is shared on the network but the VBA code needs to be installed locally. Could I take the package I created, install it once to update the shared reports dic then edit the package and delete out the report modifications part? That would leave the VBA update part to install on the clients. Does this sound feasible? Thanks, DavidF. david, that sounds like a lot of work for no particular reason. the vba and dictionary changes know where they should go. so there shouldn't be any reason you canno...

links to excel file question
Hi all, I have a hyperlink in an Outlook 2000 email that links to a Microsoft Excel 2000 file that contains vba code. The Excel program generates the error however it's based on how the link is handled in Outlook. When I click the link through the preview window in Outlook everything works fine. The link looks like this - <\\sfna-fs4\hrs\shared\ctm training\ctmtrainingsignup.xls> When I open the email (i.e. view the email in it's own window) and then click the link I get the runtime error in excel. The link display look likes this - \\sfna-fs4 \hrs\shared\ctm trai...

SaveAs wk3 file type error message
I'm trying to convert an Excel worksheet to a Lotus 123, wk3, file format. I've had to use Excel 2003 as Excel 2007 no longer appears to offer the Lotus 123 file formats. When I do the SaveAs command in Excel 2003 an error is generated saying "You are attempting to save a file type that is blocked by your registry policy setting." Does anyone know a workaround for this issue? Ken Error message when you try to save a file in Excel 2007 or in Excel 2003: "You are attempting to save a file that is blocked by your registry policy setting" http://s...

Site for SOP Returns
Is it possible to have a different default Site ID for Returns than Standard Orders? Yes. You must make sure to change the site when performing a Return Transaction. -- Richard L. Whaley Author / Consultant / MVP 2006-2008 Documentation for Software Users Get our Free Tips and Tricks Newsletter and check out our books at http://www.AccoladePublications.com "Everton Raymond" wrote: > Is it possible to have a different default Site ID for Returns than Standard > Orders? Sorry, I did not see the word "default" in your post until after I r...

I need help with VBA and a CSV file output
I am using Excel 2003 in XP Pro. I am trying to export/saveas one tab from a workbook that contains one tab and one column of data into a CSV file. My data is in column A in multiple rows and looks like the following on each row: 0,123456,0,0,99,0,0,0,0,0,0,0,0,0,5,11/11/2009,0,0,0,0,0,0, I have everything working fine except for one large issue, excel adds " " to the begining and end of each cell of data and looks like the following: "0,123456,0,0,99,0,0,0,0,0,0,0,0,0,5,11/11/2009,0,0,0,0,0,0," These quotes are an issue for what this file will be us...

Query regarding A,B,C char. width values returned in 16-bit and 32-bit OS
I am trying to retrieve individual character widths of a string (from a TrueType font) and I tried using GetCharWidth (which weirdly works for TrueType fonts too tho' MSDN says otherwise) and/or GetCharABCWidths to retrieve the A,B and C spacings. Please note that I have selected the TrueType font (hFont) into the Device Context (hDC). I used GetCharABCWidths. Problem is this: GetCharABCWidths returns correct A,B and C widths in Win 98 and incorrect (much lesser A,B and C widths such that the characters OVERLAP) in other 32-bit OS (NT, XP). The effect is seen across all TrueType fonts, th...

Starting Excel from file
Can anyone help? I've just started getting a problem loading excel from a file. It starts just fine from the start menu etc. and I can open files from within Excel. However, if I click on (say) an excel file icon in My Documents or on one of my saved icons on the desktop then Excel loads fine but NOT the file!! This is really frustrating as it means I have to go through the process of loading excel separately every time I wish to use it. I Use Office XP + Windows XP PRO. I have done a system restore to before the problem occurred - no luck. I have completely uninstalled Office...

Outlook 2002 and file attachments
I am unable to receive any file attachments in Outlook. They are all blocked. How does one go about changing the default settings to allow the attachments to come through. I have a firewall and virus protection to stop any problem files. See if the information on the following page helps: http://www.slipstick.com/outlook/esecup/getexe.htm -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the message thread. *** In news:031201c3b6f5$d725fe60$a501280a@phx.gbl, Toody wrote: > I am unable t...

Transferring Outlook Files to New Computer
Just bought a new Dell laptop with XP Professional. How do I transfer my old Outlook files including address book from Outlook 2000 to new computer Outlook 2000? On Sun, 03 Aug 2003 17:26:15 GMT,"Jay F." <jfriesel@optonline.net> penned this whopper in news:00b701c359e4$578c64a0$a301280a@phx.gbl: > Just bought a new Dell laptop with XP Professional. How > do I transfer my old Outlook files including address book > from Outlook 2000 to new computer Outlook 2000? File > Import/Export > export to .PST personnal folders file. Move that to new machine and impo...

Report for Returns
Anyone know a why to report on frequency of customer returns -- so we can see if any customers are making habit of this? Thanks and regards, Rich hitman, Try running a detailed sales report filtered to only negative sales displayed. Good luck, Matt "hitman" <hitman@discussions.microsoft.com> wrote in message news:0B3EE780-2655-45B4-B3C6-1F5D35FFA2A7@microsoft.com... > Anyone know a why to report on frequency of customer returns -- so we can > see > if any customers are making habit of this? > > Thanks and regards, > > Rich ...

Store the full path and document name in a hyperlink
Hi I need to create an Access database to track a number of documents that are created and stored during the tendering process. I have created a number of tables with hyperlink fields that will store the information eg Tender proposal document (a Word doc); Tender budget document (an Excel book) and company profiles (pdf) from companies submitting tenders. To create the hyperlink to each of these documents in the form, at the moment I right click, choose Hyperlink, Edit Hyperlink and navigate to the required document and select it. The path and filename is then stored in the field, which is ...

Returning the Beginning of the week
Returning the beginning of the week. e.g. if the date == 02/03/04 (Friday 2nd March 2004) the beginning week for that date == 01/03/04 (Thursday 1st March 2004) Hows does one go about achieving this? Regards -- Rizitsu ------------------------------------------------------------------------ Rizitsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=15840 View this thread: http://www.excelforum.com/showthread.php?threadid=314968 This formula will take a date in cell A1 and give the start of the wee in B1 * assumes that monday is the start of the week, so i...

Export Table Records to txt file
Hi all, I have Table "T_Data" in my database. In "T_Data" I have 220000 records. I need to export them into txt files. I need some kind of macro which should export chunk of 60000 records into one txt file and save that file in folder called "Data" on "C:\" drive. Macro should also put numbers at the end of each txt file name. Like (See below) Data 1.txt Data 2.txt Data 3.txt etc=85.. I'll be very greatful if any friend can help me on this as I am struggling on this. ...

you cannot save to this file format when the VBA project is protected
Urgent !! How can we abe to save the worksheet and at the same time still able to protect the VBA project coding for viewing ? This development is just like creating a Template with many macros programming and you do not want the users go into the project and modify your programs. It can be done before, why in Excel Visual Basic version 6.3 when you protect the project for viewing , you workbook can not be saved any more, unless you unprotect your project coding ? Your kind solution at this matter is greatly appreciated. Thanks, jchan -- Message posted via http://www.officekb.com This is n...

Large file size in Excel
I've got a worksheet that is blank, and the Ctrl-End key combination jumps to cell A1. I originally pasted 5 colums by 138 rows of data into this spreadsheet, then erased the columns completely by using 'Ctrl+-'. I've also selected the entire sheet and used Edit->Clear All. All this to no avail. I have an empty spreadsheet that takes up 900 KB, but zips to 30 KB Any suggestions on how to reduce the file size is welcome. "Jon" <anonymous@discussions.microsoft.com> wrote in message news:69DAD6D5-9876-43C9-B11B-7AC6C19F9CFA@microsoft.com... > I've g...

RESCUE PST.FILE OL2000
This is a multi-part message in MIME format. ------=_NextPart_000_0025_01C49E2B.2732A7B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I need to repair/reinstall XP but want to rescue the pst.file on the = C-disk. The Hard disk is now a slave on another computer but I'm not = allowed access to DOC/RICHARD/APPL/MS/OL.=20 Is this because it had a password on the user ?? THX!! ------=_NextPart_000_0025_01C49E2B.2732A7B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HT...

File Names
What is the name of the Sent file in Outlook. I accidently deleted it and want to retrieve it. Thanks There is no file specifically for a Sent item. All data is stored in a pst-file. Is it no longer in your Deleted Items folder? -- Roady [MVP] www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... Tips of the month: -Create your own fully customized Toolbar -Creating a Classic View in Outlook 2003 Subscribe to the newsletter to receive news and tips & tricks in your mailbox! www.sparnaaij.net (I changed my reply add...

xpath
I'm using the following xpath in .net //Station[@name="Station1"]/WI/Boards/Board[@sn="12345"][not(@finish)]") to test if there is a Board element with NO "finish" attribute. If there is, I need to get the @title of the WI element...but haven't been able to figure it out yet (and yes, I do user XpathVisualizer!). Also, how can I test if the above xpath results in no/null nodeset? It doesn't seem to work for me unless I first set an attribute.value string to test (e.g., Dim unfinished As String = n1.Attributes.GetNamedItem("start").Va...

Converter failed to save file
I've recently installed Office 2007 Enterprise package and can no longer read pdf files either directly from a web site or in an email from another PC. There seem to be several references to this issue on Google but no-one seems to have a definitive answer. Can anyone help please? -- A. T. Hen Do you get an error message? If so, what does it say *exactly*? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Attila the Hen" <AttilatheHen@discussions.microsoft.com> wrote in message news:A...

Alpha sort names/numericals
I have 2 columns. 1st column has names, 2nd has numerical values relevant to the names. How can this data be alpha sorted and keep the same name/numerical data. data/sort "dane" <dane@discussions.microsoft.com> wrote in message news:A9565E66-9FC0-48BF-A06B-FF51E184A8A1@microsoft.com... > I have 2 columns. 1st column has names, 2nd has numerical values relevant to > the names. How can this data be alpha sorted and keep the same name/numerical > data. Hi Dane, Please state clearly what the problem is. If you sorted and got the names sorted and the numbers did not...

return
Capability of the system to automatically change the status of the PO to change order, and add line items that have been returned. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.ms...