Listing the files from a directory
 

I have about 5 - 6 textfiles which are available in a specific directory, I
have done a macro to identify these files & convert them to excel files. Now
I'm in a fix that some time there might be 3 files only or no file in that
folder.

In the Macro, I have refered the file (which is having constant name say
A,B,C...), if these files are available, the macro will open the text file &
convert them to excel. if say text file B is not available then macro stops
over here. how should I proceed further.

Alternatively, how should I get the list of text files available in a
particular folder & how this can be displayed in the excel worksheet in a
range.

thanks for the help.

regards,
yagna.

Listing the files from a directory
 

Try using the FileSystemObject?

"yagna" wrote in message
...
I have about 5 - 6 textfiles which are available in a specific directory,
I
have done a macro to identify these files & convert them to excel files.
Now
I'm in a fix that some time there might be 3 files only or no file in that
folder.

In the Macro, I have refered the file (which is having constant name say
A,B,C...), if these files are available, the macro will open the text file
&
convert them to excel. if say text file B is not available then macro
stops
over here. how should I proceed further.

Alternatively, how should I get the list of text files available in a
particular folder & how this can be displayed in the excel worksheet in a
range.

thanks for the help.

regards,
yagna.

Listing the files from a directory
 

Yagna,

Sub ProcessFiles()
Dim sPath As String, sName As String
Dim i As Long, sh As Worksheet
Dim bk As Workbook
Dim bListInsheet As Boolean

' Variable bListInSheet value
' True: List files in worksheet
' False: Open files and save as .xls

bListInsheet = False

Set sh = ActiveSheet

sPath = "C:\Myfolder\"
sName = Dir(sPath & "*.txt")
i = 1
Do While sName < ""
If bListInsheet Then
i = i + 1
sh.Cells(i, 1).Value = sName
Else
Set bk = Workbooks.Open(sPath & sName)
bk.SaveAs Replace(sPath & sName, _
".txt", ".xls"), _
xlWorkbookNormal
bk.Close Savechanges:=False
End If
sName = Dir
Loop
If bListInsheet Then _
sh.Cells(1, 1).Value = "File Name"
End Sub


--
regards,
Tom Ogilvy

"yagna" wrote:

I have about 5 - 6 textfiles which are available in a specific directory, I
have done a macro to identify these files & convert them to excel files. Now
I'm in a fix that some time there might be 3 files only or no file in that
folder.

In the Macro, I have refered the file (which is having constant name say
A,B,C...), if these files are available, the macro will open the text file &
convert them to excel. if say text file B is not available then macro stops
over here. how should I proceed further.

Alternatively, how should I get the list of text files available in a
particular folder & how this can be displayed in the excel worksheet in a
range.

thanks for the help.

regards,
yagna.