Listing the files from a directory

Discussion in 'Microsoft Excel Programming' started by Guest, Oct 2, 2007.

  1. Guest

    Guest 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.
     
    Guest, Oct 2, 2007
    #1
    1. Advertisements

  2. Guest

    Nobody Guest

    Try using the FileSystemObject?

    "yagna" <> wrote in message
    news:...
    >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.
     
    Nobody, Oct 2, 2007
    #2
    1. Advertisements

  3. Guest

    Guest 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


    --
    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.
     
    Guest, Oct 2, 2007
    #3
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Angelikoula

    Listing the contents of a directory in a spreadsheet

    Angelikoula, Nov 6, 2003, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    288
    Don Guillett
    Nov 6, 2003
  2. peijuan

    RE:Listing of one cell controlling the listing of another cell.

    peijuan, Aug 8, 2004, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    361
    peijuan
    Aug 8, 2004
  3. Guest

    listing directory files in a spreedsheet automatically

    Guest, Nov 22, 2005, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    174
    Guest
    Nov 22, 2005
  4. paulharvey

    Listing all files in an online directory

    paulharvey, Jun 2, 2006, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    189
    Tim Williams
    Jun 2, 2006
  5. Replies:
    0
    Views:
    375
Loading...

Share This Page