Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

insert the excel file name into a range of cells

Posted on 2014-10-06
8
Medium Priority
?
763 Views
Last Modified: 2014-10-19
I would like to have a macro that would insert the name of the open file (example: DMAP_10-14.xlsx) into a range of cells
0
Comment
Question by:Dan Purcell
6 Comments
 
LVL 12

Expert Comment

by:James Elliott
ID: 40363934
Open your VB Editor (Alt+F11)

Insert a new module

Paste the following code

Option Explicit

Public Function MyName() As String

    MyName = ThisWorkbook.Name

End Function

Open in new window


Save your workbook.

Use in any cell(s) like this:

=MyName()
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 1000 total points
ID: 40363936
Why not use a formula instead of VBA?

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

Open in new window

0
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 1000 total points
ID: 40364028
I like Phillip's solution, but if you indeed need the name of the active file inserted as a value in cells, then the VBA line would be:
Range(range_reference).Value = ActiveWorkbook.Name
where range_reference is either a specific cell (ex., "A2") or range of cells (ex., "A2, B4").  You could also use the cells(r,c) method if you wished.

-Glenn
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Closing Comment

by:Dan Purcell
ID: 40364130
Thanks, I did want it as a macro!
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40364334
Eh?
0
 

Author Comment

by:Dan Purcell
ID: 40373107
I tried running it and couldn't get it to work. You can give him point's, I certainly didn't mean anything personal.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question