How far can you macro?

[UniversalJOC]

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?

[dk]

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

[UniversalJOC]

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?

[dk]

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

[UniversalJOC]

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

[dk]

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

Regards,
D

[UniversalJOC]

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

[dk]

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

[UniversalJOC]

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?

[dk]

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 -

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