iterating through .xls files in a folder - [RESOLVED]

Greetings,

OK, I really appreciate eveyone's input here so far today. OK, now, I have a button on my VBA form, that when clicked should:

1) Open a folder C:\FSR
2) For each .xls file in that folder, go to "Tab 3" (which is the actual name of the third tab) and import external data to "tbl_Import_Temp", within the database being used, which is called "FinancialReports".

Any idea the syntax for that? Here is what I have thus far:

VB Code:

Private Sub cmdStep1_Click()
    Dim cnnConnection As ADODB.Connection
    Dim rstImportTemp As ADODB.Recordset
    'Dim Detail As String
    
    Set cnnConnection = CurrentProject.Connection
    
    Set rstImportTemp = New ADODB.Recordset
    rstImportTemp.Open "tbl_Import_Temp", cnnConnection, adOpenStatic, adLockReadOnly, adCmdTable
 
    'open folder with Excel files
    'for each file
 
        'Get External Data for tab 3 and put it into tbl_Import_Temp, using the first row as headers.
    'next i
    
    MsgBox "Import of files, into the tbl_Import_Temp, is complete. Make sure the data looks OK; and then click the 'Step 2' button, below."
     
    rstImportTemp.Close
    Set rstImportTemp = Nothing
    
    Set cnnConnection = Nothing
End Sub

Thank you,
Jim

1) Use functions like Dir (read the help files) and a string variable to hold the path and another to hold the returned filename.
This will give you a filepath and a filename to use in step 2

2)
You can import via Access (if you are using that) import (I think it is docmd.transferstext or docmd.transferspreadsheet).

Or you can open an instance of excel and loop through the cells with the data in.
More coding, but possibly better/more flexible.

Thank you Ecniv.

I now have

VB Code:

For i = 0 To 3
        pathname(i) = Dir("C:\FSR\*.xls")
        MsgBox pathname(i)
    Next i

My immediate goal is to have it loop through all the Excel files in "C:/FSR".

I put 0 to 3 there becaue there are 4 files; but I'm actually going to need it to count the files, and then I can say

i = 0 to filecount.

Plus at this point, in the loop, the message box keeps displaying only the first file.

So, two things:
1) How do I get the filecount for the folder "C:\FSR", and
2) How do I get the loop to recognize each file, not just the first one?

For the record, its all .xls files in the folder.

Thank you,
Jim

Well, I did some figuring out, and I guess a filecount is worthless to me because its just an issue of it iterating through each file.

I'm all set with the following code I concocted:

VB Code:

flag = True
    firstflag = True
    
    'go through each .xls file in the folder "C:\FSR"
    Do While flag = True
        If firstflag = True Then
            excelfile = Dir("C:\FSR\*.xls")
            If Len(excelfile) > 0 Then ImportFile (excelfile)
        End If
        firstflag = False
        If firstflag = False Then excelfile = Dir()
        If Len(excelfile) > 0 Then ImportFile (excelfile)
        If Len(excelfile) = 0 Then flag = False
    Loop

Now that I'm done with being able to access each file, now I have to get on to the meat of the ImportFile function. Odds are, I'll be back in another thread. :)

Jim