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...

nu2vbAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mflamCommented:
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
ResultsTable.Recordset.MoveFirst
While Not ResultsTable.Recordset.EOF
    resultExcelData.Recordset.AddNew
    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
    resultExcelData.Recordset.Update
    ResultsTable.Recordset.MoveNext
Wend ' done with data
   


endw
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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???

0
mflamCommented:
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:
data1.recordset.movefirst
do while not data1.EOF
  msgbox(data1.recordset.fields(0)
Loop

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
  MsgBox(temp,
         vbOKOnly,
         "hi there")
Loop
Close #1

This is "old" Basic.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.