UtterAccess Discussion Forums > LOOP HELP: Get data from certain files ending in...

Full Version: LOOP HELP: Get data from certain files ending in...

UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel

Cindy4683

Sep 11 2007, 10:32 AM

Hello Everyone,
I have been dealing with this problem for some time now and I still can't figure out what I need to do for this LOOP.

In the excel program, I have code that allows you get data from a certain excel file in a certain folder on the hard drive (depending on what month/year and state, which is all based off the file name), that data is then added into the correct excel sheet. Anyways, we finally decided that it is too much of a hassle to do each file individually and now would like to get ALL of them at once.

So instead of doing the certain search for month/year and state, I would like to replace it with a GET ALL, and just find every file with the name ending in "RI-XML.xls" and LOOP until all of the files in that folder (with that name) have been added. I think a LOOP would be the best option, however mine never works.

I attached my MAIN DATABASE with the code to retrieve data, along with some sample files were data is pulled from. You will need to put all of these in C:\temp on your hard drive for it to work without changing code.

Thanks for taking the time to read everything, if you need anymore info, ill try to get it you ASAP.

THANK YOU!! -- CINDY

dflak

Sep 11 2007, 12:08 PM

Could you do something like this? (This is modifed code I found on the net)

CODE

Public Sub Excel_FileSearch2()
    Dim x As Integer
    With Application.FileSearch
        .LookIn = ThisWorkbook.Path
        .Filename = "*RI-XML.xls"
        If .Execute > 0 Then
            MsgBox .FoundFiles.Count & " workbooks were found."
            For x = 1 To .FoundFiles.Count
                MsgBox .FoundFiles(x) 'Put your existing application here
            Next x
        Else
            MsgBox "No workbooks were found."
        End If
    End With
End Sub

Clippit

Sep 11 2007, 12:28 PM

I use a loop with Dir() for this kind of thing, but I've used FileSerach in the past so it should work as well- maybe even better- it also has the advantages of looking in subdirectories I think.

The Dir loop goes something like this:

CODE

        strImageFolder="...your folder name\"
        strFileSpecMatch = strImageFolder & "File*.xls"   ' insert your wildcard expression here
        strThisSpec = Dir(strFileSpecMatch)
        Do While strThisSpec <> vbNullString
            Debug.Print strImageFolder & strThisSpec
            strThisSpec = Dir
        Loop

Dir with no path will give you the next match, and it will return an empty string when there are no more matches.

StuKiel

Sep 12 2007, 03:48 AM

FWIW, a quick word of warning about filesearch, it has disappeared from Excel 2007, so if this workbook will be used by others stick to DIR.

Stu.

Cindy4683

Sep 14 2007, 01:05 PM

Thanks guys for the help, i really appreciate it!!

Thanks again,
-Cindy

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.