?
Solved

Using VB import .txt file into excel sheet

Posted on 2005-03-08
6
Medium Priority
?
199 Views
Last Modified: 2010-05-02
Hi all,

I am using vb 6, ms access, and excel.  What i need to do is through vb code open a tab delimited .txt file then export that data to an excel file (some columns will need to be formated as text in excel).  

Any one know how to do this?
0
Comment
Question by:JoannieJefferson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 5

Expert Comment

by:gary_j
ID: 13490695
dim ff as integer
dim strInp as string
dim xlApp as object
dim xlBook as object
dim ws1 as object
dim r as range

ff = FreeFile

'prepare for writing to excel
set xlapp = CreateObject("excel.application")

note:  <------- the "book" part depends upon whether you are creating a new book
                      or opening an existing book
Set xlbook = xlapp.Workbooks.Open(ExcelFileName)

note <------------- the same is true as to whether or not you are using an existing sheet

Set ws1 = xlbook.Worksheets(ExcelSheetName)

'set the range starting point
set r = range("a1")

'now open your text file
open "filename with complete path" for input as #ff

do while not eof(ff)
    line input #ff, strInp
    'now do what you need to do with this input

    'to output to excel
    r.offset(i,j).value = something from your input       'where i = row offset and j = column offset
loop

close #ff

xlapp.workbooks.close

Note:  be sure to do all the cleanup  --  set objects to nothing, etc.

this should get you started
0
 

Author Comment

by:JoannieJefferson
ID: 13505623
gary j,

i tried to run your code but all i get in one cell of the excel sheet is some data that is not usable, it looks like the following : (ÐÏࡱ)

the code runs through the loop one time.

this is how i have it set up

Open (p_strNameofFile) For Input As #ff

Do While Not EOF(ff)
    Line Input #ff, strInp
    'now do what you need to do with this input

    'to output to excel
    i = 1
    r.Offset(i, j).Value = strInp    ***Could "strInp" be in the wrong location?
    i = i + 1
Loop

Close #ff

i am not sure how to read the input into excel.

Help!

0
 
LVL 5

Expert Comment

by:gary_j
ID: 13505865
Open (p_strNameofFile) For Input As #ff

Do While Not EOF(ff)
    Line Input #ff, strInp
    'now do what you need to do with this input

    'to output to excel
    i = 1
    r.Offset(i, j).Value = strInp    ***Could "strInp" be in the wrong location?
    i = i + 1
Loop

Close #ff

1.  i and j represent offsets, so, for example, if i want to put values in a1,a2,a3, b1,b2,be my code would be
     set r = range("a1"): r.offset(0,0).value = "a1": r.offset(0,1).value = "a2": r.offset(0,2).value = "a3"
                                   r.offset(1,0).value = "b1": r.offset(1,1).value = "b2": r.offset(1,2).value = "a4"

2.  What is in the file you are importing?  Is it tab-delimited text?  if so, do something like this:

dim astrInfo()

line input #ff, strInp

astrinfo = split(strInp,chr(9))

do while not eof(ff)
     for j = 0 to 5 '''''''this assumes there are 6 fields in your input file
           r.offset(i,j).value = astrinfo(j)
     next 'j
     i = i + 1
loop
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:JoannieJefferson
ID: 13506291
Gary,

Yes i am using a tab delimited file.

We are almost there.  The only problem that i am having now is that the strInp only contains the first line of data in the text file.  so when it loops it just prints the same thing over and over again.

Private Sub Command2_Click()
Dim ff As Integer
Dim strInp As String
Dim strInfo() As String
Dim xlApp As Object
Dim xlBook As Object
Dim ws1 As Object
Dim r As Range
Dim i, j As Long
Dim filename As String

ff = FreeFile()

Set xlApp = CreateObject("excel.application")

filename = p_strNameofFile

p_strNameofFile = Left(p_strNameofFile, (Len(p_strNameofFile) - 4)) & ".xls"
'p_strNameofFile = p_strNameofFile & ".xls"
Set xlBook = xlApp.Workbooks.Open(p_strNameofFile)

Set ws1 = xlBook.ActiveSheet

'set the range starting point
Set r = Range("a1"): r.Offset(0, 0).Value = "a1": r.Offset(0, 1).Value = "a2": r.Offset(0, 2).Value = "a3":   r.Offset(1, 0).Value = "b1": r.Offset(1, 1).Value = "b2": r.Offset(1, 2).Value = "a4"

'now open your text file
Open (filename) For Input As #ff
Line Input #ff, strInp
strInfo = Split(strInp, Chr(9))
i = 0

Do While Not EOF(ff)
     For j = 0 To 5
           r.Offset(i, j).Value = strInfo(j)      *****prints to excel the same data from the first line in the text file.
     Next
     i = i + 1

Loop

Close #ff

xlApp.Workbooks.Close
End Sub
0
 
LVL 5

Accepted Solution

by:
gary_j earned 2000 total points
ID: 13506472
Line Input #ff, strInp                  <--------- these two lines belong inside the loop
strInfo = Split(strInp, Chr(9))      <--------- like below


Do While Not EOF(ff)
     For j = 0 To 5
           line input #ff, strInp
           astrinfo = split(strInp,chr(9))
           r.Offset(i, j).Value = strInfo(j)      *****prints to excel the same data from the first line in the text file.
     Next
     i = i + 1

Loop

Close #ff
0
 

Author Comment

by:JoannieJefferson
ID: 13506492
It works......Thanks YOU.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month10 days, 11 hours left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question