I feel as if I have asked this before but can't find it.
At work I have a folder which contains scanned forms that are all pdfs and are numbered to correspond with the number on the form.
It would be extremely handy to transpose these numbers into a column on a spreadsheet that I could then use to match up other details.
Is it possible to do such a thing and if so how? There are literally 1000+ files.
|
Some questions...
What operating system do you have?
What version of Excel do you have?
Can you run Macros?
Can you install software?
|
I am going to assume you are on Windows XP or later, have Excel 2007 or later and can run macros.
I give no warranties or guarantees for the instructions or code - use at your own risk!
1. On a new workbook go to File-->Options-->customise ribbon.
2. In the right hand box select Developer then click OK.
3. On the Excel worksheet select the developer tab
4. Click the Visual Basic button
5. On the visual basic window that pops up select Insert-->Module
6. Within the module window that pops up paste the following code and press the button that looks like a tape recorder's play button
Sub ListFiles()
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim ws As Worksheet
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set ws = Worksheets.Add
'Set the folder here
Set oFolder = oFSO.GetFolder("C:")
ws.Cells(1, 1).Value = "The files found in " & oFolder.Name & "are:"
For Each oFile In oFolder.Files
ws.Cells(ws.UsedRange.Rows.Count + 1, 1).Value = oFile.Name
Next
Set oFolder = Nothing
Set oFile = Nothing
Set oFSO = Nothing
End Sub
|
You will need to change the line:
Set oFolder = oFSO.GetFolder("C:")
To the correct path for your folder e.g.
Set oFolder = oFSO.GetFolder("My Files:DataFolder")
|
|
Cheers Zippy I will try that when back at work.
|
Its Windows 8 with Excel of 2010 I think.
I personally don't know what to do with macros but I have run spreadsheets that have them in it so our system can certainly do them.
We can install software (well our IT folk can)
|
if they are sequentially numbered
ie pdf 0001 to pdf 1000
www.excel-easy.com/introduction/range.html#fill-range
|
They are numbered but not totally sequentially as some forms are missing - this is th epoint of the exercise is to try and track out of the 7000 forms we have that have been scanned, which ones are missing.
I can do a separate csv export from the database that has all the donor's details that have been entered from the form.
So my plan was to do a csv which would basically be numbered 1 to 7000 and then somehow be able to pull over the info that shows which one of these has a scanned form. Obviously can do it all manually but was looking for something quicker.
|
This might also work and is a lot simpler...
Open a DOS prompt using the CMD.exe command as administrator.
Type DIR "Folder Name" >mytextfile.txt
I understand that this should copy the names of the files in the directory to a text file.
Unfortunately I cannot test it.
You can then import the text file to Excel.
|
|
will that import them as a number per line?
|
If you use the dir /b ('bare') option you will eg.
dir /b folder*.pdf > files.csv
although you also get the .pdf after each number.
EDIT: if you get the option to specify the field separator when importing the file, use . then the pdf will be in a separate column which you can just delete.
Last edited by: Focusless on Sat 16 May 15 at 20:01
|
>> dir /b folder*.pdf > files.csv
There should be a backslash between 'folder' and the asterisk - thought I'd managed to get it in but apparently not :)
|
|
I don't think the forum software likes backslashes.
|
If it's a fixed number of characters before or after the bit you want you can easily trim the cell without resorting to code.
So for instance if you end up with the whole file name in one column, (for instance file0001.pdf, file0010.pdf) and you just want the numeric bit (0001, 0010) in the cell then
- in the next column you can say =left([source cell],8) which would get you the file0001
- in the next column say =([previous column],4) which would get you just the 0001
You can the copy that formula down by dragging.
Then to tidy it up you can copy the entire new column, and Paste - Values that is, not regular paste!) to a new column, then delete your workings out columns.
If you want to send me the DIR output I will do the rest if you want - get me via the Mods address.
|
>> I don't think the forum software likes backslashes.
It's to do with the HTML commands.
If you put two backslashes in where you would normally put one, then IIRC it should appear correctly as only one of the two will get removed.
i.e
c:\Users\Documents\test
|
>> If you put two backslashes in where you would normally put one, then IIRC it
>> should appear correctly as only one of the two will get removed.
Yeah, I did that. But then IIRC I went back and did an edit, and I suspect doing that removed one of them.
Test: xy
Yep - in the edit you have to add the 2nd slash again.
Last edited by: Focusless on Sun 17 May 15 at 17:27
|
I knew you'd think of it.... but the edit messes it up. Oh well.
The suggestion to use a DOS string of commands should sort Bobby... We can help if Bobby (ex G) is in need of help.
|
Easy way for lazy people (me)
- open Windows Explorer and navigate to the directory, so that the list of files you want is shown in the right hand pane whilst the list of directories is on the left.
- select all the files (Ctrl-A is as good a way as any)
- hold shift down (important) and right click on the selected files
- choose "copy as path" from the menu which appears
- Go to Excel and click on the cell where you want the list to begin and paste (Control-V is easy)
And there you go. Sort as you wish.
Last edited by: No FM2R on Mon 18 May 15 at 00:13
|
No "copy as path"in my Windows 7, do you have some product which might have added it to the menu? Easy solution if it works!!
EDIT It does work, I missed the Important bit - holding down the shift key. Well I never knew that, thanks.
Last edited by: smokie on Mon 18 May 15 at 08:05
|
Cheers for that NFM2R - even the excel gurus at my work didn't know that!
Now when I have done this, it has transposed the full file path over in each line as well as the file name ie
F:PUBLIC\blah\blah\blah\592.pdf
Do I just follow the methods mentioned previously to remove the file path info?
And lastly , Rob, no idea how or when the G disappeared from my name!
Last edited by: Bobby on Mon 18 May 15 at 10:00
|
The user BobbyG still exists too. Hence me wondering briefly at one point if this Bobby user was someone else :-)
The username Bobby was registered on February 22nd 2010. BobbyG was registered the same day and was last used on April 10th 2015.
Maybe you've logged in with a different username since April 10th.
|
>>Do I just follow the methods mentioned previously to remove the file path info?
Was it me I would just use "replace" and replace the path name with blank for the whole worksheet.
Last edited by: No FM2R on Mon 18 May 15 at 10:26
|
This formula finds the last backslash and returns the text after it.
=RIGHT(A1,LEN(A1)-FIND("|",SUBSTITUTE(A1,"\","|",LEN(A1)-LEN(SUBSTITU
TE(A1,"\","")))))
|
>>Easy way for lazy people (me)
Superb, there really is a hundred ways to skin a cat.
I will use this method for one off jobs.
|
Praise indeed.
Glad to be able to help, goodness knows I get enough help.
|
File names transposed to spreadsheet - zippy
