How to extract filename from a folder which is on a netwrok drive

[kevvukeka]

Hi All,

I have few hundreds of pdf on a network folder(server). I need to extract the names of all pdf in that folder to an excel sheet.

After searching in google, I could get a solution but it works if the folder is on my computer.




Option Explicit
Sub GetFileNames()
Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$
InitialFoldr$ = "C:\" '<<< Startup folder to begin searching from
With Application.FileDialog(msoFileDialogFolderPicker)
.InitialFileName = Application.DefaultFilePath & "\"
.Title = "Please select a folder to list Files from"
.InitialFileName = InitialFoldr$
.Show
If .SelectedItems.Count <> 0 Then
xDirect$ = .SelectedItems(1) & "\"
xFname$ = Dir(xDirect$, 7)
Do While xFname$ <> ""
ActiveCell.Offset(xRow) = xFname$
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
End Sub

To access the network folder I need to login to my company's application and browse the folder.
Can we set this process in VBA. Had it been few files, I would have done it manually but its hundreds of files everyday.

Kindly suggest...

Thanks for your help..

[Kenneth Hobs]

Is there a reason why you need a file dialog? If you have the security, just put the name of the drive:\folder into the first xFname$ value.
e.g.

xFname$ = "u:\ken\*.pdf"

Please use code tags for code. Most all forums are like that. Use the Go Advanced button and then the # icon to insert code tags or type them and paste code between. Or do it manually where you replace ()'s with []'s.
(code)MsgBox "Hi"(/code)

[kevvukeka]

Hi Kenneth,

The site design has changed I guess, ;'coz earlier I could see the VBA button which would put my code in tags, I cannot see it now. I wasn't aware of the go advance feature. will use it going forward.


Thanks for the reply. so will this tweak open the network folder without any password?

[Kenneth Hobs]

I don't know your system setup so I can't say. It is best to just try the code and find out.

In some systems, you just enter the password once per session such as a VPN.

[kevvukeka]

Hi Kenneth,

I tried your code, but it only extracted the path name to the sheet given at xFname$


I replace this line in my code:

xFname$ = Dir(xDirect$, 7)

with

xFname$ = "//fileserver/xxxxxxx/xxxxxxxxxxx/xxxxx\*.pdf".


moreover, how can we edit this code to extract file subfolder in the main folder. In the main folder if I have 10 subfolders and each folder will have just pdf files in it. how to edit this code such that multiselect of folders is allowed.



Thanks

[Kenneth Hobs]

Did you really need a multiselect option for subfolders? IF so, it does not work for the msoFileDialogFolderPicker. You can use a checkbox in a Listbox control on a userform. Here is an example. http://www.excelforum.com/excel-general/342931-select-multiple-folders.html

See Dave Peterson's example.

Some methods will not work well with UNC names, i.e. //server. In that case, map the drive and use it.

If there are no pdf file in the parent folder, and no subfolders of subfolders with pdf files, a line or two of code will get what you want. Otherwise, one iterates the subfolders with a method like in the link, and then iterate the pdf files in each subfolder.

[kevvukeka]

Hi Kenneth,

yes I need multiselect option. As we have around 60 people working and each one will have separate folder for him. I will check the link given by you. I Hope it should work.