Importing Into Excel Through VB

I need to be able to import a delimited text file into an Excel spreadsheet, but I want to do it through code.
It's easy enough to do through Excel, but I want to be able to automate it through VB6 code.
I can use OLE as it it's only going to be run on my PC, and I have Excel installed.

Sample code will be really appreciated!
Hope someone can help...

Who is Participating?
mflamConnect With a Mentor Commented:
Make a new (empty) excel file and close the Excel. Then...
Just open the VB with a standard exe proj.
Add a Data object to your form, from the toolbox.
In the properties:
Change the (name) to "myRecord"
Set it's "Connect" to Excel 8.0.
Click the "DatabaseName" property, and you get "..." click on the "..." and you are browsing your disk for the file you saved.
Now click the record source and find the workbook or file you want to use.

Now you have a "myRecord" ready to write to.

Now add a Command Button and when clicked:
....this is from code I wrote: see if you understand it:
'* And write them to Excel too
While Not ResultsTable.Recordset.EOF
    resultExcelData.Recordset.Fields(0).Value = ResultsTable.Recordset.Fields(0).Value
    resultExcelData.Recordset.Fields(1).Value = ResultsTable.Recordset.Fields(1).Value
    resultExcelData.Recordset.Fields(2).Value = ResultsTable.Recordset.Fields(2).Value
Wend ' done with data

nu2vbAuthor Commented:
Well, I've sort of got that far, but I need to be able to import the text file into the Excel spreadsheet.
Is there an easy way to directly import the text file into the VB project, or does it have to be imported into a database.
As I am reading from a text file, the example you gave me won't work (I don't think?).
Your example look as though it is reading from one Excel table to another.  Is that right???

Oh! It's a text file.
That's a different question, and here's the answer(s):

My "answer" example is working from an Access database to excel.

From text to excel is easy too.
You open another "data" (from toolbox) and set the following properties:
(Change to Categorized so you see them all together)

"DatabaseName" : choose the "..." and select your file. Only the path will be left, and the filename will show up in the RecordSource.

"RecordSource" : As I said, your filename.

"RecordType: Leave it as "table"

You now can write:
do while not data1.EOF

You get the whole line each time.
You'll need to parse it to words.
There are other ways to open a text file and read it, the most common (and easy) is:

Dim myStr as String
Dim myNum as Integer
Dim temp as String
Open "c:\bla.ini" For Input As #1
do while not EOF(1)
  Input #1, myStr, myNum
  temp = myStr + myNum
         "hi there")
Close #1

This is "old" Basic.
All Courses

From novice to tech pro — start learning today.