Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How far can you macro?

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm trying to establish whether I can create a marco that will open a folder and search for new files with a certain title? Is this possible? I then intend to create a further marco to open (the text file), delimit it and open it in an excel format. Can anyone enlighten me if this is possible?

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    You can use the filesearch capabilities of the Office object library. You can modify this code to suit your needs. It currently lists all files in a specified folder in an Excel worksheet.

    Sub ListFiles()
    Dim lngLoop As Long

    With Application.FileSearch
    .NewSearch
    .LookIn = "C:temp"
    .SearchSubFolders = True
    'You can experiment with this next line to limit the number of files returned
    .LastModified = msoLastModifiedThisWeek
    .Filename = "*searchtexthere*"
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute > 0 Then
    For lngLoop = 1 To .FoundFiles.Count
    Cells(lngLoop, 1) = .FoundFiles(lngLoop)
    Next
    End If
    End With

    End Sub

    HTH,
    D

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    With regard to the below code, I am a tranger to basic. How would i implement this? Also how would I choose the folder that is searched?

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK,

    First of all open the Visual Basic Editor (Alt+F11). Then click Insert, Module. This will add a module in which you can place macro and function code. Paste the code I gave you directly into that module.

    To choose the folder you are searching change this line:-

    LookIn = "C:temp" (should be only one back slash)

    to Lookin = "C:whatever folder you want"

    HTH,
    D

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi, I have tried the above but am having no result at all?
    Thanks

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are there any files in the folder you're specifying? Can you post your code?

    Regards,
    D

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thakns for this DK! Very new to basic.
    once i've submitted this into the module is there anything else I should be doing?

    Sub ListFiles()
    Dim lngLoop As Long

    With Application.FileSearch
    .NewSearch
    .LookIn = "e:intasysreports"
    .SearchSubFolders = True
    .LastModified = msoLastModifiedThisWeek
    .Filename = "*searchtexthere*"
    .FileType = msoFileTypeExcelWorkbooks
    If .Execute > 0 Then
    For lngLoop = 1 To .FoundFiles.Count
    Cells(lngLoop, 1) = .FoundFiles(lngLoop)
    Next
    End If
    End With

    End Sub

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes there is:-

    .Filename="you need to put the name of your file here"

    e.g.

    .Filename="a*" for all filenames beginning with a

    or

    .Filename="*" for all files

    If you want all files (not just those modified in the last week) then delete the line

    .LastModified = msoLastModifiedThisWeek

    If you want to search for all files then change the line

    .FileType = msoFileTypeExcelWorkbooks


    to this

    .FileType = msoFileTypeAllFiles


    The code I provided was an example. You can experiment with each of the properties of Filesearch (e.g. date last modified) to get exactly what you need.

    Let me know if you have any more problems,

    regards,
    D




  9. #9
    New Member
    Join Date
    Feb 2002
    Posts
    40
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    DK that's great. Hope you don't mind if I pick your brains a little more?
    Can I set that to a command button and how?

    Is there a way I can create another command button (once the files have been located) that will open and delimit them from a text file to an excel one?

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK,

    There are two types of button you can use - one from View, Toolsbars, Forms. If you place one of these on a sheet you can right click it and choose Assign Macro. Just assign the above macro to it.

    The second type of button is an ActiveX control button from View, Toolbars, Control Toolbox. If you use one of these you can double click it and enter code in its Click event. E.g. double click it and paste the above code -

    Code:
    Private Sub CommandButton1_Click()
        Dim lngLoop As Long
        With Application.FileSearch
            .NewSearch
            .LookIn = "e:intasysreports"
            .SearchSubFolders = True
            .LastModified = msoLastModifiedThisWeek
            .Filename = "*searchtexthere*"
            .FileType = msoFileTypeExcelWorkbooks
            If .Execute > 0 Then
                For lngLoop = 1 To .FoundFiles.Count
                    Cells(lngLoop, 1) = .FoundFiles(lngLoop)
                Next
            End If
        End With
    End Sub
    If you are using a userform then you can only use the ActiveX variety of button.

    As for your second point. Once you have located the files you can use

    Workbooks.OpenText Filename:=.FoundFiles(lngLoop)

    after the line For lngLoop=1 to .FoundFiles.Count

    Good luck,
    D

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •