By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,550 Members | 875 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,550 IT Pros & Developers. It's quick & easy.

Open many *.txt files in folder and import the data into Excel

P: n/a
I have many *.txt files in a folder. I need to loop through the folder
and open every .txt file and import the records into an Excel
worksheet.

The records for all the text files are in the following format: seven
fields delimited by the pipe('|') symbol.

Example record:

TAM1973|Wednesday|09/28/05|08:59 AM|Date|Left Message|Rich

Each of the seven fields should be in it's own cell in Excel.

With the following code I get the following error: "Member not Found"
on this statement:

myWorkSheet = myWorkBook.Worksheets(1)
Dim myDir As New System.IO.DirectoryInfo("c:\test")
Dim myFile As System.IO.FileInfo
Dim str As IO.StreamReader
Dim myFullText() As String
Dim i As Int32
Dim counter As Int32
Dim myExcel As Object
myExcel = CreateObject("Excel.Application")
Dim myWorkBook As Excel.Workbook
Dim myWorkSheet As Excel.Worksheet
Dim columnames() As String = {"A", "B", "C", "D", "E", "F", "G"}
Dim j As Int32

myWorkBook = myExcel.Workbooks.Open("C:\test.xls")

myWorkBook = myExcel.Workbooks.Add()
myWorkSheet = myWorkBook.Worksheets(1)
myExcel.Visible = True

j = 0
For Each myFile In myDir.GetFiles
j += 1
str = New IO.StreamReader(myFile.FullName)
myFullText = str.ReadToEnd.Split("|")
i = 0
For counter = 0 To myFullText.Length - 1
i += 1
If i > 7 Then
i = 1
j += 1
End If
' With myWorkBook.ActiveSheet
myWorkSheet.Range(columnames(i - 1) & j).Value =
myFullText(counter)
'End With
Next
Next
myExcel.Quit()


Thanks

Nov 21 '05 #1
Share this Question
Share on Google+
1 Reply

P: n/a

Do a debug and see what number your excel columns are. It probably
thinks there is one more column that there actually is in your
spreadsheet. Easy to do when you are looping zero based and your
spreadsheet starts with 1.

"ri***********@northwesternmutual.com"
<ri***********@northwesternmutual.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com:
I have many *.txt files in a folder. I need to loop through the folder
and open every .txt file and import the records into an Excel
worksheet.

The records for all the text files are in the following format: seven
fields delimited by the pipe('|') symbol.

Example record:

TAM1973|Wednesday|09/28/05|08:59 AM|Date|Left Message|Rich

Each of the seven fields should be in it's own cell in Excel.

With the following code I get the following error: "Member not Found"
on this statement:

myWorkSheet = myWorkBook.Worksheets(1)
Dim myDir As New System.IO.DirectoryInfo("c:\test")
Dim myFile As System.IO.FileInfo
Dim str As IO.StreamReader
Dim myFullText() As String
Dim i As Int32
Dim counter As Int32
Dim myExcel As Object
myExcel = CreateObject("Excel.Application")
Dim myWorkBook As Excel.Workbook
Dim myWorkSheet As Excel.Worksheet
Dim columnames() As String = {"A", "B", "C", "D", "E", "F", "G"}
Dim j As Int32

myWorkBook = myExcel.Workbooks.Open("C:\test.xls")

myWorkBook = myExcel.Workbooks.Add()
myWorkSheet = myWorkBook.Worksheets(1)
myExcel.Visible = True

j = 0
For Each myFile In myDir.GetFiles
j += 1
str = New IO.StreamReader(myFile.FullName)
myFullText = str.ReadToEnd.Split("|")
i = 0
For counter = 0 To myFullText.Length - 1
i += 1
If i > 7 Then
i = 1
j += 1
End If
' With myWorkBook.ActiveSheet
myWorkSheet.Range(columnames(i - 1) & j).Value =
myFullText(counter)
'End With
Next
Next
myExcel.Quit()


Thanks


Nov 21 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.