User Input to open multiple files

[Mike D.]

More than just a simple use of:

dim fileName as variant

fileName=application.GetOpenFilename("Excel Files(*.xls),
*.xls")

I would like to have the user indicate a whole folder. I
then need to open all of the files in that folder (which
are all going to be .xls files). I will then need to
open each file and copy certain info into a new
worksheet, with the result being a total of all the
collected data in this new worksheet.

I know how to open a known file and close it, etc. But,
my biggest problem is figuring out how to have the user
indicate the correct folder. I would also like the
prompt to "suggest" the path, e.g., C:\MyDocuments\Mar
2004.

Is this possible?

 

[Bob Phillips]

Sub ProcessFiles()
Dim FSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim Folder As Object
Dim file As Object
Dim Files As Object

Set FSO = CreateObject("Scripting.FileSystemObject")

sFolder = GetFolder
If sFolder <> "" Then
Set Folder = FSO.GetFolder(sFolder)

Set Files = Folder.Files
For Each file In Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
With ActiveWorkbook
.SaveAs Left(.FullName, Len(.FullName) - 4), _
FileFormat:=xlTextMac
.Close savechanges:=False
End With
End If
Next file

End If ' sFolder <> ""

End Sub



'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name)
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

If IsMissing(Name) Then Name = "Select a folder."
bInfo.pidlRoot = 0& 'Root folder = Desktop

bInfo.lpszTitle = Name

bInfo.ulFlags = &H1 'Type of directory to Return
oDialog = SHBrowseForFolder(bInfo) 'display the dialog

'Parse the result
path = Space$(512)

GetFolder = ""
If SHGetPathFromIDList(ByVal oDialog, ByVal path) Then
GetFolder = Left(path, InStr(path, Chr$(0)) - 1)
End If

End Function

 

[mts]

Bob...I've seen a couple of your previous posts on opening multiple files.
I almost understand the code, but not quite.

My particular need involves opening several csv files from multiple folders.
one file at a time, i need to modify each file and then close each file as
an excel file (preferably in the same location as the csv file).

By the way, do you have any recommendations for any particular resources for
gaining ground in VBA (particularly for excel)?

Thank you.

Mike S.