Link to home
Start Free TrialLog in
Avatar of nu2vb
nu2vb

asked on

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

ASKER CERTIFIED SOLUTION
Avatar of mflam
mflam

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of nu2vb
nu2vb

ASKER

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