PDA

View Full Version : Get file names from folder problem!



mackypogi
10-29-2013, 06:42 PM
Hi Guys, I have a problem here, I have a code that can list all the file in a folder to excel worksheet, my problem is I want to eliminate the file extension of the file eg.(.xlxs). I just get the code from the net, and I dont have idea how can I modify it. I hope you guys can help me.

Thanks

Here is the code





Option Explicit

Sub GetFileNames()

Dim xRow As Long
Dim xDirect$, xFname$, InitialFoldr$

InitialFoldr$ = "G:\" '<<< 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

Doug Robbins
10-29-2013, 06:55 PM
Use


ActiveCell.Offset(xRow) = Left(xFname$, Instr(xFname$, ".") - 1)

mackypogi
10-29-2013, 07:24 PM
hi Doug

It works with

ActiveCell.Offset(xRow) = Left(xFname$, InStr(xFname$, ".xlsx") - 1)

mackypogi
10-29-2013, 08:08 PM
ActiveCell.Offset(xRow) = Left(xFname$, InStr(xFname$, ".xlsx") - 1)

Hi Doug, I have a question, Using the code above it works, but sometimes there is an excel file extension like (.XLSX) I have an error with that filename extension.

Doug Robbins
10-29-2013, 10:58 PM
Use the code that I gave you. Then it does not matter what extension the file has.