473,320 Members | 1,909 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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
1 5515

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: Allison Bailey | last post by:
Hi Folks, I'm a brand new Python programmer, so please point me in the right direction if this is not the best forum for this question.... I would like to open an existing MS Excel spreadsheet...
3
by: Bell, Kevin | last post by:
I'd love to be able to open up a windows folder, like c:\temp, so that it pops up visually. I know how to drill down into a directory, but I can't figure out how to open one up on screen. Would I...
6
by: Daniel | last post by:
Hi all, Can i open and edit the excel sheet on web page after downloading? After editing, i close the web page and the excel file auto upload to the server. Is it possible? I really struggling...
8
by: Radu | last post by:
Hi, I have the following problem: I open a recordset based on excel/csv files, but then I need to filter (in code) in order to extract only data pertaining to a specific person. This code is...
3
by: ninrulz | last post by:
I will try to explain my situation. I know that it is hard to offers solutions without fully understanding what people would like to achieve. I receive 2 csv files every month. The csv files...
11
by: MD | last post by:
Hello, I need to import a sheet of 884 different excel-file with same lay- out. The sheet name is 'Totaal' and is the same in all different files. Is there a script (module) in order to: 1....
28
by: kkadakia | last post by:
I get a daily excel file for a entire month which I want to transfer into Access at the end of the month. So, there are around 20-25 excel files I get by the end of the month, and I would like to...
2
jaccess
by: jaccess | last post by:
I was wondering if there is a simple way to open a specific folder from an access form button. I have a form that a user can enter a date range and either view or print reports based on the date...
8
by: shenkel55 | last post by:
I'm using Access and Excel 2003. Using either the import wizard or code, I have the same problem. This problem only happens with Excel files automatically generated by Corp IT. If I try to do an...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.