Solved

insert the excel file name into a range of cells

Posted on 2014-10-06
8
450 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
8 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 250 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 250 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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Outline Suppose you have some simple text based data in Excel that you would like to display as a PowerPoint presentation. Of course it would be possible to write some fairly complex VBA code that created a new slide for each line of the Excel data…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
An overview on how to enroll an hourly employee into the employee database and how to give them access into the clock in terminal.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now