FileSystem Object Usage

[Goot]

I want to pick up excel files in a folder say C:\ED.
I want to read them in and append the data to my access tables. I can do this totally manually. BUT I want the program to do it. The problem is how do I set the loop up..
I have the shell below...These excel files will be emailed by various users, so I need a loop. Problem is the For Each statement is not working. I am getting error 438 object does not support this method. I am new at using this object.....
|
Thanks ...the code V

Set fs = CreateObject("Scripting.FileSystemObject")
Set myFolder = fs.GetFolder(strPath)

If txtFname = "" Or IsNull(txtFname) Then
MsgBox "Pls. enter filename."
Me.txtFname.SetFocus
Exit Sub
Else

sFile = fs.GetFileName(txtFname)
For Each fs In myFolder
xlApp.Visible = True
. excel objects here
Next

 

[alvechurchdata]

Two things come to mind.

The first is that you're saying
[TT]
For Each fs In myFolder
[/TT]

but fs is the File System Object and myFolder is the folder itself. You need to be looping through the collection of files belonging to the folder:

[TT]
For Each fil In MyFolder.Files
...
Next fil
[/TT]

The second problem is that I can't see where you're creating xlApp.

Geoff Franklin

http://www.alvechurchdata.co.uk