kevvukeka
07-25-2013, 03:28 AM
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..
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..