How far can you macro?

UniversalJOC

New Member
Joined
Feb 22, 2002
Messages
40
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?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
Are there any files in the folder you're specifying? Can you post your code?

Regards,
D
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top