Hi,
I have a big problem in my master thesis.
I want to read many text file into excel file for data analyses. Th
text files are so numerous that I can not read the files one by one. S
I plan to read it into excel by VBA code. But I just learn to how t
read one file into excel, and do not know how to deal with numerous.
can modify to name the files with number. My code:

Sub opentext()

Dim i As Integer


For i = 1 To 10000

Workbooks.opentext Filename:="D\:VB\10000+i.out", _ /The text file ca
be name as 10001, 10002,....
Origin:=xlMSDOS, _
StartRow:=1, _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Space:=True

Next i

End Sub

Obviously, the code can not work. Could somebody give me a help. Thank
in advance

--
Message posted from http://www.ExcelForum.com

Here is some code to read a directory and access all text files.

You will; need to install a call to your code - I have marked it.

Option Explicit

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Dim FSO As Object

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


If ActiveSheet.Range("b3").Value = "N/A" Then

ActiveWorkbook.Save

Else

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 = "Text Document" Then
' your routine here
End If
Next file

End If ' sFolder < ""

End If 'ActiveSheet.Range("b3").Value = "N/A"

End Sub


'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = "Select a folder.")
As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

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

'----------------------------- end-script -----------------------------



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Together " wrote in message
...
Hi,
I have a big problem in my master thesis.
I want to read many text file into excel file for data analyses. The
text files are so numerous that I can not read the files one by one. So
I plan to read it into excel by VBA code. But I just learn to how to
read one file into excel, and do not know how to deal with numerous. I
can modify to name the files with number. My code:

Sub opentext()

Dim i As Integer


For i = 1 To 10000

Workbooks.opentext Filename:="D\:VB\10000+i.out", _ /The text file can
be name as 10001, 10002,....
Origin:=xlMSDOS, _
StartRow:=1, _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Space:=True

Next i

End Sub

Obviously, the code can not work. Could somebody give me a help. Thanks
in advance.


---
Message posted from http://www.ExcelForum.com/

Thank you for your nice help, Bob.
I am a new user for VBA in Excel. In fact, I could not understand you
code fully. Could you review it for me again. I can not run the code.

My sitation is that there are several tpyes file in one folder, such a
***.out, ***.fre, ***.trf. All the types can be opened by textscript
Now I just use excel to open all the files, ***.out.


Option Explicit

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Dim FSO As Object

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


If ActiveSheet.Range("b3").Value = "N/A" Then

ActiveWorkbook.Save

Else

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 = "Text Document" Then

Workbooks.opentext Filename:="file", _
Origin:=xlMSDOS, _
StartRow:=1, _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Space:=True

End If
Next file
End If ' sFolder < ""
End If 'ActiveSheet.Range("b3").Value = "N/A"
End Sub
'-------------------------------------------------------------
Function GetFolder(Optional ByVal Name As String = "Select a folder."
As String
'-------------------------------------------------------------
Dim bInfo As BROWSEINFO
Dim path As String
Dim oDialog As Long

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


Thank you very much

--
Message posted from http://www.ExcelForum.com

Hi,

I have amended the code to remove an un necessary test, and to open the
selected files.

Just copy the code below and paste in to a normal code module. Then run the
macro ProcessFiles. It will prompt you to select a directory, and when you
do, it will open all files in that directory. It won't do anything with them
other than open them , that's up to you

Option Explicit

Private Declare Function SHGetPathFromIDList Lib "shell32.dll" _
Alias "SHGetPathFromIDListA" _
(ByVal pidl As Long, _
ByVal pszPath As String) As Long

Private Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" _
(lpBrowseInfo As BROWSEINFO) As Long

Private Type BROWSEINFO
hOwner As Long
pidlRoot As Long
pszDisplayName As String
lpszTitle As String
ulFlags As Long
lpfn As Long
lParam As Long
iImage As Long
End Type

Dim FSO As Object

Sub ProcessFiles()
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 = "Text Document" Then
Workbooks.OpenText Filename:=file.path, _
Origin:=xlMSDOS, _
StartRow:=1, _
DataType:=xlDelimited, _
ConsecutiveDelimiter:=True, _
Space:=True
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

'----------------------------- end-script -----------------------------


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Together " wrote in message
...
Thank you for your nice help, Bob.
I am a new user for VBA in Excel. In fact, I could not understand your
code fully. Could you review it for me again. I can not run the code.

My sitation is that there are several tpyes file in one folder, such as
***.out, ***.fre, ***.trf. All the types can be opened by textscript.
Now I just use excel to open all the files, ***.out.