I have the following code which pulls the file names from the directory I specify. I found it on the internet and modified it to work for what I need.
The problem is that I don't want it to popup with a window asking me to pick a folder - I want to use the specified folder. How can I change this code so that I don't have to use the window, or if I can't change it, what can I do about my situation?
Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$
InitialFoldr$ = "C:\Desktop" '<<< 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) = Left(xFname$, InStrRev(xFname$, ".") - 1)
xRow = xRow + 1
xFname$ = Dir
Loop
End If
End With
I ended up changing my code completely and not using the old code. Again, I found some code on the internet and modified it to work for what I need.
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim FileArray() As Variant
Dim FileCount As Integer
Dim FileName As String
Dim rng As Range
Dim Idx As Integer
FileCount = 0
FileName = Dir("C:\Desktop")
' Loop until no more matching files are found
Do While FileName <> ""
FileCount = FileCount + 1
ReDim Preserve FileArray(1 To FileCount)
FileArray(FileCount) = FileName
FileName = Dir()
Loop
GetFileList = FileArray
Set rng = ActiveCell
For Idx = 0 To FileCount - 1
ActiveCell.Offset(Idx, 0).Value = Left(FileArray(Idx + 1), InStrRev(FileArray(Idx + 1), ".") - 1)
Next Idx
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments