Solved

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

Posted on 2013-05-10
12
406 Views
Last Modified: 2013-05-10
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?
0
Comment
Question by:Dan Purcell
  • 5
  • 5
  • 2
12 Comments
 
LVL 12

Expert Comment

by:telyni19
ID: 39155661
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.
0
 

Author Comment

by:Dan Purcell
ID: 39155796
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?
0
 
LVL 12

Expert Comment

by:telyni19
ID: 39155878
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.
0
 

Author Comment

by:Dan Purcell
ID: 39156029
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
0
 
LVL 39

Expert Comment

by:als315
ID: 39156098
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
0
 
LVL 12

Expert Comment

by:telyni19
ID: 39156120
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.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Dan Purcell
ID: 39156256
Yeah, it worked! I like the idea that als315 had writing this as a macro.
0
 
LVL 12

Expert Comment

by:telyni19
ID: 39156380
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.
0
 

Author Comment

by:Dan Purcell
ID: 39156563
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?
0
 
LVL 39

Assisted Solution

by:als315
als315 earned 250 total points
ID: 39156641
You can copy files with this command in VBA:
FileCopy "N:\Agent photos\" & FileName, "C:\Users\danpurcell\Documents\Picture Names\" & FileName

Open in new window

You need same line to copy files to your site. Is it in intranet? Or you need ftp for uploading?
0
 
LVL 12

Accepted Solution

by:
telyni19 earned 250 total points
ID: 39156659
To copy the filenames from your folder into an Excel sheet, open an Excel file, put the following code in the ThisWorkbook tab of the file, then run the code to create a new sheet with the filenames. (Note that if the Dir function can't find the path you specify, it just won't return anything, so the routine will announce that it didn't copy any filenames, and it won't create a new sheet either.)

Sub FileNameCopy()
Dim strNew As Worksheet
Dim strName As String
Dim intFiles As Integer

Const strPath As String = "N:\Agent photos Need uploaded to site\February2013-Now\IR\color"

intFiles = 0
strName = Dir(strPath & "\*.jpg")
Do While strName <> ""
    If intFiles = 0 Then Set strNew = ActiveWorkbook.Worksheets.Add
    intFiles = intFiles + 1
    strNew.Cells(intFiles, 1).Value = strName
    strName = Dir
Loop
MsgBox "Copied " & intFiles & " filenames."
End Sub

Open in new window

0
 

Author Closing Comment

by:Dan Purcell
ID: 39157129
thank you very much!!!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

863 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

26 Experts available now in Live!

Get 1:1 Help Now