Link to home
Create AccountLog in
Avatar of Dan Purcell
Dan PurcellFlag for United States of America

asked on

Move file names to a Excel. Just the name, not file itself

I have a monthly project where I take a couple dozen .jpg files and copy and paste the file name into a spread sheet (Excel). To do this I'm currently select rename, copy the name, and paste it into the Excel cell. Is there a way to mass copy and paste just the file name?
Avatar of telyni19
telyni19
Flag of United States of America image

The easiest way I've found is use a commandline window (Run CMD from the Start menu) to navigate to the folder and then run the following command:
dir *.jpg /a:-d/b >> dir.txt

This should save a list of just the filenames of the jpg files in the folder to a text file called dir.txt (you can call it something else if you want, too). Then open the text file and copy the text you want into Excel.
Avatar of Dan Purcell

ASKER

So if the .jpg are in N:\Agent photos Need uploaded to site\February2013-Now\IR\color and I want to save then in C:\Users\danpurcell\Documents\Picture Names what would that look like?
Here is a step-by-step breakdown of how I would execute the solution I suggested above.

1. Open a command window - type CMD into the Run field in the Start menu.
2. Navigate to the folder you mentioned by switching to the N: drive and using CD commands:
N:
cd Agent photos Need uploaded to site
cd February2013-Now
cd IR
cd color

3. Run the dir command:
dir *.jpg /a:-d/b >> dir.txt
4. Close the command window and open the folder directly in Windows
5. Open the dir.txt file.
6. Select the filenames you want and copy them (Ctrl+C or the Copy menu command)
7. Open the Excel file you want them to be in (Picture Names.xls?)
8. Select the first cell where the filename should go, and paste the filenames (Ctrl+V or the Paste menu command).
Now you have filenames in your Excel file.
I will be the first to admit the CMD isn't my strong point! Attached is a screen shot of trying to move to the N:/ drive. My N:/ drive is mapped to our server
CMD-screen-shot.docx
You can get file names from folder with dir function in vba:
FileName = Dir("N:\Agent photos\*.jpg")
Do while FileName <> ""
      ' your code here
     'Name "oldPath + Name", "newPath + name" - for move
     'FileCopy "oldPath + Name" , "newPath + name" - for copy
      FileName=Dir()
Loop
You've got a really long directory name with spaces. Try surrounding the directory name with quotes, and make sure you're typing it exactly, or try typing just dir after the N: command and see how the system is displaying your agent photos directory. If you aren't getting into that directory, don't just keep going with the next command: the cd commands are sequential, so you can't just skip one and expect the path to work properly. You also tried to merge the last cd command with the dir command in one line, which the system can't understand.

An alternate way to get into the directory is to use the chdir command with the /D switch, which shifts you directly to a particular path on a different drive. Here's the sequence using that command:

1. Open a CMD window.
2. Change directly to your photo directory:
chdir /D "N:\Agent photos Need uploaded to site\February2013-Now\IR\color"
3. Save off the filenames with the dir command:
dir *.jpg /a:-d/b >> dir.txt
4. Open the text file and copy the names into Excel.
Yeah, it worked! I like the idea that als315 had writing this as a macro.
I'm glad it worked. You'll have to pick which solution works better for you, or give credit to both if you like both.
I can't seem to be able to translate the CMD  to a VBA code (errors) Could one of you fill in the blanks on the code?
SOLUTION
Avatar of als315
als315
Flag of Russian Federation image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
thank you very much!!!