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