Help with getting files from a folder

[JazzyLee]

I have written a code to read the files in a folder that the user selects from browsing for a folder. I want to process all .xls files in the folder and used the 'DIR("*.xls",xlNormal)' directory search. It worked fine once and processed all the files but does not see the files when I run the macro again. I closed down Excel completely and tried again but it still does see the files. I tried a new approach using the FSO and GetFolder method and it worked the first time but ended up with an error the next time through. It knows that there are files in the folder because the error message that is displayed shows the name of the first Excel file in the folder and says it cannot be found ... be sure the name is spelled correctly. Anyone has any idea why this is happening? I am on XP running Excel 2003. I am also choosing a folder on a company server through a mapped drive (if this makes a difference).

 

[combo]

What's your code? Are those files closed when you try to open them next time?

combo

 

[JazzyLee]

Combo,
This is the code for the second method. I imagine the DIR method would be resolved if I can figure out why this is happening. I get an error on the Workbooks.Open line that says: "'GAAP1100.xls' could not be found. Check the spelling of the file name, and verify that the file location is correct."

GAAP1100.xls is the first Excel file in the folder so it is seeing it. I verified that the value in strPath_I which was saved when the folder was selected from the BROWSE window is valid and pointing on iFName shows "G:\CorpSys\Work In Progress\EEH" which is my path/folder. Do I need to specify the path on the OPEN?

Dim FSO as object
Dim iFldr
Dim iFile
Dim iFName As String

iFName = strPath_I
Set FSO = CreateObject("Scripting.FileSystemObject")
Set iFldr = FSO.GetFolder(iFName)

With iFldr
For Each iFile In .Files
strFileType = Right(iFile, 4)
If UCase(strFileType) = ".XLS" Then
Workbooks.Open FileName:=iFile.Name
varInFileName = ActiveWorkbook.Name
Call ProcessFiles
varInFileName = Empty
End If
strFileType = Empty
Next
End With
Set FSO = Nothing
Set iFldr = Nothing

 

[PHV]

Workbooks.Open FileName:=iFile.[!]Path[/!]

Another way:
strPath_I & "\" & iFile.Name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[combo]

I do not have any problems in local drive. You could change iFile.Name to iFile.ShortName (or use full path), to point the location, you can have problems if if the file is not in active excel directory.
Test iFName value that you pass to Open method, if is proper, try to open the file manually - the access may be locked.

combo

 

[WinblowsME]

Sub list_dir()
   Dim path_name As String, file_name As String

   path_name = "C:\Documents and Settings\WinblowsME\Desktop\"
   file_name = Dir(path_name & "*.xls")
   
   Do While file_name <> ""
      Debug.Print file_name
      file_name = Dir
   Loop
End Sub

 

[PHV]

Debug.Print[!] path_name &[/!] file_name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JazzyLee]

Thanks for the quick response ... I am meeting my deadline on this project. I used the suggestion from PHV to add the path to the file name (strPath_I & "\" & iFile.Name) and it is working like a charm. I also went back to my old DIR codes and cleaned it up using the subroutine from WindBlowsMe and that's working now (I had changed the name of a variable -- for being a neat freak -- and forgot one ... did not do a REPLACE ALL). Small oversights that I practically lost sleep over but sometimes it takes another pair of eyes to help you see. I LOVE this website and all the members who are so willing to share ideas. STARS to all of you!