PC Review


Reply
Thread Tools Rate Thread

Listing the files from a directory

 
 
=?Utf-8?B?eWFnbmE=?=
Guest
Posts: n/a
 
      2nd Oct 2007
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.
 
Reply With Quote
 
 
 
 
Nobody
Guest
Posts: n/a
 
      2nd Oct 2007
Try using the FileSystemObject?

"yagna" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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.



 
Reply With Quote
 
 
 
 
=?Utf-8?B?VG9tIE9naWx2eQ==?=
Guest
Posts: n/a
 
      2nd Oct 2007
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.

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Listing Active Directory groups that have directory access rights cwhankey@gmail.com Microsoft Excel Programming 0 17th Oct 2008 03:13 PM
Listing sAMAccountName when listing Group Membership =?Utf-8?B?UGV0ZQ==?= Microsoft Windows 2000 Active Directory 2 22nd Aug 2005 10:52 PM
RE:Listing of one cell controlling the listing of another cell. peijuan Microsoft Excel Programming 0 8th Aug 2004 03:17 AM
Listing files in a directory Andy Levy Microsoft Access Form Coding 1 15th Oct 2003 02:13 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 10:27 PM.