Import txt file with fixed length from VBA

M

Maurice

Hi guys,

If anyone could give me some pointers on how to go about this I'd appreciate
it very much.

situation:
I have to import various textfiles using VBA. I select the filename and path
which comes from a listview and a textbox on a userform. So far so good.

The textfile however is setup as follows:

xxx xxxxx xxxx xxxxx xxxxx

The data has fixed length for example the first field is 10chars, the second
20chars, the third 8 chars etc. (got about 150 rows of data) every row has
the same fieldspecs.

How would I go about it to import this data into a designated sheet from VBA?
Using the wizard will open the file and place it on a new workbook and
that's not what I want..

Thanks in advance for any pointers.
 
D

Dave Peterson

You could use Data|Import External Data|Import Data (xl2003 menus).

And at the last (or close to the last) dialog of that wizard, you'll be asked
where the data should go.

Personally, I'd just use exactly what you used, but add a couple of more steps.

One step to copy the data to the location where I wanted it and the second to
close without saving the text file that I just opened.

Option Explicit
Sub Testme()

Dim DestCell As Range
Dim TxtWks As Worksheet

'assign the destination cell -- I have no idea what you want.
Set DestCell = Worksheets("Sheet1").Range("x99")

'your code to open the file
Workbooks.OpenText Filename:=....

Set TxtWks = ActiveSheet

With TxtWks
.UsedRange.Copy _
Destination:=DestCell
.Parent.Close savechanges:=False
End With

End Sub
 
M

Maurice

Dave,

It's those last steps i should take into consideration. Thanks for replying
;-)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top