I have a directory of some 200 filenames which I want to put into a range of cells. Is there a simple way to do this?
I would appreciate an easy to follow set of instructions.
I have a directory of some 200 filenames which I want to put into a range of cells. Is there a simple way to do this?
I would appreciate an easy to follow set of instructions.
This is a macro that was given to me to do the same thing, perhaps you can get it working for you...
Please Login or Register to view this content.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
I'm afraid I do not know how to use the code. Perhaps someone could fill in the blanks for me?
The directory is a list of names in a folder "HorologicalBooks". I want to paste them into a new worksheet range "A1-A200".
Are you OK with a formula solution (macro-enabled)?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Perhaps my abilities are best summed up by the fact that I don't even understand the question! I use Excel every day to maintain accounts and catalogues but have never got involved with macros and such. I would like the simplest method of achieving what I want.
Many thanks for your help and patience.
Ha ha...
try this and let me know if/when you get stuck!!
Set up a named range called fl using this formula (CTRL F3)
=FILES("your file path to the target folder, starting from C, goes here\*")&T(NOW())
Save as macro-enabled file. Close & reopen. Then in A1,
=IFERROR(LEFT(INDEX(fl,ROW()),LEN(INDEX(fl,ROW()))-5),"")
drag down.
Last edited by Glenn Kennedy; 08-09-2015 at 05:15 AM.
Where and how do I do this -
"Set up a named range called fl using this formula (CTRL F3)
=FILES("your file path to the target folder, starting from C, goes here\*")&T(NOW())"
When I press CTRL F3 nothing happens! The above implies Windows, I am using a Mac. My file path is /Users/ronkirkpatrick/Desktop/HorologicalBooks/
drag down?
Mmm. this may cause problems all the way through. We'll see.
fn+command+F3
should get you there on a Mac.
CTRL F3 brings up the Dock, fn CMD F3 clears the screen! CMD F3 brings up Mission Control.
What are we trying to do? Where are we trying to create a macro?
This is no doubt relevant -
With Excel 2011, you can continue to use and maintain Excel 4.0 macros (XLM macros) that have not been upgraded to Visual Basic® for Applications (VBA). Although you cannot record new Excel 4.0 macros in Excel 2011, you can run and modify them.
The Excel 4.0 Macro Reference provides information including syntax and examples for the macro functions. You can find more information about the Excel 4.0 Macro Reference on the Microsoft Web siteClick this link to open a browser window. (www.microsoft.com/downloads).
Insert/Name/Define????
Brings up
Screen Shot 2015-08-09 at 11.17.33.png
OK. Put fl in the Names and the formula in the refers to bit.
There's still hope!!
Putting fl in the Names doesn't work, it doesn't reference anything! It won't accept the path saying it is not a name.
I know nothing about Macs (as you can tell!!) - but doesn't the drive have a name - like C, or similar?? Does the path really start with /Users??? Surely not...
On a Mac discs have names (you could name a disc C but not usual). As Users is on the System drive /Users is an appropriate path.
I thought that as Excel needed to know where to find the list of names I would copy it, paste it into a cell in Excel and reference it. I didn't get any further than that because when I pasted the list (of 76 names) into C1 the names appeared in C1:C76 which is exactly what I have been trying to do!!
Is there a secret function in Excel 2011?
I wish to thank you very sincerely for your help and support, it is very much appreciated.
Ron
ha ha. If it had worked on a Mac... the approach I was advocating would have been dynamic - add more files, more names would have appearred automatically. However you're there.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks