Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using VB import .txt file into excel sheet

Posted on 2005-03-08
6
Medium Priority
?
210 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
  • 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
Independent Software Vendors: 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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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 Month11 days, 10 hours left to enroll

564 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