Listing the files from a directory

G

Guest

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.
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top